Most SQL Users Are Leaving Money on the Table

If you’re still writing nested subqueries or doing multiple passes over the same dataset just to get a running total or a rank, you’re working harder than you need to. SQL window functions have been around for years, but most analysts either don’t know them or don’t know when to reach for them. That’s a problem — because in a business context, the difference between a clunky query and a clean one often means the difference between an insight delivered on time and one that’s two days late.

This post covers four window functions that show up constantly in real business work, with examples that go beyond the textbook.

ROW_NUMBER and RANK — More Useful Than You Think

These two are often treated as curiosities, but they solve a very common business problem: finding the latest record per entity. Think of a South African insurance business that stores multiple policy endorsements per client. You don’t want all of them — you want the most recent one per policy. Here’s the pattern:

• Partition by the policy number
• Order by the endorsement date descending
• Filter where ROW_NUMBER equals 1

That’s it. No correlated subquery, no messy GROUP BY workarounds. One clean window function does the job. RANK works similarly but handles ties differently — useful when you need to surface the top three salespeople by revenue, for example, and two of them share second place. Both tools. Different jobs.

SUM OVER — Running Totals Without the Headache

A running total sounds simple until you try to build one in SQL without window functions. Suddenly you’re joining a table to itself or using a cursor and your DBA is looking at you sideways. SUM with an OVER clause changes everything.

Picture a retail chain with stores across Gauteng, the Western Cape and KwaZulu-Natal. The finance team wants to see cumulative monthly sales per region — not just totals, but a rolling picture of how each region is tracking through the year. With SUM OVER, you partition by region, order by month, and specify a row range. The result is a clean, ordered dataset that finance can drop straight into their reporting tool. No pivoting, no Excel gymnastics.

The key detail most people miss: specifying ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives you a true running total. Leave that out and your results may surprise you — not in a good way.

LAG and LEAD — Comparing This Period to Last Period

Month-on-month comparisons are bread and butter in any analytics environment. But most analysts build these by joining the same table twice — once for the current month, once for the previous month — and matching on date offsets. It works, but it’s slow to write and slower to read.

LAG pulls a value from a previous row. LEAD pulls from the next one. Both are defined within a partition and an order. A manufacturing client tracking production output week by week can use LAG to instantly calculate the change from last week — right inside the SELECT statement. No self-join. No subquery. One line of SQL.

This matters in fast-moving environments where queries get reused, modified and handed to other team members. Clean, readable SQL reduces errors and speeds up collaboration. In a consulting context, it also makes your work easier to audit — which matters when the numbers are going into a board report.

NTILE — Segmenting Data the Right Way

NTILE divides a result set into a specified number of roughly equal buckets. It sounds niche, but it’s one of the most practical tools for customer segmentation and performance banding.

A credit provider might want to split their customer base into four risk quartiles based on repayment behaviour. NTILE(4) over an ordered score gives you that instantly — no hardcoded thresholds, no manual breakpoints. The segments adjust automatically as new data comes in. That’s the kind of dynamic, low-maintenance solution that holds up in production.

The same logic applies to banding employee performance scores, tiering supplier reliability, or segmenting product lines by margin contribution. Wherever you need ranked groupings that update with the data, NTILE earns its place.

Practical Takeaways

• Window functions operate on a set of rows related to the current row — they do not collapse the result set like GROUP BY does
• Always define your PARTITION BY and ORDER BY carefully — getting either wrong produces results that look right but aren’t
• ROW_NUMBER is your go-to for deduplication and latest-record problems
• LAG and LEAD replace most self-joins involving time-series comparisons
• NTILE is underused — if you’re still hardcoding quartile boundaries, stop and try it

Window functions are not advanced SQL. They’re standard SQL that most analysts simply haven’t had a reason to practise yet. Once you build them into your regular toolkit, you’ll find yourself writing faster, cleaner queries — and spending less time explaining why a report took three days to pull.

If your team is spending more time wrestling with SQL than generating insights, that’s a conversation worth having. Reach out to us at [email protected] — we help South African businesses get more value from their data, faster.

oCode360 (t/a JVW Business Solutions (Pty) Ltd) — Making data make sense.