When should you use window functions vs CTEs? Explain the fundamental difference.

Fundamental difference: Window functions are about WHAT you calculate (row-level analytics), CTEs are about HOW you structure your query (organization). Use Window Functions when: 1. You need row-level calculations while keeping all rows (rankings, running totals) 2. You want to compare current row with other rows (LAG, LEAD) 3. You need moving averages or cumulative calculations 4. You want percentiles or statistical functions 5. Performance is critical for analytics (100x faster than correlated subqueries) Use CTEs when: 1. You need to break complex query into readable steps 2. You want to reuse a subquery multiple times 3. You need recursion (hierarchical data like org charts) 4. You want to improve query maintainability and debugging 5. You're doing multi-step data transformations Key insight: They're complementary, not alternatives. Best practice is combining both - CTE for data preparation, window functions for analytics. Example: WITH monthly_sales AS (SELECT ... GROUP BY month) -- CTE: Prep SELECT *, RANK() OVER (ORDER BY total DESC) FROM monthly_sales; -- Window: Analytics

Why are window functions 100x faster than CTEs with correlated subqueries for running totals?

Complexity difference: Window Function: O(N log N) - Sort data once: O(N log N) - Accumulate in single pass: O(N) - Total: O(N log N) - For 1M rows: ~20 million operations - Time: 0.4 seconds CTE with Correlated Subquery: O(N²) - For each row (N rows) - Scan all previous rows (average N/2 rows) - Total: N × N/2 = O(N²) - For 1M rows: ~500 billion operations - Time: 45 seconds Why window function is optimized: 1. Database knows you're calculating running total 2. Uses specialized algorithm (sort + accumulate) 3. Single pass through sorted data 4. No repeated scans Why correlated subquery is slow: 1. Database doesn't recognize pattern 2. Executes subquery for every row 3. Each subquery scans all previous rows 4. Massive duplication of work Real-world impact: 1M rows = 100x faster. 10M rows = window function still works, correlated subquery times out.

Can you combine window functions and CTEs? Give a practical example.

Yes! Combining them is best practice. Pattern: CTE for data prep, window functions for analytics. Example: Sales performance dashboard WITH monthly_sales AS ( -- CTE: Aggregate daily sales to monthly SELECT salesperson_id, DATE_TRUNC('month', sale_date) as month, SUM(amount) as monthly_total FROM sales WHERE sale_date >= '2024-01-01' GROUP BY 1, 2 ) SELECT salesperson_id, month, monthly_total, -- Window functions: Multiple analytics on prepared data AVG(monthly_total) OVER ( PARTITION BY salesperson_id ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg_3mo, RANK() OVER ( PARTITION BY month ORDER BY monthly_total DESC ) as monthly_rank, SUM(monthly_total) OVER ( PARTITION BY salesperson_id ORDER BY month ) as ytd_total FROM monthly_sales; Why this works well: 1. CTE handles data prep (filtering, aggregating) 2. Window functions perform multiple analytics 3. Clean separation of concerns 4. Easy to debug (test CTE independently) 5. Performant (aggregation once, analytics on smaller dataset) This pattern is standard in production analytics.

Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER(). When would you use each?

All three assign numbers to rows, but handle ties differently: RANK(): - Assigns same rank to ties - Skips ranks after ties - Example: 1, 1, 3, 4 (skips rank 2) - Use when: You want to show ties but skip ranks (Olympic medals) DENSE_RANK(): - Assigns same rank to ties - Doesn't skip ranks - Example: 1, 1, 2, 3 (no gaps) - Use when: You want consecutive ranks with ties (leaderboards) ROW_NUMBER(): - Assigns unique number to every row - No ties (uses tiebreaker) - Example: 1, 2, 3, 4 (always unique) - Use when: You need unique row numbers (pagination, deduplication) Practical examples: -- Olympic medals (use RANK - skip ranks after ties) RANK() OVER (ORDER BY score DESC) -- Gold: 9.8, 9.8, Silver: 9.7 -- Ranks: 1, 1, 3 (no rank 2) -- Sales leaderboard (use DENSE_RANK - no gaps) DENSE_RANK() OVER (ORDER BY sales DESC) -- Top sellers: $100K, $100K, $95K -- Ranks: 1, 1, 2 (consecutive) -- Pagination (use ROW_NUMBER - unique) ROW_NUMBER() OVER (ORDER BY created_at DESC, id) -- Every row gets unique number for OFFSET/LIMIT Tiebreaker for ROW_NUMBER: ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) -- If salaries tie, use employee_id to break tie

What is the difference between ROWS and RANGE in window frame clauses? When should you use each?

ROWS and RANGE define the window frame differently: ROWS: Counts physical rows - ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = exactly 3 rows (2 before + current) - Ignores values, just counts rows - Faster (no value comparison) - Simpler syntax RANGE: Includes rows with values in range - RANGE BETWEEN 2 PRECEDING AND CURRENT ROW = all rows with value >= (current - 2) - Compares values, includes all rows with values in range - Slower (requires value comparison) - Can include multiple rows with same value Example with data: date | amount 2024-01-01 | 100 2024-01-02 | 100 -- Same value as previous 2024-01-03 | 200 2024-01-04 | 300 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW: - Row 1: [100] (only current) - Row 2: [100, 100] (previous + current) - Row 3: [100, 200] (previous + current) - Row 4: [200, 300] (previous + current) RANGE BETWEEN 1 PRECEDING AND CURRENT ROW: - Row 1: [100] (values 99-100) - Row 2: [100, 100] (values 99-100, includes both 100s) - Row 3: [100, 100, 200] (values 199-200, includes both 100s) - Row 4: [200, 300] (values 299-300) When to use ROWS: - Moving averages (last N rows) - Most common use case - Simpler and faster - Example: 7-day moving average When to use RANGE: - Time-based windows with gaps - Include all rows with same value - Example: All orders within $100 of current order Default: Use ROWS unless you specifically need RANGE behavior.

How do you optimize queries that use both CTEs and window functions?

Optimization strategies: 1. Index PARTITION BY and ORDER BY columns: CREATE INDEX idx_sales_person_date ON sales(salesperson_id, sale_date); -- Speeds up: PARTITION BY salesperson_id ORDER BY sale_date 2. Materialize CTEs when reused: WITH expensive_calc AS MATERIALIZED ( SELECT ... -- Expensive aggregation ) SELECT ... FROM expensive_calc -- Used multiple times 3. Filter early in CTE: WITH filtered_data AS ( SELECT * FROM large_table WHERE date >= '2024-01-01' -- Filter first! ) SELECT ... window functions ... FROM filtered_data; -- Don't apply window functions to full table, filter first 4. Combine window functions (share sort): -- Good: Multiple window functions with same PARTITION BY/ORDER BY SELECT RANK() OVER (PARTITION BY dept ORDER BY salary DESC), DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC), ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) -- Database sorts once, applies all three functions -- Bad: Different PARTITION BY/ORDER BY SELECT RANK() OVER (PARTITION BY dept ORDER BY salary DESC), RANK() OVER (PARTITION BY region ORDER BY sales DESC) -- Database must sort twice 5. Limit window frame size: -- Good: Limited frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- Only 7 rows -- Bad: Unbounded frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- All previous rows 6. Use ROWS instead of RANGE: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- Faster RANGE BETWEEN 2 PRECEDING AND CURRENT ROW -- Slower 7. Monitor work_mem: SET work_mem = '256MB'; -- Increase if window operations spill to disk 8. Use EXPLAIN ANALYZE: EXPLAIN ANALYZE WITH ... SELECT ... window functions ...; -- Check for: Sort operations, WindowAgg nodes, memory usage

SQLPractice Online

Loading...