Advanced SQL Patterns for Data Analysts

Window Functions — The Most Powerful SQL Feature Analysts Underuse

Window functions compute values across a set of related rows without collapsing them. The OVER() clause defines the window — partition (which rows to group) and order (which row is "current" in a sequence).

Ranking Functions

-- Rank products by revenue within each category
SELECT
    product_name,
    category,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS row_num,
    RANK()       OVER (PARTITION BY category ORDER BY revenue DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rank
FROM products;

-- Keep only the top product per category (deduplication pattern)
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM products
) ranked
WHERE rn = 1;

LAG and LEAD — Compare Rows Across Time

-- Month-over-month revenue change
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month)               AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month)     AS mom_change,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        * 100.0 / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
        2
    )                                                    AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

-- LEAD: show next month revenue alongside current (useful for forecasting tables)
SELECT
    month,
    revenue,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

Running Totals and Moving Averages

-- Running total and 3-month moving average
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month)                            AS running_total,
    AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING
                                               AND CURRENT ROW)  AS moving_avg_3m
FROM monthly_revenue
ORDER BY month;

-- NTILE: divide rows into quartiles
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
    -- 1 = top 25%, 4 = bottom 25%
FROM customers;

CTEs — Write Readable, Reusable SQL

-- Multi-step CTE: customer lifetime value segmentation
WITH order_stats AS (
    SELECT
        customer_id,
        COUNT(*)        AS total_orders,
        SUM(amount)     AS total_spend,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order
    FROM orders
    GROUP BY customer_id
),
clv_segments AS (
    SELECT
        customer_id,
        total_orders,
        total_spend,
        DATEDIFF('day', first_order, last_order) AS customer_age_days,
        CASE
            WHEN total_spend >= 10000 THEN 'VIP'
            WHEN total_spend >= 3000  THEN 'High Value'
            WHEN total_spend >= 1000  THEN 'Mid Value'
            ELSE                           'Low Value'
        END AS clv_segment
    FROM order_stats
)
SELECT
    clv_segment,
    COUNT(*)            AS customers,
    ROUND(AVG(total_spend), 0)  AS avg_spend,
    ROUND(AVG(total_orders), 1) AS avg_orders
FROM clv_segments
GROUP BY clv_segment
ORDER BY avg_spend DESC;

Recursive CTE — Traverse Hierarchies

-- Org chart: find all reports under a manager (any depth)
WITH RECURSIVE org_tree AS (
    -- Anchor: start from the root manager
    SELECT
        employee_id,
        name,
        manager_id,
        0 AS depth
    FROM employees
    WHERE manager_id IS NULL  -- root node

    UNION ALL

    -- Recursive: add direct reports at each level
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        ot.depth + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, name, depth
FROM org_tree
ORDER BY depth, name;
-- Works for any hierarchy: category trees, bill of materials, referral chains

Conditional Aggregation — Pivot Rows into Columns

-- Revenue and order count broken out by region — all in one pass
SELECT
    DATE_TRUNC('month', order_date)                               AS month,
    SUM(CASE WHEN region = 'North'  THEN revenue ELSE 0 END)      AS north_revenue,
    SUM(CASE WHEN region = 'South'  THEN revenue ELSE 0 END)      AS south_revenue,
    SUM(CASE WHEN region = 'East'   THEN revenue ELSE 0 END)      AS east_revenue,
    SUM(CASE WHEN region = 'West'   THEN revenue ELSE 0 END)      AS west_revenue,
    COUNT(CASE WHEN region = 'North' AND revenue > 1000
               THEN order_id END)                                  AS north_high_value_orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Conditional aggregation replaces multiple self-joins or subqueries. One table scan, all segments, all in one readable query.

Query Optimisation — 7 Rules That Fix Most Slow Queries

JSON Functions — Query Semi-Structured Data

Modern databases store event data, API responses, and configuration as JSON columns. You need to extract values from them.

-- PostgreSQL JSON extraction
SELECT
    event_id,
    event_data ->> 'user_id'            AS user_id,           -- text extraction
    (event_data -> 'properties') ->> 'city' AS city,          -- nested key
    (event_data -> 'revenue')::numeric  AS revenue            -- cast to numeric
FROM events
WHERE event_data ->> 'event_type' = 'purchase';

-- MySQL JSON_EXTRACT
SELECT
    JSON_EXTRACT(event_data, '$.user_id')         AS user_id,
    JSON_EXTRACT(event_data, '$.properties.city') AS city
FROM events
WHERE JSON_EXTRACT(event_data, '$.event_type') = '"purchase"';

-- BigQuery JSON
SELECT
    JSON_VALUE(event_data, '$.user_id')              AS user_id,
    JSON_VALUE(event_data, '$.properties.city')      AS city
FROM events
WHERE JSON_VALUE(event_data, '$.event_type') = 'purchase';

Advanced SQL Interview Patterns

SELECT department, salary FROM (
    SELECT department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
    FROM employees
) r WHERE dr = 2;

WITH monthly_orders AS (
    SELECT customer_id,
           DATE_TRUNC('month', order_date) AS order_month
    FROM orders GROUP BY 1, 2
),
with_prev AS (
    SELECT customer_id, order_month,
           LAG(order_month) OVER (PARTITION BY customer_id ORDER BY order_month)
               AS prev_month
    FROM monthly_orders
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE order_month = prev_month + INTERVAL '1 month';

SELECT
    product_name,
    revenue,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total,
    ROUND(SUM(revenue) OVER (ORDER BY revenue DESC) * 100.0
          / SUM(revenue) OVER (), 2)                  AS cumulative_pct
FROM products
ORDER BY revenue DESC;

Frequently Asked Questions

Master Advanced SQL at Evika Academy — Noida Sector 51

Our SQL curriculum covers everything in this chapter — window functions, CTEs, query optimisation, and interview-level problem solving — with hands-on practice on real datasets.