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…