Advanced SQL: Window Functions & CTEs for Complex Analysis Questions

Advanced SQL techniques using window functions (ROW_NUMBER, RANK, DENSE_RANK, etc.) and common table expressions (CTEs), including recursive queries, for complex data analysis, ranking and analytics patterns, cumulative totals, and multi-step data transformations within relational databases and data warehousing contexts.

HardTechnical

55 practiced

Design SQL to unpivot/wide-to-long transform event counters (many columns representing event types) into a tall feature table for modeling. Assume `daily_events(user_id, dt, event_type_A_count, event_type_B_count, ..., event_type_N_count)`. Provide patterns for Postgres and BigQuery and discuss strategies when event types are high-cardinality or dynamic.

Sample Answer

Approach: turn wide daily_events(user_id, dt, event_type_A_count, ..., event_type_N_count) into tall rows (user_id, dt, event_type, count) using UNPIVOT-style logic. Prefer storing the result as a long table (sparse) for modeling and only pivot when needed.

Postgres pattern (safe, explicit columns using LATERAL VALUES):

sql

WITH src AS (
  SELECT user_id, dt,
         event_type_A_count, event_type_B_count, event_type_C_count
  FROM daily_events
)
SELECT s.user_id, s.dt, v.event_type, v.count
FROM src s
CROSS JOIN LATERAL (
  VALUES
    ('A', s.event_type_A_count),
    ('B', s.event_type_B_count),
    ('C', s.event_type_C_count)
) AS v(event_type, count)
WHERE v.count IS NOT NULL AND v.count <> 0;

BigQuery pattern (UNPIVOT or UNNEST with STRUCT):Using UNPIVOT (concise if columns known):

sql

SELECT user_id, dt, event_type, count
FROM `project.dataset.daily_events`
UNPIVOT (count FOR event_type IN (event_type_A_count AS 'A', event_type_B_count AS 'B', event_type_C_count AS 'C'))
WHERE count IS NOT NULL AND count <> 0;

Or dynamic/unknown columns with ARRAY of STRUCT:

sql

SELECT user_id, dt, e.event_type, e.count
FROM `...`.daily_events,
UNNEST([STRUCT('A' AS event_type, event_type_A_count AS count),
        STRUCT('B', event_type_B_count),
        STRUCT('C', event_type_C_count)]) AS e
WHERE e.count IS NOT NULL AND e.count <> 0;

Strategies for high-cardinality or dynamic event types:- Normalize upstream: store events as event_log(user_id, dt, event_type, cnt) at ingestion so no wide schema needed.- Sparse storage: keep long table with only non-zero events — reduces storage and simplifies modeling.- Dynamic SQL / metadata-driven pipelines: query INFORMATION_SCHEMA or table schema to build UNPIVOT code or use ELT job that pivots based on column list.- If event types are extremely many: aggregate/feature-hash event_type into fixed-size buckets or compute embeddings (co-occurrence or learned) instead of one-hot. Alternatives: frequency thresholding (keep top-K), group rare events into "other", target/mean encoding, or use hashing trick.- Feature engineering: precompute aggregated features (counts, recency, normalized rates) per event or per bucket; use dimensionality reduction (SVD, PCA) or supervised selection.- Performance: run transformation in the data warehouse (BigQuery) for scale, or stream normalized events into a feature store. Partition and cluster long table by dt and user_id for fast joins.

Edge cases: zero vs NULL semantics, duplicated columns, column name collisions — handle in ETL.

HardTechnical

56 practiced

Provide a SQL derivation and a working implementation that computes an exponentially time-decayed sum per user without using a recursive CTE by transforming the recurrence into a closed form. Input: `events(user_id, date, amount)` where decay is per day. Explain numerical pitfalls and how you would implement efficiently on large datasets.

Sample Answer

Derivation (closed form)We want for each user and event at day t (integer days) the exponentially-decayed sum of all prior events:S(t) = sum_{i: date_i <= t} amount_i * alpha^{t - date_i}where alpha = exp(-decay_per_day) (0 < alpha <= 1).

Factor out alpha^{t}:S(t) = alpha^{t} * sum_{i: date_i <= t} amount_i * alpha^{-date_i}

So we can compute a running prefix sum of transformed values x_i = amount_i * alpha^{-date_i}, then multiply by alpha^{t} for the row's date t. This avoids recursion.

Working SQL (Postgres-style; replace date functions as needed)Assume events(user_id, date, amount), date is DATE, decay_per_day is a constant.

sql

WITH params AS (
  SELECT 0.1::double precision AS decay_per_day  -- example: 0.1
),
alpha AS (
  SELECT exp(-decay_per_day) AS a FROM params
),
prep AS (
  SELECT
    user_id,
    date,
    amount,
    -- convert date to integer day count since epoch to avoid fractional exponents
    extract(epoch FROM date)::bigint / 86400 AS day,
    (SELECT a FROM alpha) AS a
  FROM events
),
transformed AS (
  SELECT
    user_id,
    date,
    amount,
    day,
    a,
    -- transformed value amount * a^{-day}  (use pow)
    amount * pow(a, -day) AS x
  FROM prep
),
running AS (
  SELECT
    user_id,
    date,
    amount,
    day,
    a,
    -- cumulative sum of transformed x up to current row (ordered by day, tie-breaker by date/time if present)
    sum(x) OVER (PARTITION BY user_id ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prefix_x
  FROM transformed
)
SELECT
  user_id,
  date,
  amount,
  -- S(t) = a^{day} * prefix_x
  pow(a, day) * prefix_x AS decayed_sum_at_date
FROM running
ORDER BY user_id, date;

Key points / numerical pitfalls- Directly computing pow(alpha, large positive or negative exponents) can underflow/overflow in floating point. By factoring out alpha^{t} we compute pow(a, -day) for transformed x and later multiply by pow(a, day) — both can still be extreme, but their product cancels exponent magnitude for each row. Using integer day epoch prevents fractional drift.- Use double precision; if decay_per_day is tiny and day ranges are huge, consider using log-space: store log_x = ln(amount) - day * ln(a) and use log-sum-exp for stable accumulation, then exponentiate when needed.- For very old events alpha^{t - t_i} may underflow to 0; that is OK if contribution is negligible, but be aware when decay is very slow.

Efficiency at scale- Precompute day and transformed x (amount * pow(a, -day)) and persist as columns/partitioned table.- Use partitioning by user_id and ordering by date when writing to storage (Hive/BigQuery/Parquet bucketing) so range scans are efficient.- In analytical SQL engines that parallelize windows (BigQuery, Snowflake, Postgres), cumulative SUM OVER (PARTITION BY user ORDER BY date) is efficient if data is clustered by user. For massive scale prefer: - MapReduce / Spark: map per user, sort events by day within partition, compute streaming cumulative sum (O(n) per user), then multiply by pow(a, day). - Maintain these features incrementally: when ingesting new events, update prefix sums for that user's later rows only (append-only, incremental processing).- Use appropriate data types (double), and consider truncating history beyond N days where alpha^{N} ~ 0 to reduce data scanned.- If you need per-user value at arbitrary t (not only event times) you can store last prefix_x and last_day per user and compute S(t) = pow(a, t) * prefix_x efficiently without re-scanning history.

This transforms the recurrence into a closed-form accumulation amenable to windowed SQL or streaming computations while mitigating numeric instability and enabling scalable implementations.

MediumTechnical

56 practiced

Using recursion, write SQL to compute for each user the total purchase amount contributed by their referral tree up to depth 3. Tables: `referrals(referrer_id INT, referred_id INT)` and `purchases(user_id INT, amount NUMERIC)`. Output `root_user_id, depth, referred_user_id, referred_amount`, and then aggregate to `root_user_id, total_referred_amount_up_to_depth_3` for use as a feature.

Sample Answer

Approach: use a recursive CTE to walk the referral graph from each root user down up to depth=3, join purchases at each step to get amounts, then produce the per-edge rows (root_user_id, depth, referred_user_id, referred_amount) and finally aggregate per root to produce the feature total_referred_amount_up_to_depth_3.

sql

-- 1) Expand referral tree up to depth 3 and attach purchases
WITH RECURSIVE referral_tree AS (
  -- base: direct referrals (depth = 1)
  SELECT
    r.referrer_id AS root_user_id,
    r.referred_id AS referred_user_id,
    1 AS depth
  FROM referrals r

  UNION ALL

  -- recursive: follow referrals, increment depth
  SELECT
    rt.root_user_id,
    r.referred_id AS referred_user_id,
    rt.depth + 1 AS depth
  FROM referral_tree rt
  JOIN referrals r
    ON r.referrer_id = rt.referred_user_id
  WHERE rt.depth < 3
),
-- 2) attach purchases for each referred node (could be zero)
referred_with_purchases AS (
  SELECT
    rt.root_user_id,
    rt.depth,
    rt.referred_user_id,
    COALESCE(SUM(p.amount), 0) AS referred_amount
  FROM referral_tree rt
  LEFT JOIN purchases p
    ON p.user_id = rt.referred_user_id
  GROUP BY rt.root_user_id, rt.depth, rt.referred_user_id
)
-- 3) output per-edge rows
SELECT
  root_user_id,
  depth,
  referred_user_id,
  referred_amount
FROM referred_with_purchases
ORDER BY root_user_id, depth, referred_user_id;

-- 4) aggregate to feature: total referred amount up to depth 3
WITH total_per_root AS (
  SELECT
    root_user_id,
    SUM(referred_amount) AS total_referred_amount_up_to_depth_3
  FROM referred_with_purchases
  GROUP BY root_user_id
)
SELECT * FROM total_per_root;

Key points:- Recursive CTE prevents infinite loops by limiting depth to 3.- LEFT JOIN + SUM handles users with zero purchases.- If cycles exist, include a path-tracking column (e.g., array of ids) in CTE and filter to avoid revisiting nodes.- Performance: for large graphs, prefilter roots of interest or materialize adjacency lists; ensure indexes on referrals.referrer_id and purchases.user_id.

HardTechnical

51 practiced

Extend the referrals aggregation: compute for each user a weighted referrals revenue where each hop's contribution decays by 0.5 per depth up to depth 5. Use a recursive CTE to traverse referrals and prevent cycles. Tables: `referrals(referrer_id, referred_id)` and `purchases(user_id, amount)`. Provide the SQL and discuss how to limit memory blow-up and alternatives.

Sample Answer

Approach: use a recursive CTE that walks up to depth 5 from each referrer to referred users, track visited nodes to avoid cycles, compute weight = amount * (0.5^depth), and aggregate per original referrer.

SQL:

sql

WITH RECURSIVE walk AS (
  -- base: direct referrals (depth = 1)
  SELECT
    r.referrer_id AS origin_id,      -- original referrer to accumulate into
    r.referred_id AS node_id,        -- current node reached
    ARRAY[r.referrer_id, r.referred_id] AS path,
    1 AS depth
  FROM referrals r

  UNION ALL

  -- recurse: follow referrals outwards, stop at depth 5 and avoid cycles
  SELECT
    w.origin_id,
    r.referred_id AS node_id,
    path || r.referred_id,
    w.depth + 1
  FROM walk w
  JOIN referrals r
    ON r.referrer_id = w.node_id
  WHERE w.depth < 5
    AND NOT r.referred_id = ANY(path)
)

-- join purchases and compute weighted contribution, decay = 0.5^depth
, contributions AS (
  SELECT
    w.origin_id AS referrer_id,
    p.user_id AS purchaser_id,
    p.amount * pow(0.5, w.depth) AS weighted_amount
  FROM walk w
  JOIN purchases p
    ON p.user_id = w.node_id
)

SELECT
  referrer_id,
  SUM(weighted_amount) AS weighted_referrals_revenue
FROM contributions
GROUP BY referrer_id;

Notes / reasoning:- Depth starts at 1 for direct referrals; decay factor 0.5^depth implements halving per hop.- Path array prevents cycles by checking membership.- pow(0.5, depth) uses SQL math; some engines require POWER(0.5, depth).

Limiting memory blow-up & production concerns:- Recursive CTEs can explode when fan-out is high. Mitigations: - Strict depth cap (we use 5). - Early prune branches with no purchases: join purchases inside recursion to continue only if subtree has purchases (if purchases are sparse). - Run per-batch (shard by origin_id ranges) instead of whole table at once. - Add appropriate indexes: referrals(referrer_id), purchases(user_id). - Materialize referral graph or precompute k-hop neighborhoods offline (Spark, GraphFrames) and store results. - Use graph DB or graph processing (Neo4j, Pregel/Giraph, Spark GraphX) for larger graphs. - Approximate methods: Monte Carlo sampling, truncated personalized PageRank to estimate influence.

Trade-offs:- SQL recursive CTE is simple and transactional-safe for moderate data sizes.- For very large graphs, offline distributed graph processing with incremental updates is more scalable.

MediumTechnical

31 practiced

Finance wants a month-to-date revenue trend by product from a daily sales fact table, but some product-day combinations are missing because there were no sales. The report still needs to show zero-revenue days and reset correctly at each month boundary. How would you structure the query and what reference data, if any, would you need?

Sample Answer

**Approach**I would build a date spine, which is a table of every calendar date in the reporting range, then cross join it to the product list. That guarantees zero-sales days are present. After that, I would left join the daily sales fact and use a running sum partitioned by product and month.

sql

WITH spine AS (
  SELECT d::date AS dt
  FROM generate_series(date '2025-01-01', date '2025-01-31', interval '1 day') AS g(d)
), daily AS (
  SELECT product_id, sale_date, SUM(revenue) AS revenue
  FROM sales_fact
  GROUP BY product_id, sale_date
), base AS (
  SELECT p.product_id, s.dt
  FROM products p
  CROSS JOIN spine s
)
SELECT
  b.product_id,
  b.dt,
  COALESCE(d.revenue, 0) AS revenue,
  SUM(COALESCE(d.revenue, 0)) OVER (
    PARTITION BY b.product_id, DATE_TRUNC('month', b.dt)
    ORDER BY b.dt
  ) AS mtd_revenue
FROM base b
LEFT JOIN daily d
  ON d.product_id = b.product_id
 AND d.sale_date = b.dt;

**Reference data needed**- A product dimension.- A calendar or date spine table, or a generated date series.

**Why it works**Partitioning by month resets the running total at each boundary, and the spine ensures missing product-day combinations still show as zero.

Unlock Full Question Bank

Get access to hundreds of Advanced SQL: Window Functions & CTEs for Complex Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.