For productid 9 (TrailShoes) in 2026: return month (YYYY-MM), revenue (quantity price), and percent change vs the…
SQLLevel: IntermediateWindow functions
For product_id 9 (TrailShoes) in 2026: return month (YYYY-MM), revenue (quantity * price), and percent change vs the previous month rounded to 1 decimal (NULL for the first month), in month order.
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
- Distinguish a missing value from zero, an empty string and false.
- Test comparison, aggregation and filtering on a tiny missing-value example.
Answer
Approach: Monthly revenue CTE, then LAG in the change formula.
WITH mrev AS (
SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS m,
SUM(oi.quantity * p.price) AS revenue
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 oi.product_id = 9 AND YEAR(o.order_date) = 2026
GROUP BY m
)
SELECT m, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY m))
/ LAG(revenue) OVER (ORDER BY m), 1) AS pct_change
FROM mrev
ORDER BY m;
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