Customers with month-over-month spend decline

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

A retention team wants to spot customers who are spending less than before. For each customer, they compare each month's revenue with the previous month in which that customer ordered. From lb_orders, first calculate the revenue of each customer in each calendar month. Then find the months where the revenue is lower than in the previous month shown for that customer. Return customer_id, month_start, month_revenue and prev_month_revenue for these declining months only. Sort by customer_id and month_start.

Requirements

• Add up the revenue for each customer and month.
• Use LAG to get the previous month of the same customer.
• Keep only the rows where month_revenue < prev_month_revenue.
• Return customer_id, month_start, month_revenue and prev_month_revenue, sorted by customer_id and month_start.

Constraints

• The table is lb_orders.
• Group first, then apply LAG(month_revenue) OVER (PARTITION BY customer_id ORDER BY month_start).
• Compare with the previous month that exists in the data. Do not fill in months where the customer did not order.

Examples

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