Skip to content
Quantoros

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

Related questions