SQL Analytics — Cheat Sheet
Tags: sql, analytics, data-analysis, window-functions, aggregations, ctes
Level: intermediate
TL;DR: Essential SQL concepts, window functions, CTEs, and query patterns used to extract, transform, and analyze large datasets effectively.
Key concepts
-
Window functions perform calculations across a set of related rows without collapsing them into a single output row.
-
Common Table Expressions (CTEs) improve query readability by creating temporary result sets that can be referenced sequentially.
-
Aggregate functions (SUM, AVG, COUNT, MAX, MIN) combined with GROUP BY form the foundation of data summarization.
-
The WHERE clause filters rows before aggregation, while the HAVING clause filters result sets after aggregation.
-
Pivoting transforms row-level data into column-level data, typically achieved using CASE WHEN statements wrapped in aggregate functions.
-
ROLLUP and CUBE extensions in GROUP BY clauses generate automatic subtotals and grand totals in a single pass.
-
Time-series analysis relies heavily on date functions like DATE_TRUNC, EXTRACT, and DATE_DIFF to group data by granular time periods.
Typical questions and ready answers
-
Q: What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?R: ROW_NUMBER() assigns a strictly unique sequential integer. RANK() assigns the same rank to ties but skips subsequent numbers. DENSE_RANK() gives the same rank to ties without skipping any numbers.
-
Q: How do you evaluate and optimize a slow-performing analytical query?R: I start by analyzing the execution plan using EXPLAIN. I look for full table scans, ensure filtering happens as early as possible in the WHERE clause, replace correlated subqueries with joins or CTEs, and verify index usage.
-
Q: Why use a CTE over a subquery?R: I prefer CTEs because they make complex logic linear and readable. They can also be referenced multiple times within the same execution context and support recursive structures.
Pitfalls and common mistakes
-
Using COUNT(column) instead of COUNT(*) when attempting to count total rows, inadvertently excluding nulls.
-
Filtering on a window function result directly in a WHERE clause instead of wrapping it in a CTE or subquery first.
-
Forgetting to specify the correct PARTITION BY in window functions, resulting in global aggregates instead of group-level metrics.
-
Missing join conditions leading to accidental Cartesian products (cross joins), which causes massive memory spikes and bad data.
-
Dividing integers without casting them to decimals first, resulting in truncated integer math (e.g., 1/2 returning 0 instead of 0.5).
Snippets and commandsSQL
```-- Calculate a running total and a 7-day moving average
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM sales_daily;
-- Pivot rows into columns (e.g., revenue by year per department)
SELECT
department,
SUM(CASE WHEN sales_year = 2022 THEN revenue ELSE 0 END) AS rev_2022,
SUM(CASE WHEN sales_year = 2023 THEN revenue ELSE 0 END) AS rev_2023
FROM department_sales
GROUP BY department;
-- Find the top 3 highest paid employees per department
WITH RankedSalaries AS (
SELECT
department_id,
employee_name,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees
)
SELECT *
FROM RankedSalaries
WHERE rank <= 3;
```
To review
-
[ ] Recursive CTE syntax for hierarchical data (e.g., organizational charts, category trees)
-
[ ] Database-specific syntax for parsing and extracting data from JSON columns
