Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

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.

Aggregate total amount in lb_orders by calendar month (DATE_TRUNC('month', order_date) as month_start). Return month_start, revenue, and prev_month_revenue using LAG ordered by month_start. Order by month_start.

Requirements

  • First month has NULL prev_month_revenue.

Constraints

  • month_start must be a DATE (first of month).

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 one 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

intermediate

Month-over-month revenue with LAG

Interview-style drill: Monthly revenue with previous month via LAG.

Aggregate total `amount` in `lb_orders` by calendar month (`DATE_TRUNC('month', order_date)` as `month_start`). Return `month_start`, `revenue`, and `prev_month_revenue` using LAG ordered by `month_start`. Order by `month_start`.