Advanced SQL — Window Functions, CTEs & Query Optimisation

-- Monthly sales leaderboard per region (India)
-- Demonstrates: RANK vs DENSE_RANK vs ROW_NUMBER with tie handling

WITH monthly_sales AS (
  SELECT
    sales_rep,
    region,                           -- Delhi, Mumbai, Bengaluru, Hyderabad, etc.
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount_inr)                  AS total_sales
  FROM orders
  WHERE YEAR(order_date) = 2026
  GROUP BY sales_rep, region, month
)
SELECT
  month,
  region,
  sales_rep,
  total_sales,

  -- ROW_NUMBER: unique number even on ties (good for "pick exactly one per group")
  ROW_NUMBER() OVER (PARTITION BY month, region ORDER BY total_sales DESC) AS row_num,

  -- RANK: tied reps get same rank; next rank skips (1, 1, 3)
  RANK()       OVER (PARTITION BY month, region ORDER BY total_sales DESC) AS rank_pos,

  -- DENSE_RANK: tied reps same rank; no skipping (1, 1, 2)
  DENSE_RANK() OVER (PARTITION BY month, region ORDER BY total_sales DESC) AS dense_rank

FROM monthly_sales
ORDER BY month, region, total_sales DESC;

-- ── TOP-N PER GROUP — the classic use case ──────────────────
-- "Get the top 3 products by revenue in each category"

WITH ranked AS (
  SELECT
    category,
    product_name,
    SUM(amount_inr)                                            AS revenue,
    DENSE_RANK() OVER (PARTITION BY category
                       ORDER BY SUM(amount_inr) DESC)         AS dr
  FROM order_items oi
  JOIN orders      o ON oi.order_id = o.order_id
  GROUP BY category, product_name
)
SELECT category, product_name, revenue, dr AS rank_in_category
FROM ranked
WHERE dr <= 3
ORDER BY category, dr;
-- Use DENSE_RANK (not ROW_NUMBER) so tied-3rd products all appear

-- LAG / LEAD: access value from a previous or next row
-- Running totals, 7-day averages, MoM growth calculations

WITH daily AS (
  SELECT
    order_date,
    SUM(amount_inr) AS daily_revenue
  FROM orders
  WHERE order_date >= '2026-01-01'
  GROUP BY order_date
)
SELECT
  order_date,
  daily_revenue,

  -- LAG: yesterday's revenue (for Day-on-Day comparison)
  LAG(daily_revenue, 1) OVER (ORDER BY order_date)          AS prev_day_revenue,

  -- Day-on-day % change
  ROUND(
    (daily_revenue - LAG(daily_revenue,1) OVER (ORDER BY order_date))
    / NULLIF(LAG(daily_revenue,1) OVER (ORDER BY order_date), 0) * 100,
  1) AS dod_pct,

  -- LEAD: tomorrow's revenue (useful for lookahead windows)
  LEAD(daily_revenue, 1) OVER (ORDER BY order_date)         AS next_day_revenue,

  -- 7-day rolling average (smooths day-of-week effects)
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 0)                                                      AS ma_7day,

  -- Running total (cumulative revenue for the year)
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                                                          AS cumulative_ytd

FROM daily
ORDER BY order_date;

-- ── MONTH-ON-MONTH GROWTH WITH LAG ───────────────────────────
WITH monthly AS (
  SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount_inr)                  AS revenue
  FROM orders
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)                        AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
  1)                                                         AS mom_growth_pct
FROM monthly
ORDER BY month;

-- Cohort analysis: retention of customers by their first order month
-- Shows what % of each month's new customers returned in subsequent months

WITH
-- Step 1: find each customer's first order month (cohort)
first_orders AS (
  SELECT
    customer_id,
    MIN(DATE_FORMAT(order_date, '%Y-%m')) AS cohort_month
  FROM orders
  GROUP BY customer_id
),

-- Step 2: tag every order with the customer's cohort
orders_with_cohort AS (
  SELECT
    o.customer_id,
    fo.cohort_month,
    DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
    -- months since first order
    PERIOD_DIFF(
      DATE_FORMAT(o.order_date, '%Y%m'),
      DATE_FORMAT(STR_TO_DATE(CONCAT(fo.cohort_month, '-01'), '%Y-%m-%d'), '%Y%m')
    ) AS months_since_first
  FROM orders o
  JOIN first_orders fo ON o.customer_id = fo.customer_id
),

-- Step 3: count unique customers per cohort + period
cohort_sizes AS (
  SELECT cohort_month, COUNT(DISTINCT customer_id) AS cohort_size
  FROM first_orders
  GROUP BY cohort_month
),

-- Step 4: count returning users per cohort per period
retention_counts AS (
  SELECT
    cohort_month,
    months_since_first,
    COUNT(DISTINCT customer_id) AS active_customers
  FROM orders_with_cohort
  GROUP BY cohort_month, months_since_first
)

-- Step 5: calculate retention rate
SELECT
  rc.cohort_month,
  rc.months_since_first,
  cs.cohort_size                           AS new_customers,
  rc.active_customers,
  ROUND(rc.active_customers / cs.cohort_size * 100, 1) AS retention_pct
FROM retention_counts rc
JOIN cohort_sizes cs ON rc.cohort_month = cs.cohort_month
WHERE rc.months_since_first <= 6         -- show 6-month retention window
ORDER BY rc.cohort_month, rc.months_since_first;

-- ── RECURSIVE CTE — Manager Hierarchy ────────────────────────
-- Used for org charts, geographic hierarchies, bill-of-materials trees

WITH RECURSIVE org_hierarchy AS (
  -- Base case: top-level managers (no manager above them)
  SELECT employee_id, name, manager_id, 1 AS level, name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: employees whose manager is already in the set
  SELECT
    e.employee_id, e.name, e.manager_id,
    oh.level + 1,
    CONCAT(oh.path, ' → ', e.name)
  FROM employees e
  JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT level, employee_id, name, path
FROM org_hierarchy
ORDER BY path;

-- ── STEP 1: Read the query plan ─────────────────────────────
EXPLAIN SELECT customer_id, SUM(amount_inr)
FROM orders
WHERE order_date >= '2026-01-01'
  AND city = 'Noida'
GROUP BY customer_id;

-- Key columns in MySQL EXPLAIN:
-- type: 'ALL' = full table scan (bad on large tables)
--       'ref'  = uses index (good)
--       'range'= range scan on index (acceptable)
-- key:  which index is being used (NULL = no index)
-- rows: estimated rows examined (lower = better)
-- Extra: 'Using filesort' / 'Using temporary' = expensive operations

-- ── STEP 2: Create missing index ─────────────────────────────
-- If type = ALL on orders(order_date, city), add a composite index:
CREATE INDEX idx_orders_date_city ON orders (order_date, city);
-- Put the equality column first (city), then range column (date) — order matters for composite indexes
CREATE INDEX idx_orders_city_date ON orders (city, order_date);  -- better for this query

-- ── COMMON ANTI-PATTERNS AND FIXES ─────────────────────────
-- ❌ BAD: Function on indexed column → index cannot be used
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- ✅ GOOD: Range on index → index CAN be used
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';

-- ❌ BAD: Correlated subquery — runs once per row (extremely slow)
SELECT o.order_id, o.amount_inr,
  (SELECT AVG(amount_inr) FROM orders WHERE city = o.city) AS city_avg
FROM orders o;

-- ✅ GOOD: Pre-aggregate with CTE/subquery, then JOIN (runs once)
WITH city_avgs AS (
  SELECT city, AVG(amount_inr) AS city_avg
  FROM orders GROUP BY city
)
SELECT o.order_id, o.amount_inr, ca.city_avg
FROM orders o
JOIN city_avgs ca ON o.city = ca.city;

-- ❌ BAD: SELECT * fetches columns you do not need
SELECT * FROM orders WHERE city = 'Delhi';

-- ✅ GOOD: Select only required columns
SELECT order_id, customer_id, amount_inr, order_date
FROM orders WHERE city = 'Delhi';

-- ❌ BAD: OR across different columns prevents index use
SELECT * FROM orders WHERE city = 'Noida' OR category = 'Electronics';

-- ✅ GOOD: UNION ALL (each branch can use its own index)
SELECT * FROM orders WHERE city = 'Noida'
UNION ALL
SELECT * FROM orders WHERE category = 'Electronics' AND city != 'Noida';

-- ── PARTITIONING (BigQuery / Snowflake / Redshift) ────────────
-- Partition by date so queries only scan relevant partitions
-- BigQuery:
CREATE TABLE orders_bq
PARTITION BY DATE(order_date)   -- query scans only relevant day partitions
CLUSTER BY city, category       -- further prunes data within partitions
AS SELECT * FROM raw_orders;

-- A query with WHERE order_date >= '2026-01-01' AND city = 'Noida'
-- now scans only Jan–Sep 2026 partitions for Noida — dramatic cost reduction

What is the difference between RANK, DENSE_RANK, and ROW_NUMBER in SQL?

All three are window functions that assign a sequential number to rows based on an ORDER BY clause, but they handle ties differently. ROW_NUMBER assigns a unique number to every row — no two rows get the same number, even if they have equal values. Ties are broken arbitrarily (by internal row order). RANK assigns the same rank to tied rows, then skips numbers — if two rows tie for rank 1, the next row is rank 3 (not 2). DENSE_RANK also assigns the same rank to ties, but does NOT skip — if two rows tie for rank 1, the next rank is 2. Example: Sales [500, 500, 300]. ROW_NUMBER gives [1, 2, 3]. RANK gives [1, 1, 3]. DENSE_RANK gives [1, 1, 2]. Use ROW_NUMBER when you need exactly one row per group (top-1 per category). Use RANK when you want to identify all entries that tied for a position (top-3 sellers might have 4 entries if two tied for 3rd). Use DENSE_RANK when you want contiguous rank numbers without gaps.

When should you use a CTE instead of a subquery?

Use a CTE (WITH clause) when: the logic is complex enough that it benefits from naming and explaining; the same intermediate result is used more than once in the query; you want to structure multi-step analysis as readable named steps; or you are building a recursive query (only possible with CTEs). Use a subquery when: the logic is short and self-explanatory; it is used in only one place; you need it inline in a WHERE or FROM clause for a quick filter. The practical rule: if a reader would have to re-read the subquery multiple times to understand what it produces, wrap it in a CTE and give it a clear name. Both produce identical query plans in most databases (PostgreSQL, BigQuery, Snowflake, Redshift) — performance difference is negligible unless the CTE is materialised. CTEs substantially improve readability and are preferred in code reviews at data-driven Indian companies.

How do you optimise a slow SQL query in MySQL or PostgreSQL?

A systematic optimisation process: (1) Run EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) to see the query plan — look for full table scans (type: ALL in MySQL; Seq Scan in PostgreSQL) on large tables. (2) Check if columns in WHERE, JOIN ON, and ORDER BY have indexes. Create missing indexes with CREATE INDEX — especially on foreign keys, date columns, and high-cardinality filter columns. (3) Avoid functions on indexed columns in WHERE clauses — WHERE YEAR(order_date) = 2026 cannot use a date index; WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31' can. (4) Avoid SELECT * — retrieve only the columns you need. (5) Filter early — apply WHERE conditions before JOINs when possible, using CTEs or subqueries to reduce row counts. (6) Avoid correlated subqueries that run once per row — replace with a JOIN or window function. (7) On analytical databases (BigQuery, Redshift), partition tables by date and cluster/sort by frequently-filtered columns.

What advanced SQL questions come up in Indian data analyst interviews?

The most common advanced SQL interview questions in India (2026): (1) Write a query to find the second-highest salary — tests knowledge of DENSE_RANK or OFFSET-FETCH. (2) Find the Nth order per customer — tests ROW_NUMBER with PARTITION BY. (3) Calculate a 7-day rolling average of daily revenue — tests AVG() OVER with ROWS BETWEEN. (4) Find customers whose orders increased month-over-month — tests LAG() or self-join approach. (5) Identify products that appear in every category — tests GROUP BY with HAVING COUNT(DISTINCT category) = total. (6) Write a query to de-duplicate keeping the latest record — tests ROW_NUMBER with PARTITION BY id ORDER BY updated_at DESC and an outer WHERE rn = 1. (7) Explain the difference between INNER JOIN and LEFT JOIN with a scenario — tests conceptual clarity. (8) What does EXPLAIN tell you and how do you use it? — tests performance thinking. Practise these on real tables (orders, customers, products) with Indian-context data.