SQL for Data Science Cheat Sheet
Reference for SQL aggregations, window functions, joins, and CTEs commonly used to analyze and reshape data during exploratory data science work.
2 PagesBeginnerMar 20, 2026
Aggregations & GROUP BY
Summarize rows into group-level statistics.
sql
SELECT region, COUNT(*) AS num_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order_value, MIN(order_date) AS first_order, MAX(order_date) AS last_orderFROM ordersWHERE order_date >= '2024-01-01'GROUP BY regionHAVING SUM(amount) > 10000ORDER BY total_revenue DESC;
Window Functions
Compute per-row values across a related set of rows.
sql
SELECT customer_id, order_date, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq, RANK() OVER (ORDER BY amount DESC) AS amount_rank, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total, LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amountFROM orders;
Joins & CTEs
Combine tables and structure multi-step queries.
sql
-- INNER JOIN: only matching rowsSELECT o.order_id, c.customer_nameFROM orders oINNER JOIN customers c ON o.customer_id = c.customer_id;-- LEFT JOIN: all rows from orders, matched customers or NULLSELECT o.order_id, c.customer_nameFROM orders oLEFT JOIN customers c ON o.customer_id = c.customer_id;-- Common table expression (CTE)WITH monthly_sales AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY 1)SELECT month, total FROM monthly_sales WHERE total > 50000;
Key Concepts
Terminology every data scientist writing SQL should know.
- GROUP BY- Aggregates rows sharing the same value(s) into summary rows
- HAVING vs WHERE- WHERE filters rows before aggregation, HAVING filters groups after aggregation
- Window functions- Compute values across a set of related rows without collapsing them, via the OVER clause
- CTE (WITH clause)- Named temporary result set that improves readability of multi-step queries
- NULL handling- Use COALESCE(col, default) to substitute NULLs; NULL = NULL is never TRUE, use IS NULL instead
- Subquery vs JOIN- Correlated subqueries can often be rewritten as JOINs or CTEs for better performance
- Index- Speeds up WHERE/JOIN/ORDER BY lookups on the indexed column(s) at the cost of write speed
Recursive CTEs
Walk hierarchical or graph-shaped data such as an org chart or bill of materials.
sql
WITH RECURSIVE org_chart AS ( -- anchor: top-level employees with no manager SELECT employee_id, manager_id, name, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- recursive: join each employee to their manager's row SELECT e.employee_id, e.manager_id, e.name, oc.level + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.employee_id)SELECT employee_id, name, levelFROM org_chartORDER BY level, employee_id;
Conditional Aggregation & PIVOT
Reshape long data into wide, one column per category value.
sql
-- Portable across engines: conditional aggregationSELECT customer_id, SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue, SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount, COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_ordersFROM ordersGROUP BY customer_id;-- Native PIVOT (SQL Server, Snowflake, BigQuery)SELECT *FROM ordersPIVOT (SUM(amount) FOR status IN ('completed', 'refunded', 'cancelled')) AS p;
Statistical Window Functions
Compute quartiles, percentiles, and correlations without collapsing rows.
sql
SELECT customer_id, amount, NTILE(4) OVER (ORDER BY amount) AS quartile, PERCENT_RANK() OVER (ORDER BY amount) AS pct_rank, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER () AS median_amount, STDDEV(amount) OVER () AS amount_stddev, CORR(amount, days_since_signup) OVER () AS amount_signup_corrFROM orders;
Set Operations
Combine or compare result sets across queries instead of joins.
sql
-- Customers who ordered in BOTH January and FebruarySELECT customer_id FROM jan_ordersINTERSECTSELECT customer_id FROM feb_orders;-- Customers who ordered in January but NOT FebruarySELECT customer_id FROM jan_ordersEXCEPTSELECT customer_id FROM feb_orders;-- Stack rows without de-duplication (cheaper than UNION)SELECT customer_id, 'jan' AS month FROM jan_ordersUNION ALLSELECT customer_id, 'feb' AS month FROM feb_orders;
Reading Query Plans & Optimization
Terms for diagnosing why a query is slow.
- EXPLAIN- Shows the planner's chosen execution strategy (scan type, join order) without running the query
- EXPLAIN ANALYZE- Actually runs the query and reports real row counts and timings alongside the estimated plan
- Sequential scan vs. index scan- Seq scan reads every row; index scan uses an index to jump directly to matching rows
- Covering index- An index that contains every column a query needs, letting it answer the query without touching the table
- Query planner statistics- Row count and distribution estimates the optimizer uses to choose a plan; stale stats cause bad plans
- Partition pruning- Skipping entire partitions whose range can't match the WHERE clause, common on date-partitioned tables
- Materialized view- A precomputed, stored query result refreshed on a schedule, trading storage for query speed
Pro Tip
When debugging a complex query, build it up incrementally with CTEs and SELECT * at each stage - it's much easier to spot where a JOIN is duplicating or dropping rows than to debug the final nested query.
Was this cheat sheet helpful?
Explore Topics
#SQLForDataScience#SQLForDataScienceCheatSheet#DataScience#Beginner#AggregationsGROUPBY#WindowFunctions#JoinsCTEs#KeyConcepts#Functions#Databases#MachineLearning#CheatSheet#SkillVeris
