12. Day-over-day revenue growth
Asked in screens shaped like: Meta, Uber, Lyft
Put yesterday’s revenue and the percent change beside each day. The first day has no prior row, and the answer must leave it NULL rather than inventing a zero.
Requirements
- Columns: order_day, revenue, prev_revenue, pct_change
- Only status = 'completed'
- prev_revenue is the previous day’s revenue by order_day
- pct_change is (revenue - prev_revenue) / prev_revenue as a percentage, rounded to 2 decimals; NULL on the first day
- Order by order_day
Expected output
order_day, revenue, prev_revenue, pct_change
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
LAG(revenue) OVER (ORDER BY order_day) reaches back one row.
Hint 2
Arithmetic with NULL yields NULL, which is exactly the wanted first-row behaviour.
Hint 3
Multiply by 100.0 for a percentage.
WITH daily AS (
SELECT DATE(ordered_at) AS order_day, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(ordered_at)
)
SELECT order_day,
ROUND(revenue, 2) AS revenue,
ROUND(LAG(revenue) OVER (ORDER BY order_day), 2) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY order_day)) / LAG(revenue) OVER (ORDER BY order_day), 2) AS pct_change
FROM daily
ORDER BY order_day;Why it works
LAG is the readable way to reach the previous row; a self-join on date - 1 also works but breaks on missing days. NULL propagation gives the first row the right answer for free.
Run your query to see the result set.
Submit to run the checks.
Why it works
LAG is the readable way to reach the previous row; a self-join on date - 1 also works but breaks on missing days. NULL propagation gives the first row the right answer for free.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems