SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.
Compute monthly revenue per customer from lb_orders. Find months where spend declined versus the prior month present in the data for that customer. Return customer_id, month_start, month_revenue, prev_month_revenue for those declining months. Order by customer_id, month_start.
Input: lb_orders (C01 monthly totals) customer_id | month_start | month_revenue C01 | 2024-01-01 | 1200 C01 | 2024-02-01 | 35 C01 | 2024-03-01 | 40 Output: customer_id | month_start | month_revenue | prev_month_revenue C01 | 2024-02-01 | 35 | 1200 Why this passes: February is lower than January. March rises versus February so it is excluded.
Topics: lakebench, sql, lag, trends.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Flag months where spend declined versus the prior month.
Compute monthly revenue per customer from `lb_orders`. Find months where spend declined versus the prior month present in the data for that customer. Return `customer_id`, `month_start`, `month_revenue`, `prev_month_revenue` for those declining months. Order by `customer_id`, `month_start`.