Advanced SQL — Window Functions, CTEs, Query Optimisation & Real-World Patterns

-- Rank customers by revenue within each region (DENSE_RANK for top-N)
SELECT
    customer_id,
    region,
    total_revenue,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS revenue_rank
FROM customer_summary;

-- Filter top 3 per region — must wrap in a subquery or CTE
WITH ranked AS (
    SELECT
        customer_id, region, total_revenue,
        DENSE_RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS rnk
    FROM customer_summary
)
SELECT * FROM ranked WHERE rnk <= 3;

-- Deduplication: keep the most recent row per order_id (ROW_NUMBER)
WITH deduped AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM raw_orders
)
SELECT * FROM deduped WHERE rn = 1;

-- Month-over-Month revenue growth
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)                           AS prev_month_rev,
    revenue - LAG(revenue) OVER (ORDER BY month)                 AS mom_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    )                                                            AS mom_pct_change
FROM monthly_revenue;

-- LAG with PARTITION BY: MoM within each product category
SELECT
    category,
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (PARTITION BY category ORDER BY month) AS prev_month,
    -- third argument = default when no prior row exists (avoids NULL)
    revenue - LAG(revenue, 1, 0) OVER (PARTITION BY category ORDER BY month) AS change
FROM category_monthly_revenue;

-- LEAD: days until the next order per customer
SELECT
    customer_id,
    order_date,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
        - order_date                                              AS days_between_orders
FROM orders;

-- Running total (cumulative sum)
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales;

-- 7-day moving average
SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)   AS moving_avg_7d,
    -- also: 30-day
    AVG(daily_revenue) OVER (ORDER BY order_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)  AS moving_avg_30d
FROM daily_sales;

-- Running total partitioned by region
SELECT
    region, order_date, daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS region_running_total
FROM regional_daily_sales;

-- Percentage of total so far (running %)
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        / SUM(daily_revenue) OVER () * 100           AS running_pct
FROM daily_sales;

-- Multi-step CTE: customer lifetime value analysis
WITH
-- Step 1: aggregate orders per customer
customer_orders AS (
    SELECT
        customer_id,
        COUNT(*)                        AS total_orders,
        SUM(revenue)                    AS total_revenue,
        MIN(order_date)                 AS first_order,
        MAX(order_date)                 AS last_order
    FROM orders
    GROUP BY customer_id
),
-- Step 2: calculate derived metrics
customer_metrics AS (
    SELECT
        customer_id,
        total_orders,
        total_revenue,
        first_order,
        last_order,
        total_revenue / total_orders                    AS avg_order_value,
        DATEDIFF(last_order, first_order)               AS days_as_customer,
        DATEDIFF(CURRENT_DATE, last_order)              AS days_since_last_order
    FROM customer_orders
),
-- Step 3: segment customers
customer_segments AS (
    SELECT *,
        CASE
            WHEN total_revenue >= 100000 AND days_since_last_order <= 90 THEN 'Champion'
            WHEN total_revenue >= 50000  AND days_since_last_order <= 180 THEN 'Loyal'
            WHEN days_since_last_order > 365                              THEN 'At Risk'
            ELSE 'Regular'
        END AS segment
    FROM customer_metrics
)
-- Final output: summary by segment
SELECT
    segment,
    COUNT(*)            AS customers,
    ROUND(AVG(total_revenue), 0) AS avg_ltv,
    ROUND(AVG(avg_order_value), 0) AS avg_order_value,
    ROUND(AVG(days_since_last_order), 0) AS avg_days_inactive
FROM customer_segments
GROUP BY segment
ORDER BY avg_ltv DESC;

-- Generate a continuous date series (no gaps, even on days with no sales)
-- Useful for charts that must show every day even if revenue = 0
WITH RECURSIVE date_series AS (
    SELECT DATE('2026-01-01') AS dt          -- anchor (starting row)
    UNION ALL
    SELECT dt + INTERVAL 1 DAY               -- recursive step
    FROM date_series
    WHERE dt < DATE('2026-12-31')            -- termination condition
)
SELECT
    ds.dt                                    AS order_date,
    COALESCE(s.revenue, 0)                   AS revenue   -- 0 for missing days
FROM date_series ds
LEFT JOIN daily_sales s ON s.order_date = ds.dt
ORDER BY ds.dt;

-- Traverse an org hierarchy: find all reports under a manager
WITH RECURSIVE org_tree AS (
    -- Anchor: start with the target manager
    SELECT employee_id, name, manager_id, 1 AS depth
    FROM employees
    WHERE employee_id = 101              -- root manager

    UNION ALL

    -- Recursive step: join employees to their manager in the tree
    SELECT e.employee_id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, name, depth
FROM org_tree
ORDER BY depth, name;

-- PostgreSQL: EXPLAIN ANALYZE shows actual run time + row estimates
EXPLAIN ANALYZE
SELECT customer_id, SUM(revenue)
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

-- Key terms to look for in the output:
-- Seq Scan         → reading every row in the table (bad on large tables)
-- Index Scan       → using an index (good — targeted lookup)
-- Index Only Scan  → even better — never touches the table itself
-- Hash Join        → joining using a hash table (usually efficient for large sets)
-- Nested Loop      → joining row-by-row (fast for small sets, slow for large ones)
-- Sort             → explicit sort step (check if an index could remove it)
-- cost=X..Y        → estimated cost; actual time=X..Y shows real execution time
-- rows=N           → estimated row count; big difference from actual = stale statistics

-- Fix 1: create an index on frequently filtered columns
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Fix 2: composite index for queries that filter + group
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);

-- Fix 3: avoid functions on indexed columns in WHERE — they bypass the index
-- Bad (function prevents index use):
WHERE YEAR(order_date) = 2026
-- Good (range scan uses the index):
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'

Group users by their first-order month and track what % return in each subsequent month.

WITH cohorts AS (
    SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders GROUP BY customer_id
),
activity AS (
    SELECT o.customer_id, c.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', o.order_date)) AS period
    FROM orders o JOIN cohorts c USING (customer_id)
)
SELECT cohort_month, period, COUNT(DISTINCT customer_id) AS active_customers
FROM activity GROUP BY 1, 2 ORDER BY 1, 2;

Turn rows into columns — e.g. one column per month — without a PIVOT keyword.

SELECT
    product_id,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN revenue ELSE 0 END) AS jan,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN revenue ELSE 0 END) AS feb,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN revenue ELSE 0 END) AS mar
FROM orders
WHERE YEAR(order_date) = 2026
GROUP BY product_id;

Get the first and last event per user — e.g. first purchase category, most recent product.

WITH ranked AS (
    SELECT customer_id, product_category, order_date,
        FIRST_VALUE(product_category) OVER
            (PARTITION BY customer_id ORDER BY order_date ASC
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_category,
        LAST_VALUE(product_category) OVER
            (PARTITION BY customer_id ORDER BY order_date ASC
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_category
    FROM orders
)
SELECT DISTINCT customer_id, first_category, last_category FROM ranked;

Find consecutive sequences (e.g. consecutive active days) or gaps in a series.

-- Assign each row to a "group" based on consecutive date gaps
SELECT
    customer_id, activity_date,
    activity_date - ROW_NUMBER() OVER
        (PARTITION BY customer_id ORDER BY activity_date)::int AS grp
FROM user_activity;
-- Rows with the same grp value are consecutive. GROUP BY grp to find streaks.