Window functions, CTEs, query tuning, analytics patterns
Practise these as flashcards. One or two lines each; the ones you miss come back first.
Start freeA window function performs calculations across a set of rows related to the current row, but unlike aggregate functions, it doesn't collapse rows. Instead, it adds a computed column while retaining all original rows, making it ideal for running totals, rankings, and comparisons.
I would use the LAG function with an ORDER BY clause on the date column to access the previous row's sales value. For example, LAG(sales, 1) OVER (ORDER BY date) gives me the previous day's sales for each row.
A Common Table Expression (CTE) is a temporary result set defined within a query using the WITH clause. It's useful for breaking complex queries into readable parts, such as calculating intermediate aggregates before a final SELECT.
PARTITION BY divides the result set into groups, and the window function is applied independently within each group. For example, calculating a running total per user would use PARTITION BY user_id.
I'd use ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) to rank employees within each department, then filter for rows where the rank is less than or equal to 2.
I can use conditional aggregation with CASE statements inside aggregate functions, or use the PIVOT operator if supported. For example, summing sales by region with SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) for each region column.
A recursive CTE is a CTE that references itself, allowing you to perform recursive operations like traversing hierarchies or graphs. I'd use it for problems like finding all subordinates in an org chart or computing paths in a tree structure.
I use ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY timestamp DESC) to assign a rank, then select rows where the rank is 1. This keeps only the most recent record for each key.
A hash join builds a hash table on one input and probes it with the other, efficient for large, unsorted tables. Merge join requires both inputs to be sorted and is very fast for large, sorted data. Nested loop join compares each row from one table to all rows in the other, best for small tables or when indexes are available.
Partition pruning is when the query engine scans only relevant partitions based on query filters, skipping others. It's crucial for performance in large datasets because it reduces the amount of data read and speeds up queries.
Clustering organizes data within partitions based on specified columns, so related rows are stored together. This allows the engine to skip irrelevant data blocks during scans, improving filter and join performance.
Recursive CTEs are set-based and run entirely in the database, making them efficient for moderate recursion depths. However, they can be less performant or hit limits with very deep hierarchies, where procedural loops outside SQL might be more flexible but less efficient.