Window functions are one of the most practical features in SQL for analytics work. They let you calculate metrics “across” related rows while still returning one row per record in your result set. Instead of collapsing rows the way GROUP BY does, window functions keep detail intact and add computed values alongside it. This is exactly what you need for leaderboard logic, running totals, trend analysis, cohort comparisons, and many other real-world reporting tasks you’ll encounter while building skills for a data scientist course in Ahmedabad.
1) The Core Pattern: OVER (PARTITION BY … ORDER BY …)
A window function becomes powerful when you define what group to look at and how to order it. The OVER() clause is where that happens:
- PARTITION BY: splits data into independent groups (like “per customer” or “per store”).
- ORDER BY: defines a sequence inside each group (like “by date”).
- Frame clause (optional): defines which rows around the current row are included (like “last 7 days”).
Example: running revenue per customer by date:
SELECT
customer_id,
order_date,
revenue,
SUM(revenue) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders;
This returns every order row with an extra running total, without losing row-level detail.
2) Ranking Functions for Leaderboards and Tie Handling
Ranking functions help you create ordered results within each partition. Common ones include:
- ROW_NUMBER() gives a unique sequence (no ties).
- RANK() leaves gaps when ties occur (1, 1, 3…).
- DENSE_RANK() avoids gaps (1, 1, 2…).
- NTILE(n) splits rows into n buckets (useful for quartiles/deciles).
Example: top products per category by sales:
SELECT
category,
product_id,
total_sales,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY total_sales DESC
) AS sales_rank
FROM product_sales;
To filter the top 3 products per category, you can wrap it:
SELECT *
FROM (
SELECT
category, product_id, total_sales,
DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rnk
FROM product_sales
) t
WHERE rnk <= 3;
This pattern is extremely common in analytics dashboards and is a core competency expected from learners in a data scientist course in Ahmedabad.
3) Windowed Aggregations for Running, Rolling, and Comparative Metrics
Windowed aggregations (SUM, AVG, COUNT, MIN, MAX) become advanced when you control the frame. Typical use cases include:
Running totals
Use UNBOUNDED PRECEDING to include all prior rows in the partition.
Rolling averages
Use a limited frame like “current row and previous 6 rows” for a 7-point moving average:
SELECT
store_id,
sales_date,
sales,
AVG(sales) OVER (
PARTITION BY store_id
ORDER BY sales_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS sales_7day_avg
FROM daily_store_sales;
Share-of-total within a partition
Compute category share inside each region:
SELECT
region,
category,
revenue,
revenue * 1.0 /
SUM(revenue) OVER (PARTITION BY region) AS category_share_in_region
FROM region_category_revenue;
These calculations help you build “complex metrics” that are easy to explain and validate because each metric is expressed clearly in SQL.
4) Value Functions for Change, Context, and Sequence Analysis
Value functions let you reference other rows in a partition without self-joins:
- LAG() and LEAD() compare the current row to previous/next rows.
- FIRST_VALUE() and LAST_VALUE() pull boundary values.
- NTH_VALUE() fetches the nth row in a frame.
Example: day-over-day change in revenue:
SELECT
order_date,
revenue,
revenue – LAG(revenue) OVER (ORDER BY order_date) AS day_change
FROM daily_revenue;
Example: compare each customer’s monthly spend to their first month:
SELECT
customer_id,
month,
spend,
spend – FIRST_VALUE(spend) OVER (
PARTITION BY customer_id
ORDER BY month
) AS lift_vs_first_month
FROM customer_monthly_spend;
One practical caution: LAST_VALUE() depends heavily on the frame. If you want the true last value of the whole partition, explicitly set the frame to include all rows.
Conclusion
Analytical SQL window functions turn complex metric requirements into clean, auditable queries. By combining ranking functions for ordering, windowed aggregations for cumulative and rolling metrics, and value functions for context and change, you can solve many advanced reporting problems without messy self-joins or multiple query passes. If you are preparing for hands-on analytics roles through a data scientist course in Ahmedabad, mastering OVER(), partitions, ordering, and frames will quickly level up both your SQL confidence and the quality of insights you deliver.
