For 2026 Jan-Mar: return productname with three columns janrev, febrev, marrev = SUM(quantity price) in each month.…
SQLLevel: IntermediateConditional aggregation and pivots
For 2026 Jan-Mar: return product_name with three columns jan_rev, feb_rev, mar_rev = SUM(quantity * price) in each month. Products with no 2026 Q1 sales excluded (INNER joins are fine).
Table definitions (from the source exercise):
CREATE TABLE products(product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price REAL);
CREATE TABLE orders(order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, amount REAL, rep_id INTEGER, discount REAL);
CREATE TABLE order_items(item_id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER);
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
Approach: Three SUM(CASE WHEN MONTH(...) = m THEN oi.quantity * p.price ELSE 0 END) columns.
SELECT p.product_name,
SUM(CASE WHEN MONTH(o.order_date) = 1 THEN oi.quantity * p.price ELSE 0 END) AS jan_rev,
SUM(CASE WHEN MONTH(o.order_date) = 2 THEN oi.quantity * p.price ELSE 0 END) AS feb_rev,
SUM(CASE WHEN MONTH(o.order_date) = 3 THEN oi.quantity * p.price ELSE 0 END) AS mar_rev
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY p.product_id, p.product_name;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT