Easy. Scope: count Q1 2026 orders by status. Target: 3 rows: cancelled 1, completed 6, pending 1 (total 8)
SELECT o.status, COUNT(*) AS orders FROM orders o WHERE o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY o.status ORDER BY o.status;
Easy. Control total: completed Q1 revenue. Target: 1 row: revenue = 6150.00
SELECT 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 >= '2026-01-01' AND o.order_date < '2026-04-01';
Professional. Hand off an audited retail performance report. Target: 6 completed orders, 6150 revenue, 1025 average order value; customer report totals reconcile; document status rule, grain, fixed date range and missing-month limitation.
SELECT COUNT(DISTINCT o.order_id) AS completed_orders,SUM(i.quantity*i.unit_price) AS revenue,ROUND(SUM(i.quantity*i.unit_price)/NULLIF(COUNT(DISTINCT o.order_id),0),2) AS average_order_value FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01';
Medium. Revenue by month for Q1. Target: 3 rows: 2026-01 1600.00, 2026-02 1950.00, 2026-03 2600.00
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 >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY month ORDER BY month;
Medium. Revenue by product category, highest first. Target: 3 rows: Electronics 4000.00, Home 1250.00, Stationery 900.00
SELECT p.category, 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' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY p.category ORDER BY revenue DESC;
Medium. Revenue by customer city, highest first. Target: 3 rows: Mumbai 2600.00, Delhi 1850.00, Pune 1700.00
SELECT c.city, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'completed' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY c.city ORDER BY revenue DESC;
Medium. Rank products by completed revenue (DENSE_RANK, 1 = highest). Target: 5 rows: Keyboard 2400.00 (1), Desk lamp 1600.00 (2), Mug 1250.00 (3), Notebook 600.00 (4), Pen 300.00 (5)
WITH r AS (SELECT 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' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY p.product_id, p.product_name) SELECT product_name, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM r ORDER BY revenue_rank;
Hard. Every customer with completed orders, revenue (0 if none) and share of total revenue in %, rounded to 1 decimal. Target: 6 rows: Bilal 2600.00 42.3, Dev 1850.00 30.1, Asha 1700.00 27.6, then Charu, Esha, Farah with 0
WITH s AS (SELECT o.customer_id, COUNT(DISTINCT o.order_id) AS orders, 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 >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY o.customer_id), t AS (SELECT SUM(revenue) AS total FROM s) SELECT c.customer_name, COALESCE(s.orders, 0) AS orders, COALESCE(s.revenue, 0) AS revenue, ROUND(100 * COALESCE(s.revenue, 0) / t.total, 1) AS share_pct FROM customers c LEFT JOIN s ON s.customer_id = c.customer_id CROSS JOIN t ORDER BY revenue DESC, c.customer_id;
Hard. Reconciliation: show total revenue, the sum of monthly revenue, the sum of category revenue and the largest difference. Target: 1 row: 6150.00, 6150.00, 6150.00, difference 0.00
WITH base AS (SELECT o.order_date, p.category, i.quantity * i.unit_price AS amount 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' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01'), by_month AS (SELECT DATE_FORMAT(order_date, '%Y-%m') AS m, SUM(amount) AS revenue FROM base GROUP BY m), by_category AS (SELECT category, SUM(amount) AS revenue FROM base GROUP BY category) SELECT (SELECT SUM(amount) FROM base) AS total, (SELECT SUM(revenue) FROM by_month) AS months_total, (SELECT SUM(revenue) FROM by_category) AS categories_total, GREATEST(ABS((SELECT SUM(amount) FROM base) - (SELECT SUM(revenue) FROM by_month)), ABS((SELECT SUM(amount) FROM base) - (SELECT SUM(revenue) FROM by_category))) AS max_difference;
Hard. Lost revenue: value of cancelled and pending orders in Q1, by status. Target: 2 rows: cancelled 800.00, pending 600.00
SELECT o.status, SUM(i.quantity * i.unit_price) AS value FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status <> 'completed' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY o.status ORDER BY o.status;
Hard. Repeat customers: customers with two or more completed Q1 orders. Target: 3 rows: Asha 2, Bilal 2, Dev 2
SELECT c.customer_name, COUNT(*) AS completed_orders FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'completed' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY c.customer_id, c.customer_name HAVING COUNT(*) >= 2 ORDER BY c.customer_id;
Hard. Month-over-month change in completed revenue. Target: 3 rows: 2026-01 1600.00 NULL, 2026-02 1950.00 350.00, 2026-03 2600.00 650.00
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 >= '2026-01-01' AND o.order_date < '2026-04-01' GROUP BY month) SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_prev FROM m ORDER BY month;
Professional. Data-quality section: orphan items, orders without items, non-positive prices and missing emails, one row each. Target: 4 rows: 0, 0, 0 and missing emails 1 (Charu)
SELECT 'orphan items' AS check_name, COUNT(*) AS problems FROM order_items i LEFT JOIN orders o ON o.order_id = i.order_id WHERE o.order_id IS NULL UNION ALL SELECT 'orders without items', COUNT(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.order_id) UNION ALL SELECT 'non-positive prices', COUNT(*) FROM products WHERE price <= 0 UNION ALL SELECT 'missing emails', COUNT(*) FROM customers WHERE email IS NULL;
Professional. One-row executive summary: period, completed orders, revenue, average order value, buying customers and top product. Target: 1 row: 2026-Q1, 6, 6150.00, 1025.00, 3, Keyboard
WITH base AS (SELECT o.order_id, o.customer_id, i.product_id, i.quantity * i.unit_price AS amount FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' AND o.order_date >= '2026-01-01' AND o.order_date < '2026-04-01') SELECT '2026-Q1' AS period, COUNT(DISTINCT order_id) AS completed_orders, SUM(amount) AS revenue, ROUND(SUM(amount) / COUNT(DISTINCT order_id), 2) AS average_order_value, COUNT(DISTINCT customer_id) AS buying_customers, (SELECT p.product_name FROM base b JOIN products p ON p.product_id = b.product_id GROUP BY p.product_id, p.product_name ORDER BY SUM(b.amount) DESC LIMIT 1) AS top_product FROM base;
Professional. Write the Limitations section of your README as SQL comments: at least three honest limits of this analysis. Target: 3+ comment lines, e.g. tiny sample, status rule, no calendar table, prices at time of sale
/* Limitations
1. Only 8 orders in Q1: trends and rankings are not statistically meaningful.
2. Revenue excludes 1 pending (600.00) and 1 cancelled (800.00) order.
3. Months with no orders would be missing without a calendar table.
4. unit_price is the price charged at the time of sale, not today's catalogue price. */