Drill 11 of 12 in E-commerce questions
0

Interview Question Bank · Monthly totals · pivot or LAG · strict increase with no gaps

11. Spend rose every month

HardE-commerceQuestion #95

Concept: Monthly totals · pivot or LAG · strict increase with no gapsshow

'Grew every month' has two traps: values must be totals per month (not per order), and a missing month is a break, not a skip. Pivot each month into its own column with SUM(...) FILTER and compare neighbours with >: a missing month is NULL, and NULL > x is never true. For longer periods, LAG over monthly totals works too, but also require the number of months to equal the full period.

Example (different data):

WITH w AS (
  SELECT runner_id,
    SUM(km) FILTER (WHERE week = 1) AS w1,
    SUM(km) FILTER (WHERE week = 2) AS w2
  FROM runs GROUP BY runner_id
)
SELECT runner_id FROM w WHERE w2 > w1;

Your task

Quillmere's loyalty team wants to reward customers whose spending grew every single month of Q1 2025.

Using completed orders, total each customer's spend (quantity * unit_price) in January, February and March 2025. Return name, jan_spend, feb_spend and mar_spend for customers who bought in all three months with feb_spend strictly greater than jan_spend and mar_spend strictly greater than feb_spend. Sort by name.

Starting Postgres in your browser…

Checking your access…