Zepto hard ROWS BETWEENWindow FunctionsPARTITION BYCOALESCE

For each store in zepto_daily_sales, excluding any row where revenue is NULL (the store was closed, not a zero-revenue day), compute a 3-day moving average of revenue ordered by sale_date — the average of the current trading day and the two trading days immediately before it (fewer days if not enough history exists yet). Return store_name, sale_date, revenue, and moving_avg_3day rounded to 2 decimal places, sorted by store_name and sale_date.

Zepto · Dark Store Revenue Trends — practice this real-world SQL scenario live in your browser.

📖 Story Zepto · Dark Store Revenue Trends

Monday morning at Zepto's Growth Ops team. zepto_daily_sales logs each dark store's daily revenue. Koramangala's store was shut for a compressor repair on 2026-08-04, so that day's revenue was left NULL instead of being logged as a number — it's a trading gap, not a genuine zero-revenue day. The team wants a smooth 3-day moving average of revenue per store to spot real demand trends without one noisy day throwing off the read, and the closure must not get counted as a day the store actually earned zero.

🎯 Your Mission

📋 Table Structure

🗂 zepto_daily_sales

id INTEGER 1

store_name TEXT Koramangala

sale_date TEXT 2026-08-01

revenue REAL 42000

⚡ Step-by-Step Walkthrough

Look at the raw daily revenue, including the day Koramangala was closed

query.sql

SELECT *
FROM zepto_daily_sales
ORDER BY store_name, sale_date;

💡 Explanation

zepto_daily_sales has one row per store per calendar day, not one row per store — store_name repeats across all seven dates for both Koramangala and Indiranagar, with every date from 2026-08-01 to 2026-08-07 covered for both.
Koramangala's 2026-08-04 row has revenue = NULL. This isn't a day the store earned zero — the ops note for that day is a compressor repair that kept the store shut, so no orders were fulfilled and no real revenue number exists to log.
Indiranagar has no gaps at all — every day from 08-01 to 08-07 has a real revenue value climbing steadily from 30000 to 39000, which makes it a clean baseline to compare Koramangala's noisier week against.
Nothing is averaged yet — this step only confirms revenue is genuinely missing for one day, not legitimately zero, which matters because the next two steps handle that missing day very differently.

See the trap: COALESCE-ing the closed day to 0 revenue before averaging

query.sql

SELECT store_name, sale_date, revenue,
       ROUND(AVG(COALESCE(revenue,0)) OVER (
         PARTITION BY store_name ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_wrong
FROM zepto_daily_sales
ORDER BY store_name, sale_date;

💡 Explanation

AVG(COALESCE(revenue,0)) OVER (PARTITION BY store_name ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) forces the closed day's NULL into a literal 0 before the window function ever runs, treating 2026-08-04 exactly like a trading day that earned zero rupees.
The damage isn't limited to 08-04 itself — because the frame looks back two rows, the fabricated 0 also drags down the moving averages on 08-05 and 08-06, so one closure corrupts three days of trend data, not one.
The distortion is concrete in the numbers: 08-05's moving_avg_wrong comes out to 29666.67, barely more than half of the 44833.33 the same day shows once the closed day is properly excluded — a demand dashboard reading this would wrongly flag a crash that never happened.
This is the same COALESCE(x,0) pattern that correctly fixes NULLs in several other questions on this site — the trap here is that COALESCE is the wrong tool exactly when NULL means 'no trading happened' rather than 'the true value is zero'.

Fix it: drop closed days before windowing, then take the 3-day moving average

query.sql

SELECT store_name, sale_date, revenue,
       ROUND(AVG(revenue) OVER (
         PARTITION BY store_name ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3day
FROM zepto_daily_sales
WHERE revenue IS NOT NULL
ORDER BY store_name, sale_date;

💡 Explanation

WHERE revenue IS NOT NULL runs before the window function ever sees a row, so the closed day disappears from Koramangala's history entirely instead of being padded with a fabricated value.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW now looks back over trading days that actually happened — for 08-05 that's 08-02 (45500) and 08-03 (41000) plus 08-05 itself (48000), giving 44833.33, nowhere near the 29666.67 the 0-padded version produced.
Early rows in each partition — 08-01 and 08-02 for both stores — don't have two full preceding trading days yet, so the frame just uses however many exist so far, which is why moving_avg_3day equals the raw revenue on the very first day of each store's history.
Indiranagar's numbers are identical in both the trap and fix versions because it never had a closed day — this bug stays completely invisible unless the data actually has a gap, which is exactly why it can sit undetected in a dashboard until a store shuts for a day.

▶ Practice this live ← Browse all questions