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.

MediumTechnical

60 practiced

You receive raw user records with duplicates and an `updated_at` timestamp and `source_priority`. Show SQL that uses ROW_NUMBER and CTEs to select the single canonical record per user using ordering: newest updated_at then lowest source_priority. Then show how to perform an atomic upsert into `users` table in Postgres using that CTE.

MediumTechnical

53 practiced

Implement an exponentially decayed sum per user in SQL: decayed_sum_t = amount_t + decay^gap_days * decayed_sum_{t-1}, where decay is a constant per day. Input table `purchases(user_id, amt, dt DATE)`. Provide two SQL approaches: 1) recursive CTE per user, 2) closed-form using precomputed decay powers and windowed SUM. Discuss trade-offs.

MediumTechnical

56 practiced

Design an incremental SQL pipeline using CTEs to compute features only for users with new events since the last feature run. Given tables `events(user_id, event_ts)` and `features_store(user_id, last_computed_at, ...features)`, write a query that identifies changed users and computes fresh features for them, minimizing recomputation.

MediumTechnical

67 practiced

Write SQL to sessionize event logs into sessions per user where a new session starts if the gap between consecutive events is greater than 30 minutes. Table: `events(user_id INT, event_ts TIMESTAMP, event_id UUID)`. Output should include `user_id`, `event_id`, `event_ts`, `session_id` (deterministic), `session_start`, `session_end` per session. Use window functions and CTEs.

EasyTechnical

63 practiced

You have `purchases(user_id INT, product_id INT, amount DECIMAL, purchased_at TIMESTAMP)`. Return the top 3 products per user by total spend using SQL (tie-handling should preserve deterministic results). Provide product_id, total_spend, and rank per user. Use window functions and CTEs.

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.