Week 02

Window Functions II: LAG/LEAD, Running Totals & Gaps

Day 92.5 to 3 hrs

Compare rows over time

You already know how to rank rows within a group. Now we will compare neighbouring rows, calculate totals up to a date, and find runs of consecutive activity.

Before writing the query, decide what "previous" means. The previous available record may not be from the previous calendar day.

The ask, as the OA words it	What it really needs
"Revenue growth compared to the previous day"	LAG on the previous row
"Cumulative revenue till each date"	running SUM inside the window
"Users active on 3 or more consecutive days"	gaps and islands
"Each day's share of that city's total"	partition total, no ORDER BY
"Average time between two logins per user"	LEAD, then a date difference

The function is only part of the answer. The partition, ordering and window frame decide which rows contribute to each result. Check those choices against the question before reusing a template.

Two tables run through the whole topic. First, attendance(student, class_date), one row per class attended:

student	class_date
Rahul	2024-08-01
Rahul	2024-08-02
Rahul	2024-08-03
Rahul	2024-08-07
Priya	2024-08-01
Priya	2024-08-02
Priya	2024-08-05
Priya	2024-08-06
Priya	2024-08-07
Aman	2024-08-03

Rahul came three days in a row, then vanished till the 7th. Aman came once.

Second, sales(sale_date, city, amount), the daily revenue of a food delivery outlet: five days in Pune with 1200, 800, 1000, 1500, 500, and three in Nagpur with 600, 900, 300.

What is an Offset Window?

LAG brings a value from an earlier row into the current row. LEAD brings one from a later row. Both use the order you supply inside OVER; neither removes rows.

Start with the input rows, no window at all, just Pune's sales in date order.

SQL

SELECT sale_date, amount
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount
2024-08-01	1200
2024-08-02	800
2024-08-03	1000
2024-08-04	1500
2024-08-05	500

Five rows, one per day, nothing computed yet.

Now look at 08-02. You see 800 there and 1200 just above it, but SQL cannot: a plain SELECT works on one row at a time and has no idea what the row above holds. That is the gap LAG closes.

SQL

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS prev_amount
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	prev_amount
2024-08-01	1200	NULL
2024-08-02	800	1200
2024-08-03	1000	800
2024-08-04	1500	1000
2024-08-05	500	1500

The amount column shifted down by one and became a new column. The first row has nobody in front of it, so it gets NULL.

OVER (...) says "look at other rows". The ORDER BY inside it is not the sort of the output, it is the order the rows stand in the queue, and it decides who counts as "previous". it and the ORDER BY at the end of the query are two different things.

Once the previous value sits in the same row, ordinary arithmetic finishes the job:

SQL

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY sale_date) AS change
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	prev_amount	change
2024-08-01	1200	NULL	NULL
2024-08-02	800	1200	-400
2024-08-03	1000	800	200
2024-08-04	1500	1000	500
2024-08-05	500	1500	-1000

Sales fell by 400 on the 2nd, rose by 500 on the 4th. The first row stays NULL because NULL in any arithmetic gives NULL, and that NULL is a signal, not a bug. LAG(col, n, default) takes two more optional arguments: how many rows back, and what to put instead of NULL at the boundary.

Adding PARTITION BY

PARTITION BY splits the rows into independent groups before the window runs, and restarts everything at each new group. Without it, one long queue. With it, one queue per student, per city, per product. You need it the moment two entities share one table:

student	class_date	prev_date without PARTITION	prev_date with PARTITION BY student
Aman	2024-08-03	NULL	NULL
Priya	2024-08-01	2024-08-03	NULL
Priya	2024-08-02	2024-08-01	2024-08-01
Priya	2024-08-05	2024-08-02	2024-08-02
Rahul	2024-08-01	2024-08-07	NULL
Rahul	2024-08-02	2024-08-01	2024-08-01

In the third column Priya's first class is compared with Aman's, and Rahul's first with Priya's last. Both are nonsense. The fourth restarts at each student, which is why it has three NULLs.

The second family: a running total

The other half of today is an ordinary aggregate, SUM or AVG or COUNT, written with OVER. One small change flips its meaning, so build it in two steps. Step 1, no ORDER BY inside:

SQL

SELECT sale_date, amount,
       SUM(amount) OVER (PARTITION BY city) AS city_total
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	city_total
2024-08-01	1200	5000
2024-08-02	800	5000
2024-08-03	1000	5000
2024-08-04	1500	5000
2024-08-05	500	5000

Every Pune row sees the same 5000, the partition total, which is the number you divide by for a day's share of the city. Step 2, add ORDER BY inside the window and nothing else:

SQL

SELECT sale_date, amount,
       SUM(amount) OVER (PARTITION BY city ORDER BY sale_date) AS running_total
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	running_total
2024-08-01	1200	1200
2024-08-02	800	2000
2024-08-03	1000	3000
2024-08-04	1500	4500
2024-08-05	500	5000

The column grows row by row, 1200, then 1200 plus 800, and so on. Same function, same table, two completely different answers.

Why does one word change so much? Adding an ORDER BY to an aggregate window quietly gives it a frame, the slice of the partition each row may see. With no ORDER BY the frame is the whole partition. With one, it runs from the start of the partition to the current row, which is exactly a total so far.

partition the rows
order inside the window
pick a frame
compute per row

Warning
Warning: with an ORDER BY and no explicit frame, SQLite uses a RANGE frame, and RANGE pulls in every row sharing the ordering value at once. On the full sales table both cities have a 2024-08-01 row, so SUM(amount) OVER (ORDER BY sale_date) shows 1800 on the first row instead of 1200. When rows can share that value, write the frame yourself: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The Pattern

Use these four decisions to organise the query.

Decide the partition. Per student, per city, per product, or none at all.
Decide the order inside the window, with a tie-break so the answer is reproducible.
Pick the tool. LAG or LEAD for a neighbour, a running SUM or AVG for cumulative work, FIRST_VALUE and LAST_VALUE for the group's anchor, NTILE for buckets.
Wrap it in a CTE and filter outside. A CTE is the WITH name AS (...) block, a named temporary result you can select from. You need it because the interesting filter, gap_days > 3 or running_total > 5000, can only run after the window does.

Dry run. Find every student whose longest gap between two classes is 3 days or more.

SQL

WITH gaps AS (
  SELECT student, class_date,
         CAST(julianday(class_date)
              - julianday(LAG(class_date) OVER (PARTITION BY student ORDER BY class_date))
              AS INT) AS gap_days
  FROM attendance
)
SELECT student, MAX(gap_days) AS longest_gap
FROM gaps
WHERE gap_days IS NOT NULL
GROUP BY student
HAVING MAX(gap_days) >= 3
ORDER BY longest_gap DESC;

Read the query line by line:

The CTE named gaps runs first and gives back all 10 attendance rows, each with one extra column.
LAG(class_date) OVER (PARTITION BY student ORDER BY class_date) fetches that student's previous class date.
julianday() turns both dates into day numbers so they can be subtracted, and CAST(... AS INT) makes the difference whole days.
WHERE gap_days IS NOT NULL drops each student's first row, which has no previous class.
GROUP BY student collapses the rest into one row per student, and MAX(gap_days) picks that student's worst gap.
HAVING runs after grouping, so it can test the group's MAX. WHERE cannot, since at WHERE time no group exists. The final ORDER BY sorts what gets printed.

step	what happens	rows left
FROM gaps	the CTE has already run the window, every row carries gap_days	10
WHERE gap_days IS NOT NULL	the three first-class rows are dropped	7
GROUP BY student	Rahul 3 rows, Priya 4 rows, Aman is gone already	2
HAVING MAX(gap_days) >= 3	Rahul has 4, Priya has 3, both pass	2
SELECT	student, longest_gap	2
ORDER BY longest_gap DESC	Rahul 4 first, then Priya 3	2

student	longest_gap
Rahul	4
Priya	3

Aman never reaches GROUP BY. His only row had a NULL gap and WHERE removed it, which is right here, since one class means no gap. But note this point. In a question like "students who never missed a class" that same student must appear, so there you handle the NULL instead of dropping it.

Check what you learned

On the sales table, SUM(amount) OVER (PARTITION BY city) with no ORDER BY inside the window returns what on the Pune row of 2024-08-02?

Query Templates

SQL

-- 1. Compare a row with the previous one: gap, growth, repeat detection.
WITH marked AS (
  SELECT sale_date, amount,
         LAG(amount) OVER (ORDER BY sale_date) AS prev_amount
  FROM sales WHERE city = 'Pune'
)
SELECT sale_date, amount, prev_amount,
       PRINTF('%.2f', (amount - prev_amount) * 100.0 / prev_amount) AS growth_pct
FROM marked
WHERE prev_amount IS NOT NULL
ORDER BY sale_date;

-- 2. Running total and 3-day moving average.
SELECT sale_date, amount,
       SUM(amount) OVER (PARTITION BY city ORDER BY sale_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       PRINTF('%.2f', AVG(amount) OVER (PARTITION BY city ORDER BY sale_date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW))        AS moving_avg_3
FROM sales
ORDER BY city, sale_date;

-- 3. Share of the group.
SELECT city, sale_date, amount,
       PRINTF('%.2f', amount * 100.0 / SUM(amount) OVER (PARTITION BY city)) AS pct_of_city
FROM sales
ORDER BY city, sale_date;

-- 4. Gaps and islands: consecutive dates collapse into one island.
WITH d AS (SELECT DISTINCT student, class_date FROM attendance),
islands AS (
  SELECT student, class_date,
         DATE(class_date,
              '-' || ROW_NUMBER() OVER (PARTITION BY student ORDER BY class_date) || ' days'
         ) AS island
  FROM d
)
SELECT student, COUNT(*) AS streak_days,
       MIN(class_date) AS start_date, MAX(class_date) AS end_date
FROM islands
GROUP BY student, island
HAVING COUNT(*) >= 3
ORDER BY student;

The sample has no zero previous amounts. For other data, decide how to represent undefined growth when the previous value is zero and handle it before formatting.

Template 1 on Pune gives this. The 2nd's growth is (800 - 1200) * 100.0 / 1200, a fall of 33.33 percent:

sale_date	amount	prev_amount	growth_pct
2024-08-02	800	1200	-33.33
2024-08-03	1000	800	25.00
2024-08-04	1500	1000	50.00
2024-08-05	500	1500	-66.67

Only 4 rows, not 5. WHERE prev_amount IS NOT NULL removed 08-01, since a growth percentage for the first day does not exist.

Template 2 on Pune, with its two computed columns worth reading side by side:

sale_date	amount	running_total	moving_avg_3
2024-08-01	1200	1200	1200.00
2024-08-02	800	2000	1000.00
2024-08-03	1000	3000	1000.00
2024-08-04	1500	4500	1100.00
2024-08-05	500	5000	1000.00

The running total only adds, it never forgets. The moving average does forget: on 08-04 its window is 800, 1000, 1500 and day one's 1200 has dropped out.

On the first two rows the frame is short, so it averages 1 row and then 2 instead of 3, and SQLite does not pad with NULLs. If the statement wants those two days blank, wrap the expression in CASE WHEN ROW_NUMBER() OVER (PARTITION BY city ORDER BY sale_date) < 3 THEN NULL ELSE ... END.

Variations

Variation 1 - Gaps and islands (streaks)

This method works well when a streak means consecutive calendar dates and you have one row per person per date.

Subtract the row number, counted in days, from the date. Inside a run of consecutive days both grow by 1, so the difference stays frozen. That frozen value is the island id, and it changes only where a day was skipped.

student	class_date	rn	island = date minus rn days
Priya	2024-08-01	1	2024-07-31
Priya	2024-08-02	2	2024-07-31
Priya	2024-08-05	3	2024-08-02
Priya	2024-08-06	4	2024-08-02
Priya	2024-08-07	5	2024-08-02
Rahul	2024-08-01	1	2024-07-31
Rahul	2024-08-02	2	2024-07-31
Rahul	2024-08-03	3	2024-07-31
Rahul	2024-08-07	4	2024-08-03

Priya's first two rows share 2024-07-31, her last three share 2024-08-02, and the jump is at 08-05 because she skipped 08-03 and 08-04.

Group by (student, island) and every group is one unbroken run, so COUNT(*) is its length and MIN and MAX its ends. HAVING COUNT(*) >= 3 then prints:

student	streak_days	start_date	end_date
Priya	3	2024-08-05	2024-08-07
Rahul	3	2024-08-01	2024-08-03

HAVING dropped Priya's 2-day run and Rahul's lone 08-07, and Aman never had 3 days.

Check the result. With ORDER BY student, the practice output is:

Text

Priya|3|2024-08-05|2024-08-07
Rahul|3|2024-08-01|2024-08-03

So the ORDER BY is not decoration. Drop ORDER BY student and the two lines can come out either way, and hidden cases fail on a correct result.

Always deduplicate the dates first. If Priya has two rows for 08-06 she gets two row numbers, the arithmetic shifts from there on, and her streak breaks in half:

student	class_date	rn	island	correct island
Priya	2024-08-05	3	2024-08-02	2024-08-02
Priya	2024-08-06	4	2024-08-02	2024-08-02
Priya	2024-08-06	5	2024-08-01	(duplicate, should not exist)
Priya	2024-08-07	6	2024-08-01	2024-08-02

Her one 3-day run is now two runs of 2, so HAVING COUNT(*) >= 3 drops her completely and the output shrinks to one line, Rahul|3|2024-08-01|2024-08-03. Put SELECT DISTINCT student, class_date first and it disappears. The same idea works on integers, where value - ROW_NUMBER() is constant inside a consecutive run.

Variation 2 - Next event and time between events

LEAD is LAG facing forward. LEAD(class_date) OVER (PARTITION BY student ORDER BY class_date) gives the next class instead of the previous:

student	class_date	next_date
Priya	2024-08-01	2024-08-02
Priya	2024-08-02	2024-08-05
Priya	2024-08-05	2024-08-06
Priya	2024-08-06	2024-08-07
Priya	2024-08-07	NULL

The NULL has moved to the bottom, since the last row of a partition has nobody behind it. Keep NULL when there is no next event. Supply a default only if the question gives an end date to compare against.

For durations, julianday(a) - julianday(b) gives days and strftime('%s', a) - strftime('%s', b) gives seconds, so divide by 60 for minutes. To add a duration from a column: datetime(x, '+' || minutes || ' minutes').

Variation 3 - FIRST_VALUE, LAST_VALUE and NTILE

FIRST_VALUE and LAST_VALUE return the value from the first or last row the frame can see, and NTILE(k) chops the ordered rows into k near equal buckets.

SQL

SELECT sale_date, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY city ORDER BY sale_date) AS first_day,
       LAST_VALUE(amount)  OVER (PARTITION BY city ORDER BY sale_date
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND UNBOUNDED FOLLOWING)     AS last_day,
       LAST_VALUE(amount)  OVER (PARTITION BY city ORDER BY sale_date) AS last_wrong,
       NTILE(2) OVER (PARTITION BY city ORDER BY amount DESC)          AS half
FROM sales WHERE city = 'Pune' ORDER BY sale_date;

sale_date	amount	first_day	last_day	last_wrong	half
2024-08-01	1200	1200	500	1200	1
2024-08-02	800	1200	500	800	2
2024-08-03	1000	1200	500	1000	1
2024-08-04	1500	1200	500	1500	1
2024-08-05	500	1200	500	500	2

Compare last_day with last_wrong. The correct one is 500 everywhere, the last real Pune row. The wrong one copies the row's own amount, because the default frame ends at the current row, so the last row the window can see is the row itself. Use UNBOUNDED FOLLOWING to include the end of the partition. FIRST_VALUE already sees the first row because the default frame starts there.

NTILE(2) puts 1500, 1200, 1000 in bucket 1 and 800, 500 in bucket 2. When the rows do not divide evenly, the extra one goes to the earlier bucket.

Variation 4 - Median without PERCENTILE_CONT

When a median aggregate is unavailable, use window functions to number rows and count the partition. Keep the middle row, or the middle two when the count is even.

SQL

WITH ranked AS (
  SELECT city, amount,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount) AS rn,
         COUNT(*)     OVER (PARTITION BY city)                 AS n
  FROM sales
)
SELECT city, AVG(amount) AS median_amount
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY city ORDER BY city;

city	median_amount
Nagpur	600.0
Pune	1000.0

Pune has 5 rows, so (5+1)/2 and (5+2)/2 are both 3 and only the middle value 1000 survives. For an even count they give the two central rows and AVG takes their mean.

Check what you learned

LAST_VALUE(amount) OVER (PARTITION BY city ORDER BY sale_date) returned 800 on the 2024-08-02 Pune row instead of 500. Why?

Common Mistakes

A running total instead of a group total. SUM(x) OVER (PARTITION BY city) repeats the city's total. Adding an order and a cumulative frame changes the calculation.
LAST_VALUE seeing only part of the group. Use a frame ending at UNBOUNDED FOLLOWING when you need the final value in the partition.
Dropping boundary rows by accident. The first LAG and last LEAD values are normally NULL. Decide whether to keep, exclude or default these rows based on the requirement.
Duplicate dates breaking a streak. Reduce the input to one row per person per date before using the date-minus-row-number method.
Adding to a date string. class_date + 1 does numeric conversion in SQLite. Use DATE(class_date, '+1 day').
Undefined growth. A missing or zero previous value needs an explicit rule. Do not let formatting turn an undefined percentage into an apparently valid zero.
Filtering too early. Calculate the window column in a CTE before using it in a filter.
Confusing rows with days. A three-row moving average covers three calendar days only when there is exactly one row for every day.

Interview Notes

Q: What does LAG return at the first row? NULL, unless I pass a third argument as the default, and LEAD does the same at the last row of each partition. In a growth question I decide upfront whether that row is dropped or defaulted, since the two give different row counts. Follow-up: compare that NULL with a number and nothing matches.

Q: Difference between ROWS and RANGE frames? ROWS counts physical rows, so 2 PRECEDING is exactly two rows back. RANGE works on the ORDER BY value, so rows sharing a value enter together. With an ORDER BY and no frame written the default is RANGE, which surprises people the moment the sort column has duplicates.

Q: How do you compute a 7-day moving average? AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). I would add that this is 7 rows and not 7 calendar days, so if dates are missing I join a date series first.

Q: Explain gaps and islands. Inside a run of consecutive dates the date and ROW_NUMBER grow at the same rate, so date minus row number is constant. I group by that constant, and each group is one unbroken run. Follow-up: what breaks it? Duplicate rows, so deduplicate first.

Q: Month-over-month growth? Two steps. A CTE aggregating to one row per month with strftime('%Y-%m', d), then outside it (cur - LAG(cur)) * 100.0 / LAG(cur) over the month order, guarding the first month's NULL and keeping the 100.0.

Q: Can you use LAG inside WHERE? No. Window functions run after WHERE and GROUP BY, so the column does not exist yet at filter time. I compute it in a CTE and filter in the outer query.

Q: Running total when several rows share the ordering key? I name the frame explicitly, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Add a unique tie-break to the ordering for a stable row-by-row total. If tied rows should share a cumulative total, RANGE may be the correct choice. Follow-up: and a cumulative percentage? Two windows in one SELECT, the cumulative sum over the partition total.

Quick Test

Check what you learned

On Pune's amounts 1200, 800, 1000, 1500, 500 ordered by date, what does SUM(amount) OVER (ORDER BY sale_date) show on the second row?

Week 02

Window Functions II: LAG/LEAD, Running Totals & Gaps

Day 92.5 to 3 hrs

Compare rows over time

You already know how to rank rows within a group. Now we will compare neighbouring rows, calculate totals up to a date, and find runs of consecutive activity.

Before writing the query, decide what "previous" means. The previous available record may not be from the previous calendar day.

The ask, as the OA words it	What it really needs
"Revenue growth compared to the previous day"	LAG on the previous row
"Cumulative revenue till each date"	running SUM inside the window
"Users active on 3 or more consecutive days"	gaps and islands
"Each day's share of that city's total"	partition total, no ORDER BY
"Average time between two logins per user"	LEAD, then a date difference

The function is only part of the answer. The partition, ordering and window frame decide which rows contribute to each result. Check those choices against the question before reusing a template.

Two tables run through the whole topic. First, attendance(student, class_date), one row per class attended:

student	class_date
Rahul	2024-08-01
Rahul	2024-08-02
Rahul	2024-08-03
Rahul	2024-08-07
Priya	2024-08-01
Priya	2024-08-02
Priya	2024-08-05
Priya	2024-08-06
Priya	2024-08-07
Aman	2024-08-03

Rahul came three days in a row, then vanished till the 7th. Aman came once.

Second, sales(sale_date, city, amount), the daily revenue of a food delivery outlet: five days in Pune with 1200, 800, 1000, 1500, 500, and three in Nagpur with 600, 900, 300.

What is an Offset Window?

LAG brings a value from an earlier row into the current row. LEAD brings one from a later row. Both use the order you supply inside OVER; neither removes rows.

Start with the input rows, no window at all, just Pune's sales in date order.

SQL

SELECT sale_date, amount
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount
2024-08-01	1200
2024-08-02	800
2024-08-03	1000
2024-08-04	1500
2024-08-05	500

Five rows, one per day, nothing computed yet.

Now look at 08-02. You see 800 there and 1200 just above it, but SQL cannot: a plain SELECT works on one row at a time and has no idea what the row above holds. That is the gap LAG closes.

SQL

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS prev_amount
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	prev_amount
2024-08-01	1200	NULL
2024-08-02	800	1200
2024-08-03	1000	800
2024-08-04	1500	1000
2024-08-05	500	1500

The amount column shifted down by one and became a new column. The first row has nobody in front of it, so it gets NULL.

Once the previous value sits in the same row, ordinary arithmetic finishes the job:

SQL

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY sale_date) AS change
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	prev_amount	change
2024-08-01	1200	NULL	NULL
2024-08-02	800	1200	-400
2024-08-03	1000	800	200
2024-08-04	1500	1000	500
2024-08-05	500	1500	-1000

Adding PARTITION BY

student	class_date	prev_date without PARTITION	prev_date with PARTITION BY student
Aman	2024-08-03	NULL	NULL
Priya	2024-08-01	2024-08-03	NULL
Priya	2024-08-02	2024-08-01	2024-08-01
Priya	2024-08-05	2024-08-02	2024-08-02
Rahul	2024-08-01	2024-08-07	NULL
Rahul	2024-08-02	2024-08-01	2024-08-01

In the third column Priya's first class is compared with Aman's, and Rahul's first with Priya's last. Both are nonsense. The fourth restarts at each student, which is why it has three NULLs.

The second family: a running total

The other half of today is an ordinary aggregate, SUM or AVG or COUNT, written with OVER. One small change flips its meaning, so build it in two steps. Step 1, no ORDER BY inside:

SQL

SELECT sale_date, amount,
       SUM(amount) OVER (PARTITION BY city) AS city_total
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	city_total
2024-08-01	1200	5000
2024-08-02	800	5000
2024-08-03	1000	5000
2024-08-04	1500	5000
2024-08-05	500	5000

Every Pune row sees the same 5000, the partition total, which is the number you divide by for a day's share of the city. Step 2, add ORDER BY inside the window and nothing else:

SQL

SELECT sale_date, amount,
       SUM(amount) OVER (PARTITION BY city ORDER BY sale_date) AS running_total
FROM sales
WHERE city = 'Pune'
ORDER BY sale_date;

sale_date	amount	running_total
2024-08-01	1200	1200
2024-08-02	800	2000
2024-08-03	1000	3000
2024-08-04	1500	4500
2024-08-05	500	5000

The column grows row by row, 1200, then 1200 plus 800, and so on. Same function, same table, two completely different answers.

partition the rows
order inside the window
pick a frame
compute per row

Warning
Warning: with an ORDER BY and no explicit frame, SQLite uses a RANGE frame, and RANGE pulls in every row sharing the ordering value at once. On the full sales table both cities have a 2024-08-01 row, so SUM(amount) OVER (ORDER BY sale_date) shows 1800 on the first row instead of 1200. When rows can share that value, write the frame yourself: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The Pattern

Use these four decisions to organise the query.

Decide the partition. Per student, per city, per product, or none at all.
Decide the order inside the window, with a tie-break so the answer is reproducible.
Pick the tool. LAG or LEAD for a neighbour, a running SUM or AVG for cumulative work, FIRST_VALUE and LAST_VALUE for the group's anchor, NTILE for buckets.
Wrap it in a CTE and filter outside. A CTE is the WITH name AS (...) block, a named temporary result you can select from. You need it because the interesting filter, gap_days > 3 or running_total > 5000, can only run after the window does.

Dry run. Find every student whose longest gap between two classes is 3 days or more.

SQL

WITH gaps AS (
  SELECT student, class_date,
         CAST(julianday(class_date)
              - julianday(LAG(class_date) OVER (PARTITION BY student ORDER BY class_date))
              AS INT) AS gap_days
  FROM attendance
)
SELECT student, MAX(gap_days) AS longest_gap
FROM gaps
WHERE gap_days IS NOT NULL
GROUP BY student
HAVING MAX(gap_days) >= 3
ORDER BY longest_gap DESC;

Read the query line by line:

The CTE named gaps runs first and gives back all 10 attendance rows, each with one extra column.
LAG(class_date) OVER (PARTITION BY student ORDER BY class_date) fetches that student's previous class date.
julianday() turns both dates into day numbers so they can be subtracted, and CAST(... AS INT) makes the difference whole days.
WHERE gap_days IS NOT NULL drops each student's first row, which has no previous class.
GROUP BY student collapses the rest into one row per student, and MAX(gap_days) picks that student's worst gap.
HAVING runs after grouping, so it can test the group's MAX. WHERE cannot, since at WHERE time no group exists. The final ORDER BY sorts what gets printed.

step	what happens	rows left
FROM gaps	the CTE has already run the window, every row carries gap_days	10
WHERE gap_days IS NOT NULL	the three first-class rows are dropped	7
GROUP BY student	Rahul 3 rows, Priya 4 rows, Aman is gone already	2
HAVING MAX(gap_days) >= 3	Rahul has 4, Priya has 3, both pass	2
SELECT	student, longest_gap	2
ORDER BY longest_gap DESC	Rahul 4 first, then Priya 3	2

student	longest_gap
Rahul	4
Priya	3

Check what you learned

On the sales table, SUM(amount) OVER (PARTITION BY city) with no ORDER BY inside the window returns what on the Pune row of 2024-08-02?

Query Templates

SQL

-- 1. Compare a row with the previous one: gap, growth, repeat detection.
WITH marked AS (
  SELECT sale_date, amount,
         LAG(amount) OVER (ORDER BY sale_date) AS prev_amount
  FROM sales WHERE city = 'Pune'
)
SELECT sale_date, amount, prev_amount,
       PRINTF('%.2f', (amount - prev_amount) * 100.0 / prev_amount) AS growth_pct
FROM marked
WHERE prev_amount IS NOT NULL
ORDER BY sale_date;

-- 2. Running total and 3-day moving average.
SELECT sale_date, amount,
       SUM(amount) OVER (PARTITION BY city ORDER BY sale_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       PRINTF('%.2f', AVG(amount) OVER (PARTITION BY city ORDER BY sale_date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW))        AS moving_avg_3
FROM sales
ORDER BY city, sale_date;

-- 3. Share of the group.
SELECT city, sale_date, amount,
       PRINTF('%.2f', amount * 100.0 / SUM(amount) OVER (PARTITION BY city)) AS pct_of_city
FROM sales
ORDER BY city, sale_date;

-- 4. Gaps and islands: consecutive dates collapse into one island.
WITH d AS (SELECT DISTINCT student, class_date FROM attendance),
islands AS (
  SELECT student, class_date,
         DATE(class_date,
              '-' || ROW_NUMBER() OVER (PARTITION BY student ORDER BY class_date) || ' days'
         ) AS island
  FROM d
)
SELECT student, COUNT(*) AS streak_days,
       MIN(class_date) AS start_date, MAX(class_date) AS end_date
FROM islands
GROUP BY student, island
HAVING COUNT(*) >= 3
ORDER BY student;

The sample has no zero previous amounts. For other data, decide how to represent undefined growth when the previous value is zero and handle it before formatting.

Template 1 on Pune gives this. The 2nd's growth is (800 - 1200) * 100.0 / 1200, a fall of 33.33 percent:

sale_date	amount	prev_amount	growth_pct
2024-08-02	800	1200	-33.33
2024-08-03	1000	800	25.00
2024-08-04	1500	1000	50.00
2024-08-05	500	1500	-66.67

Only 4 rows, not 5. WHERE prev_amount IS NOT NULL removed 08-01, since a growth percentage for the first day does not exist.

Template 2 on Pune, with its two computed columns worth reading side by side:

sale_date	amount	running_total	moving_avg_3
2024-08-01	1200	1200	1200.00
2024-08-02	800	2000	1000.00
2024-08-03	1000	3000	1000.00
2024-08-04	1500	4500	1100.00
2024-08-05	500	5000	1000.00

The running total only adds, it never forgets. The moving average does forget: on 08-04 its window is 800, 1000, 1500 and day one's 1200 has dropped out.

Variations

Variation 1 - Gaps and islands (streaks)

This method works well when a streak means consecutive calendar dates and you have one row per person per date.

student	class_date	rn	island = date minus rn days
Priya	2024-08-01	1	2024-07-31
Priya	2024-08-02	2	2024-07-31
Priya	2024-08-05	3	2024-08-02
Priya	2024-08-06	4	2024-08-02
Priya	2024-08-07	5	2024-08-02
Rahul	2024-08-01	1	2024-07-31
Rahul	2024-08-02	2	2024-07-31
Rahul	2024-08-03	3	2024-07-31
Rahul	2024-08-07	4	2024-08-03

Priya's first two rows share 2024-07-31, her last three share 2024-08-02, and the jump is at 08-05 because she skipped 08-03 and 08-04.

Group by (student, island) and every group is one unbroken run, so COUNT(*) is its length and MIN and MAX its ends. HAVING COUNT(*) >= 3 then prints:

student	streak_days	start_date	end_date
Priya	3	2024-08-05	2024-08-07
Rahul	3	2024-08-01	2024-08-03

HAVING dropped Priya's 2-day run and Rahul's lone 08-07, and Aman never had 3 days.

Check the result. With ORDER BY student, the practice output is:

Text

Priya|3|2024-08-05|2024-08-07
Rahul|3|2024-08-01|2024-08-03

So the ORDER BY is not decoration. Drop ORDER BY student and the two lines can come out either way, and hidden cases fail on a correct result.

Always deduplicate the dates first. If Priya has two rows for 08-06 she gets two row numbers, the arithmetic shifts from there on, and her streak breaks in half:

student	class_date	rn	island	correct island
Priya	2024-08-05	3	2024-08-02	2024-08-02
Priya	2024-08-06	4	2024-08-02	2024-08-02
Priya	2024-08-06	5	2024-08-01	(duplicate, should not exist)
Priya	2024-08-07	6	2024-08-01	2024-08-02

Variation 2 - Next event and time between events

LEAD is LAG facing forward. LEAD(class_date) OVER (PARTITION BY student ORDER BY class_date) gives the next class instead of the previous:

student	class_date	next_date
Priya	2024-08-01	2024-08-02
Priya	2024-08-02	2024-08-05
Priya	2024-08-05	2024-08-06
Priya	2024-08-06	2024-08-07
Priya	2024-08-07	NULL

Variation 3 - FIRST_VALUE, LAST_VALUE and NTILE

FIRST_VALUE and LAST_VALUE return the value from the first or last row the frame can see, and NTILE(k) chops the ordered rows into k near equal buckets.

SQL

SELECT sale_date, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY city ORDER BY sale_date) AS first_day,
       LAST_VALUE(amount)  OVER (PARTITION BY city ORDER BY sale_date
                                 ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND UNBOUNDED FOLLOWING)     AS last_day,
       LAST_VALUE(amount)  OVER (PARTITION BY city ORDER BY sale_date) AS last_wrong,
       NTILE(2) OVER (PARTITION BY city ORDER BY amount DESC)          AS half
FROM sales WHERE city = 'Pune' ORDER BY sale_date;

sale_date	amount	first_day	last_day	last_wrong	half
2024-08-01	1200	1200	500	1200	1
2024-08-02	800	1200	500	800	2
2024-08-03	1000	1200	500	1000	1
2024-08-04	1500	1200	500	1500	1
2024-08-05	500	1200	500	500	2

NTILE(2) puts 1500, 1200, 1000 in bucket 1 and 800, 500 in bucket 2. When the rows do not divide evenly, the extra one goes to the earlier bucket.

Variation 4 - Median without PERCENTILE_CONT

When a median aggregate is unavailable, use window functions to number rows and count the partition. Keep the middle row, or the middle two when the count is even.

SQL

WITH ranked AS (
  SELECT city, amount,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount) AS rn,
         COUNT(*)     OVER (PARTITION BY city)                 AS n
  FROM sales
)
SELECT city, AVG(amount) AS median_amount
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY city ORDER BY city;

city	median_amount
Nagpur	600.0
Pune	1000.0

Pune has 5 rows, so (5+1)/2 and (5+2)/2 are both 3 and only the middle value 1000 survives. For an even count they give the two central rows and AVG takes their mean.

Check what you learned

LAST_VALUE(amount) OVER (PARTITION BY city ORDER BY sale_date) returned 800 on the 2024-08-02 Pune row instead of 500. Why?

Common Mistakes

A running total instead of a group total. SUM(x) OVER (PARTITION BY city) repeats the city's total. Adding an order and a cumulative frame changes the calculation.
LAST_VALUE seeing only part of the group. Use a frame ending at UNBOUNDED FOLLOWING when you need the final value in the partition.
Dropping boundary rows by accident. The first LAG and last LEAD values are normally NULL. Decide whether to keep, exclude or default these rows based on the requirement.
Duplicate dates breaking a streak. Reduce the input to one row per person per date before using the date-minus-row-number method.
Adding to a date string. class_date + 1 does numeric conversion in SQLite. Use DATE(class_date, '+1 day').
Undefined growth. A missing or zero previous value needs an explicit rule. Do not let formatting turn an undefined percentage into an apparently valid zero.
Filtering too early. Calculate the window column in a CTE before using it in a filter.
Confusing rows with days. A three-row moving average covers three calendar days only when there is exactly one row for every day.

Interview Notes

Q: Can you use LAG inside WHERE? No. Window functions run after WHERE and GROUP BY, so the column does not exist yet at filter time. I compute it in a CTE and filter in the outer query.

Quick Test

Check what you learned

On Pune's amounts 1200, 800, 1000, 1500, 500 ordered by date, what does SUM(amount) OVER (ORDER BY sale_date) show on the second row?