SQL Window Frames: Running Total in LeetCode

This guide shows you how to solve LeetCode’s running total problems using SQL window frames. You’ll learn the exact syntax for ROWS BETWEEN and RANGE BETWEEN, how to build a cumulative sum, and how to avoid the classic pitfalls that trip up most submissions. By the end, you’ll be able to write a window frame query that passes any ‘running total’ or ‘cumulative sum’ problem on LeetCode.

What You’ll Need

• A LeetCode account (free tier is fine) — you’ll practice directly on their platform.
• Basic knowledge of SELECT, FROM, ORDER BY, and aggregate functions like SUM().
• Understanding of the OVER() clause — even a beginner-level grasp is enough to start.
• Familiarity with the specific problem’s table schema (usually provided in the question).

Step-by-Step

1. Read the problem and identify the target column. Most running total problems ask for a cumulative sum of a numeric column (e.g., amount, salary, weight) ordered by a date or an ID. Write down the column names and the sort order.
2. Write the basic query skeleton. Start with SELECT the columns you need to output, plus a new column for the running total. For example, if the problem wants person_id, day, and total_amount, your select list will include those.
3. Add the window function with SUM(). In the select list, add SUM(amount) OVER (ORDER BY day). This alone gives a running total in many databases because the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
4. Explicitly define the window frame. To be safe and explicit (and to pass stricter databases like PostgreSQL), write SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). This tells the database: start from the first row in the partition and add up to the current row.
5. Add a PARTITION BY if the problem groups by a category. For example, if you need a running total per customer, add PARTITION BY customer_id before the ORDER BY. The frame then resets for each customer.
6. Handle ties in the ordering column. If the ORDER BY column has duplicate values, decide whether you want the running total to include all tied rows at once (use RANGE) or one row at a time (use ROWS). LeetCode problems usually expect ROWS unless stated otherwise.
7. Write the full query and test it. Put it all together. For example: SELECT person_id, day, SUM(amount) OVER (PARTITION BY person_id ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM transactions; Run it on the sample input to verify.
8. Check the output format. Ensure the column alias matches what the problem expects. LeetCode often checks column names, so use the exact alias from the expected output.
9. Submit and debug if needed. If the sample passes, submit. If it fails, look at the error message — usually it’s a tie-handling or partition issue.

Common Mistakes

• Forgetting the ORDER BY inside OVER(). Without it, the window frame is the entire partition, and you get a grand total, not a running total.
• Using RANGE when you need ROWS. If the ordering column has duplicates, RANGE will include all rows with the same value in the current row’s frame, which can double-count or skip rows.
• Omitting PARTITION BY when the problem asks for per-group totals. You’ll get a single running total across all groups.
• Misnaming the output column. LeetCode’s judge compares column names; a mismatch causes a runtime error.
• Using ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING by mistake. That gives a reverse running total (from current to end), which is rarely what’s asked.

Tips

• Always write the frame explicitly. It’s self-documenting and avoids default-behavior surprises across different SQL dialects.
• Use ROWS for most LeetCode problems. They usually want a simple cumulative sum row by row, not tied-value grouping.
• Test with a small custom dataset. Create a quick table with 3-4 rows and run your query to see the intermediate results.
• Remember that SUM() with a window frame ignores NULL values. If your column has nulls, the running total will skip them — that’s usually fine, but be aware.
• Practice on LeetCode problem #534 (Game Play Analysis III) and #178 (Rank Scores) to reinforce the concept.

Troubleshooting

• Query returns a grand total instead of a running total. Check if you have an ORDER BY inside OVER(). Without it, the frame is the whole partition.
• Running total resets unexpectedly. Verify your PARTITION BY clause — it should match the grouping in the problem statement.
• Duplicate rows cause wrong totals. Switch from RANGE to ROWS in the frame definition.
• Error: ‘window frame clause is not supported’ (in older MySQL). Upgrade to MySQL 8.0+ or use a workaround with a self-join (but that’s slower and not recommended for LeetCode).
• Column alias not recognized in the outer query. If you wrap the window query in a subquery, you can’t reference the alias in the same SELECT — use the full expression or a subquery.
• Still failing? Check the expected output’s ordering. LeetCode may require a specific ORDER BY at the end of the query, independent of the window’s internal ordering.

Frequently Asked Questions

What is the difference between ROWS and RANGE in window frames?

ROWS counts physical rows — each row is a separate unit. RANGE counts rows based on the value of the ordering column; if two rows have the same value, they are treated as one unit. For running totals, ROWS is more intuitive and commonly expected.

Can I use a running total without a window function?

Yes, with a self-join or a correlated subquery, but it’s slower and more complex. Window functions are the standard, efficient way in modern SQL.

What does UNBOUNDED PRECEDING mean?

It means the frame starts at the first row of the partition. Combined with CURRENT ROW, it includes all rows from the start up to the current row.

Does the default frame differ between databases?

Yes. PostgreSQL, SQL Server, and Oracle default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. MySQL 8.0+ also uses that default, but older versions may not support frames at all.

How do I handle NULLs in the summed column?

SUM() ignores NULLs by default. If you want to treat NULL as 0, use COALESCE(column, 0) inside the sum.

Can I use a running total with a condition, like only positive amounts?

Yes, you can filter rows before the window function using a WHERE clause, or use a CASE expression inside the SUM() to conditionally include values.

Related

• What Is Jailbreaking an iPhone? Risks & Reality
• Audio Device Disabled in Windows: How to Restore Sound
• Outlook Email Photos: How to Save Attachments and Images