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
59 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
47 practiced
Design SQL to compute a 7-day rolling mean of daily event counts per user. Table: `events(user_id INT, event_ts TIMESTAMP)`. Take into account missing dates (treat missing days as zeros) by generating a calendar per user. Provide a query suitable for Postgres or BigQuery.
MediumTechnical
58 practiced
Given a SQL query that uses multiple window functions, what parts of an EXPLAIN ANALYZE output would indicate expensive window processing (e.g., sorts, temporary files)? Provide three concrete optimizations you would attempt and explain why each helps.
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
51 practiced
Given `measurements(sensor_id INT, reading_value DOUBLE PRECISION, ts TIMESTAMP)`, write SQL to flag anomalies where the reading deviates from the past 7-day mean by more than 3 standard deviations. Use window functions for mean and stddev and add a safeguard for small sample sizes (e.g., require at least 10 points). Discuss numeric stability considerations.
Unlock Full Question Bank
Get access to hundreds of Advanced SQL: Window Functions & CTEs for Complex Analysis interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.