SQL Window Functions for Data Analysts: RANK, LAG and Running Totals Explained

SQL window functions help analysts answer questions that are awkward with basic GROUP BY queries. They can rank products, compare one month with the previous month, calculate cumulative revenue and find the first or latest event for every customer. The important advantage is that a window function performs a calculation across related rows without collapsing those rows into one summary record.

For learners building practical analytics skills, window functions connect database knowledge with real reporting work. They are especially useful before data moves into Excel, Power BI or Tableau because the database can return a clean, analysis-ready result.

How a window function works

A window function usually contains an OVER() clause. The clause defines which rows belong together and how they should be ordered.

SELECT
    order_date,
    region,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY region
        ORDER BY order_date
    ) AS running_revenue
FROM sales;

PARTITION BY region creates a separate calculation for each region. ORDER BY order_date defines the sequence. Unlike a grouped query, the result still shows every sale.

Use ROW_NUMBER to select one record

ROW_NUMBER() assigns a unique sequence within each partition. Analysts often use it to find the latest order, most recent support ticket or first transaction for each customer.

WITH ranked_orders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS row_num
    FROM orders
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

This pattern is safer than selecting a maximum date and then joining blindly, because two records can share the same date.

Understand RANK and DENSE_RANK

RANK() and DENSE_RANK() are useful for leaderboards and performance reports. Both give tied values the same position. The difference appears after a tie: RANK() leaves a gap, while DENSE_RANK() continues with the next number.

For example, if two salespeople tie for first place, RANK() produces 1, 1, 3. DENSE_RANK() produces 1, 1, 2. Choose the method that matches the business rule and document it in the report.

Compare periods with LAG and LEAD

LAG() reads a previous row and LEAD() reads a following row. They make month-over-month comparisons clear.

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change_value
FROM monthly_sales;

Before interpreting the result, confirm that every expected period exists. If a month is missing, the previous row is not necessarily the previous calendar month. A calendar table can solve this problem.

Build running totals and moving averages

Running totals show progress toward a target. Moving averages reduce daily or weekly noise and make the underlying trend easier to see. Define the frame explicitly when the required behaviour matters.

AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_row_average

This example calculates an average over the current row and six preceding rows. It is a seven-row average, not automatically a seven-day average. Duplicate dates or missing dates can change the meaning, so analysts should validate the data grain.

Common mistakes to avoid

• Using the wrong partition and accidentally mixing customers, products or regions.
• Assuming ORDER BY inside OVER() also sorts the final output.
• Ignoring ties when choosing between ROW_NUMBER, RANK and DENSE_RANK.
• Treating row-based frames as calendar-based periods.
• Calculating percentages without handling zero or null denominators.

Practise with a business question

Create a sales table and answer four questions: Who are the top three sellers per region? What was each product’s previous-month revenue? How much revenue accumulated through each date? Which order was the latest for each customer? A single project covering those questions demonstrates SQL, business reasoning and validation skills.

If you want to develop SQL together with Excel, Python, Power BI and dashboard projects, review the Data Analytics Course in Vizag. Next, learn how SQL results can support a customer retention cohort analysis and apply the dashboard data quality checklist before sharing results.

Final takeaway

Window functions are valuable because they preserve row-level detail while adding comparisons, rankings and cumulative measures. Learn the business grain first, choose the correct partition and order, and validate edge cases. That discipline matters more than memorising syntax.