Why should you care?

Compare each day’s revenue to yesterday for growth % on a fintech dashboard — LAG avoids self-join pain.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

SELECT DATE(placed_at) AS d,
       SUM(total_inr) AS day_revenue,
       LAG(SUM(total_inr)) OVER (ORDER BY DATE(placed_at)) AS prev_day,
       SUM(total_inr) - LAG(SUM(total_inr)) OVER (ORDER BY DATE(placed_at)) AS delta_inr
FROM orders
GROUP BY DATE(placed_at);

What happened?

• Daily revenue ordered by date.
• LAG pulls previous day’s sum into same row.
• delta_inr is day-over-day change without correlated subquery.

Practice next

1. Insert orders across 5 distinct dates.
2. Run query; verify delta on row 2 manually.
3. Use LEAD for next_day preview column.
4. LAG with third arg default 0 for first row.
5. PARTITION BY city via JOIN for city-level deltas.

Remember

LAG = look back; LEAD = look ahead. ORDER BY defines row sequence. Great for period-over-period metrics.

DataFlow GMV alert

Alert fires when delta_inr drops 30% vs LAG day — ops investigates pipeline.

Outcome: Incidents caught before weekly review.