Month-over-month revenue with LAG

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.

A manager wants to see whether revenue is going up or down from month to month. For that, each month needs to sit next to the month before it. From lb_orders, add up amount for each calendar month. Use DATE_TRUNC('month', order_date), which turns any date into the first day of its month, and call it month_start. Return month_start, revenue and prev_month_revenue, where prev_month_revenue is the revenue of the previous row, found with LAG. Sort by month_start.

Requirements

• Add up amount for each month.
• month_start is a date that is the first day of the month.
• prev_month_revenue is the revenue of the row just before, ordered by month_start.
• The first month has no previous row, so its prev_month_revenue is NULL.
• Sort by month_start.

Constraints

• The table is lb_orders.
• Group first, then use LAG(revenue) OVER (ORDER BY month_start) on the grouped result.
• month_start must be a DATE, not a timestamp.

Examples

Input: month_start | revenue 2023-01-01 | 500 2024-01-01 | 1305 2024-02-01 | 35 2024-03-01 | 40 Output: month_start | revenue | prev_month_revenue 2023-01-01 | 500 | NULL 2024-01-01 | 1305 | 500 2024-02-01 | 35 | 1305 2024-03-01 | 40 | 35 Why this passes: LAG looks back 1 row in month order across the months present in the data.

Topics: lakebench, sql, lag, mom.

More SQL interview questions · All interview problems · Learn data engineering