Sales trends.

Capstone: a fixed period, CTE steps, date_trunc, LAG and a safe change calculation. LAG(revenue) OVER (ORDER BY month_start) returns the value from the PREVIOUS AVAILABLE row in the window order — not from the previous calendar month. If a month has no orders it is simply missing as a row, and LAG will point at the month before it. The first row has no predecessor, so LAG returns NULL there.

That is why the change must be guarded: difference = revenue - prev_revenue is NULL for the first month; the percentage 100.0 * (revenue - prev_revenue) / prev_revenue must handle both NULL and division by zero: CASE WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL ELSE ... END. Round with ROUND(x, 2).

You cannot filter on LAG in the same SELECT — compute it in a CTE, then filter in the outer query. "Top N" works the same way: the CTE computes the changes and the outer query adds ORDER BY ... LIMIT N. The window ORDER BY does not sort the result — a final ORDER BY is required for a predictable display.

Common mistakes: assuming LAG means "the previous month" when data is missing; dividing by zero; leaving the first row's NULL unguarded; forgetting the final ORDER BY; using <= instead of < the next first day.

Small example:

SELECT order_date, total, LAG(total) OVER (ORDER BY order_date, id) AS prev_total FROM orders ORDER BY order_date, id;