Author(s): Priyanka Shah

Originally published on Towards AI.

The queries I actually reach for at work — and the frame-clause default that quietly breaks all of them.

For about a year, I wrote this query:

The article explains why SQL window functions are often the simplest way to keep row-level detail while adding group context, contrasting them with GROUP BY and the self-join/subquery workarounds they replace. It walks through seven practical patterns analysts use constantly—running totals, ranking choices (ROW_NUMBER vs RANK vs DENSE_RANK), top-N per group, deduplication, period-over-period comparisons with LAG/LEAD (including NULLIF and missing-period pitfalls), moving averages (with date spines and row vs day semantics), and share-of-total calculations for Pareto analysis. It then highlights the “gotcha” default frame behavior (RANGE vs ROWS) that can silently break running totals and FIRST/LAST_VALUE results, provides performance guidance (sorting cost, indexing, reuse of window definitions, named windows), and ends with a cheat sheet and rules of thumb for when to compute in SQL vs DAX.

Read the full blog for free on Medium.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI