SQL Analytical Functions for Business Metrics

Moving from raw data to actionable business intelligence requires translating key performance indicators (KPIs) into precise, efficient SQL. While basic aggregations tell you what happened, analytical functions—particularly window functions—allow you to understand how and why by performing calculations across related rows without collapsing your dataset. This skill is foundational for building the robust, automated backends that power modern dashboards and drive strategic decisions.

Foundational Window Functions: LAG, LEAD, and Row Comparison

At the core of trend analysis is the ability to compare a value to its prior or subsequent value in a logical sequence. This is where LAG() and LEAD() become indispensable. These functions access data from a different row within the same result set, relative to the current row, based on a specified ORDER BY clause within a PARTITION.

Consider calculating month-over-month (MoM) revenue growth. A simple GROUP BY month gives you totals, but not the change. A window function provides the context.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS revenue_month,
        SUM(amount) AS total_revenue
    FROM orders
    GROUP BY 1
)
SELECT
    revenue_month,
    total_revenue,
    LAG(total_revenue, 1) OVER (ORDER BY revenue_month) AS prev_month_revenue,
    ROUND(
        (total_revenue - LAG(total_revenue, 1) OVER (ORDER BY revenue_month)) 
        / LAG(total_revenue, 1) OVER (ORDER BY revenue_month) * 100, 
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY revenue_month;

In this query, LAG(total_revenue, 1) looks "back" one row in the sequence ordered by revenue_month to fetch the previous month's total. The OVER() clause defines the window—here, the entire dataset ordered by time. This pattern is essential for analyzing growth rates, spotting anomalies, and forecasting.

Cohort Analysis for Measuring Retention

Cohort analysis groups users based on a shared initial event (like a first purchase or sign-up date) and tracks their behavior over time. Translating this business concept into SQL involves a two-step process: cohort assignment, followed by conditional aggregation across time periods.

First, define the cohort. For a monthly sign-up cohort analyzing 3-month retention:

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(signup_date)) AS cohort_month
    FROM users
    GROUP BY user_id
),
user_activity AS (
    SELECT
        u.user_id,
        c.cohort_month,
        DATE_TRUNC('month', u.activity_date) AS activity_month,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', u.activity_date), c.cohort_month)) AS months_since_cohort
    FROM user_activity_log u
    JOIN user_cohorts c ON u.user_id = c.user_id
)
SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size,
    COUNT(DISTINCT CASE WHEN months_since_cohort = 0 THEN user_id END) AS m0_active,
    COUNT(DISTINCT CASE WHEN months_since_cohort = 1 THEN user_id END) AS m1_active,
    COUNT(DISTINCT CASE WHEN months_since_cohort = 2 THEN user_id END) AS m2_active,
    ROUND(COUNT(DISTINCT CASE WHEN months_since_cohort = 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 1) AS m2_retention_pct
FROM user_activity
GROUP BY cohort_month
ORDER BY cohort_month;

This query creates a classic cohort retention table. The CASE statements inside the COUNT(DISTINCT ...) aggregates act as filters, counting users only if they were active in the specific month relative to their cohort start. This conditional aggregation is the workhorse for building pivot-style summaries directly in SQL.

Funnel Conversion and Revenue Attribution

Understanding user progression through a series of steps—a funnel—and attributing value to touchpoints are critical for marketing and product analytics. SQL solves this by joining event data and using window functions to establish sequence and assign credit.

For a simple purchase funnel (Page View -> Add to Cart -> Purchase), you need to identify the first occurrence of each step per user and then aggregate.

WITH funnel_events AS (
    SELECT
        user_id,
        event_type,
        event_time,
        FIRST_VALUE(event_time) OVER (PARTITION BY user_id, event_type ORDER BY event_time) AS first_event_time
    FROM events
    WHERE event_type IN ('page_view', 'add_to_cart', 'purchase')
),
user_funnel_status AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS viewed_page,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS added_cart,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS made_purchase
    FROM funnel_events
    GROUP BY user_id
)
SELECT
    SUM(viewed_page) AS total_viewers,
    SUM(added_cart) AS total_cart_adders,
    SUM(made_purchase) AS total_purchasers,
    ROUND(SUM(added_cart) * 100.0 / NULLIF(SUM(viewed_page), 0), 2) AS view_to_cart_rate,
    ROUND(SUM(made_purchase) * 100.0 / NULLIF(SUM(added_cart), 0), 2) AS cart_to_purchase_rate
FROM user_funnel_status;

For revenue attribution (e.g., first-touch), you might use FIRST_VALUE() to find the initial marketing channel for a user and then attribute all their subsequent lifetime revenue to it. A PARTITION BY user_id ensures the calculation is per user.

SELECT
    first_touch_channel,
    SUM(order_revenue) AS attributed_revenue,
    COUNT(DISTINCT user_id) AS converting_users
FROM (
    SELECT
        user_id,
        order_revenue,
        FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY touch_time) AS first_touch_channel
    FROM marketing_touches mt
    JOIN orders o ON mt.user_id = o.user_id
) subq
GROUP BY first_touch_channel;

Calculating Customer Lifetime Value (CLV)

Customer Lifetime Value (CLV) is the total profit expected from a customer over their entire relationship. A practical, historical CLV calculation in SQL aggregates a customer's gross revenue to date. A more predictive approach uses averages and window functions.

A foundational query calculates average revenue per user (ARPU) per cohort, which is a core CLV component:

WITH cohort_revenue AS (
    SELECT
        DATE_TRUNC('month', MIN(o.order_date)) AS cohort_month,
        o.user_id,
        SUM(o.amount) AS lifetime_revenue
    FROM orders o
    GROUP BY o.user_id
)
SELECT
    cohort_month,
    COUNT(user_id) AS cohort_size,
    AVG(lifetime_revenue) AS avg_clv,
    SUM(lifetime_revenue) AS total_clv
FROM cohort_revenue
GROUP BY cohort_month
ORDER BY cohort_month;

To build a revenue waterfall analysis showing monthly recurring revenue (MRR) changes from new customers, upgrades, downgrades, and churn, you would combine LAG() with complex conditional logic to categorize each customer's month-to-month subscription value change. This creates a powerful narrative of revenue drivers.

Common Pitfalls

1. Ignoring Window Frame Defaults: The default frame for window functions with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This can lead to incorrect running totals if not understood. For calculations like a 3-month rolling average, you must explicitly define the frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
2. Misplacing Filters: Attempting to filter the result of a window function (e.g., WHERE row_number = 1) in the same WHERE clause will fail because window functions are calculated after WHERE. You must use a subquery or Common Table Expression (CTE) to filter on the calculated column.
3. Creating Cartesian Products in Cohort Analysis: When joining events to cohort assignments, ensure your join is on user_id and that the event date is on or after the cohort start date. A sloppy join can incorrectly assign pre-cohort events or inflate counts.
4. Overlooking Performance: Window functions that partition by a non-indexed column or that require a full table scan (like PARTITION BY large_text_field) can be slow. Always analyze query execution plans and consider indexing columns used in PARTITION BY and ORDER BY clauses.

Summary

• Window functions like LAG() and LEAD() are essential for calculating trends such as month-over-month growth by comparing rows within an ordered set.
• Cohort analysis is built by assigning users to a start group and using conditional aggregation (COUNT(CASE WHEN...)) to track their activity over subsequent periods.
• Funnel conversion rates require identifying the sequence of user events, often using FIRST_VALUE() or conditional flags, before aggregating to calculate step-by-step drop-off.
• Revenue attribution and CLV translate business logic into SQL by using FIRST_VALUE() for attribution models and strategic aggregations over customer lifespans for value calculation.
• Always be mindful of window frame specifications and query performance; these powerful functions can be computationally expensive if not designed carefully on indexed columns.