MySQL date reporting and window functions: ROW_NUMBER, RANK, LAG, running totals

Answers — Dates and window functions

1. Easy. List February orders using a half-open date range. Target: 103,104,105; cancelled orders remain because no status filter was requested.

   SELECT order_id FROM orders WHERE order_date >= '2026-02-01' AND order_date < '2026-03-01' ORDER BY order_id;

2. Medium. Label products as premium if price is at least 800. Target: Desk lamp and Keyboard premium; four standard.

   SELECT product_name,CASE WHEN price>=800 THEN 'premium' ELSE 'standard' END AS tier FROM products ORDER BY product_id;

3. Hard. Rank completed orders by their total, preserving ties. Target: 103 rank 1; 102,106,108 rank 2; 105 rank 3; 101 rank 4.

   WITH t AS (SELECT o.order_id,SUM(i.quantity*i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' GROUP BY o.order_id) SELECT *,DENSE_RANK() OVER(ORDER BY total DESC) AS revenue_rank FROM t ORDER BY total DESC,order_id;

4. Professional. Create monthly and cumulative revenue for a finance handoff. Target: Monthly 1600,1950,2600; cumulative 1600,3550,6150. State that absent months need a calendar table in a larger dataset.

   WITH m AS (SELECT DATE_FORMAT(o.order_date,'%Y-%m') AS month,SUM(i.quantity*i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' GROUP BY month) SELECT month,revenue,SUM(revenue) OVER(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue FROM m ORDER BY month;

5. Easy. Show each order with its year and month number. Target: 8 rows; first (101, 2026, 1), last (108, 2026, 3)

   SELECT order_id, YEAR(order_date) AS yr, MONTH(order_date) AS mon FROM orders ORDER BY order_id;

6. Easy. Show each order with the weekday name of its date. Target: 8 rows; 101 Monday … 108 Wednesday

   SELECT order_id, order_date, DAYNAME(order_date) AS weekday FROM orders ORDER BY order_id;

7. Easy. List orders placed in January 2026 using a half-open range. Target: 2 rows: 101, 102

   SELECT order_id, order_date FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01' ORDER BY order_id;

8. Easy. Label orders as 'revenue' if completed, else 'not revenue'. Target: 8 rows; 104 and 107 are 'not revenue'

   SELECT order_id, CASE WHEN status = 'completed' THEN 'revenue' ELSE 'not revenue' END AS label FROM orders ORDER BY order_id;

9. Medium. Show days between each order and 2026-03-31. Target: 8 rows; first (101, 85), last (108, 20)

   SELECT order_id, DATEDIFF('2026-03-31', order_date) AS days_ago FROM orders ORDER BY order_id;

10. Medium. Number all orders in date order. Target: 8 rows; 101 is 1, 108 is 8

    SELECT order_id, order_date, ROW_NUMBER() OVER (ORDER BY order_date) AS seq FROM orders ORDER BY seq;

11. Medium. Number each customer's orders 1, 2, ... in date order. Target: 8 rows; Asha 101 → 1, 103 → 2

    SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS nth_order FROM orders ORDER BY customer_id, nth_order;

12. Medium. Rank products by price, highest first, using RANK. Target: 6 rows: Keyboard 1, Desk lamp 2, Planner 3, Mug 4, Notebook 5, Pen 6

    SELECT product_name, price, RANK() OVER (ORDER BY price DESC) AS price_rank FROM products ORDER BY price_rank, product_id;

13. Medium. Count completed vs other orders per month in one row per month. Target: 3 rows: (2026-01, 2, 0); (2026-02, 2, 1); (2026-03, 2, 1)

    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed, SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) AS other FROM orders GROUP BY month ORDER BY month;

14. Hard. Show each customer's first order date. Target: 5 rows: (1, 01-05); (2, 01-08); (3, 02-10); (4, 02-15); (5, 03-04), all 2026

    SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id ORDER BY customer_id;

15. Hard. Show each order with the previous order date and days since it (all orders, by date). Target: 8 rows; first gap NULL, last (108, prev 2026-03-04, 7 days)

    SELECT order_id, order_date, LAG(order_date) OVER (ORDER BY order_date) AS prev_date, DATEDIFF(order_date, LAG(order_date) OVER (ORDER BY order_date)) AS gap_days FROM orders ORDER BY order_date;

16. Hard. Show monthly completed revenue with the change from the previous month. Target: 3 rows: (2026-01, 1600, NULL); (2026-02, 1950, 350); (2026-03, 2600, 650)

    WITH m AS (SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY month) SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_prev FROM m ORDER BY month;

17. Hard. Find each customer's most recent order (all statuses). Target: 5 rows: (1, 103); (2, 106); (3, 104); (4, 108); (5, 107)

    WITH r AS (SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) SELECT customer_id, order_id, order_date FROM r WHERE rn = 1 ORDER BY customer_id;

18. Professional. Customer running total: completed order totals in date order and cumulative spend per customer. Target: 6 rows; running ends at Asha 1700, Bilal 2600, Dev 1850

    WITH t AS (SELECT o.order_id, o.customer_id, o.order_date, SUM(i.quantity * i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id, o.customer_id, o.order_date) SELECT customer_id, order_id, total, SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_spend FROM t ORDER BY customer_id, order_date;

19. Professional. Top product per category by completed revenue (ties kept). Target: 3 rows: Electronics Keyboard 2400; Home Mug 1250; Stationery Notebook 600

    WITH r AS (SELECT p.category, p.product_name, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status = 'completed' GROUP BY p.category, p.product_id, p.product_name), k AS (SELECT *, DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rk FROM r) SELECT category, product_name, revenue FROM k WHERE rk = 1 ORDER BY category;

20. Professional. On the large dataset, show completed revenue per month for 2025 with its rank (highest month = 1). Target: 12 rows, one per month of 2025; exactly one month has rank 1. Check the 12 values sum to the 2025 total.

    WITH m AS (SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01' GROUP BY month) SELECT month, revenue, RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM m ORDER BY month;