While machine learning frameworks and deep learning libraries get much of the spotlight, Structured Query Language (SQL) remains the true workhorse of real-world data science.
Before any model can be trained or any metric visualized, data must be extracted, filtered, joined, and aggregated from relational databases. Modern data science platforms like Snowflake, BigQuery, and Databricks rely heavily on SQL for data wrangling at scale.
Here are the fundamental SQL queries, techniques, and patterns every data scientist must master.
1. The Core Execution Order
Understanding the logical order in which SQL executes a query is critical for writing efficient, error-free code:
1. FROM / JOIN ──► Determines the target tables and joins datasets.
2. WHERE ──► Filters raw rows before grouping.
3. GROUP BY ──► Aggregates rows into distinct categories.
4. HAVING ──► Filters aggregated groups (unlike WHERE).
5. SELECT ──► Selects columns and calculates expressions.
6. ORDER BY ──► Sorts the final output set.
7. LIMIT / OFFSET──► Restricts the total output row count.
2. Basic Filtering and Aggregations
Aggregations allow data scientists to summarize millions of raw records into meaningful metrics like averages, totals, and distributions.
SQL
SELECT
department_id,
COUNT(employee_id) AS total_employees,
ROUND(AVG(salary), 2) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
WHERE status = 'Active'
GROUP BY department_id
HAVING COUNT(employee_id) >= 5
ORDER BY avg_salary DESC;
-
WHEREvs.HAVING:WHEREfilters individual rows before aggregation occurs.HAVINGfilters entire groups afterGROUP BYrunning calculations.
3. Combining Tables with JOINs
Data science datasets are rarely contained in a single table. Joining related entities across primary and foreign keys is an everyday task.
INNER JOIN LEFT JOIN FULL OUTER JOIN
┌───────┬───────┐ ┌───────┬───────┐ ┌───────┬───────┐
│ Table │ Table │ │ Table │ Table │ │ Table │ Table │
│ A │ B │ │ A │ B │ │ A │ B │
│ ┌─┴─┐ │ │ ████│█ │ │ │ ████│████ │
│ │███│ │ │ ████│█ │ │ │ ████│████ │
└─────┴───┴─────┘ └───────┴───────┘ └───────┴───────┘
Matching rows only All A + Matching B All rows from both
SQL
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS total_orders,
COALESCE(SUM(o.order_value), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
-
COALESCE(): ReplacesNULLvalues resulting from non-matchingLEFT JOINrecords with a default scalar value (e.g.,0).
4. Subqueries and CTEs (Common Table Expressions)
Complex analytical queries become unreadable when deeply nested. Common Table Expressions (CTEs) using the
WITH clause break queries into modular, step-by-step logic blocks.SQL
WITH high_value_orders AS (
SELECT
customer_id,
order_id,
order_value
FROM orders
WHERE order_value > 500
),
customer_summary AS (
SELECT
customer_id,
COUNT(order_id) AS premium_order_count
FROM high_value_orders
GROUP BY customer_id
)
SELECT
c.customer_name,
cs.premium_order_count
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.premium_order_count DESC;
5. Window Functions (Advanced Analytics)
Unlike standard
GROUP BY queries that collapse individual rows, Window Functions compute aggregate or ranking metrics across a subset of rows (“window”) while preserving every raw detail row.SQL
SELECT
employee_id,
department_id,
salary,
-- Rank employees by salary within each department
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank,
-- Calculate running total salary per department
SUM(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
) AS running_dept_salary
FROM employees;
Must-Know Window Functions
-
ROW_NUMBER(): Assigns a unique sequential integer to each row. -
DENSE_RANK(): Ranks rows without skipping rank values in case of ties. -
LEAD()/LAG(): Accesses values from the next or previous row, essential for calculating period-over-period growth rates.
SQL
-- Calculating Month-over-Month Revenue Growth
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS order_month,
SUM(order_value) AS revenue
FROM sales
GROUP BY 1
)
SELECT
order_month,
revenue,
LAG(revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue, 1) OVER (ORDER BY order_month))
/ LAG(revenue, 1) OVER (ORDER BY order_month) * 100, 2
) AS mom_growth_pct
FROM monthly_revenue;
6. Categorical Transformations with CASE WHEN
Feature engineering often requires bucketing continuous numerical features into discrete categorical segments.
SQL
SELECT
user_id,
total_spent,
CASE
WHEN total_spent >= 1000 THEN 'VIP'
WHEN total_spent BETWEEN 500 AND 999 THEN 'Regular'
ELSE 'Casual'
END AS customer_tier
FROM user_metrics;
Summary SQL Reference Checklist
| Technique | Function / Syntax | Practical Data Science Use Case |
| Aggregations | SUM(), AVG(), COUNT(), HAVING |
Summarizing KPI performance |
| Joining Tables | LEFT JOIN, INNER JOIN, COALESCE() |
Merging features across disparate tables |
| Modular Queries | WITH table_name AS (...) |
Structuring multi-stage data pipelines |
| Ranking Windows | ROW_NUMBER(), DENSE_RANK() |
Deduplicating records & identifying top-$N$ items |
| Time Series Windows | LAG(), LEAD() |
Calculating growth rates and time differences |
| Conditional Logic | CASE WHEN ... THEN ... END |
Binning continuous features for ML modeling |
Key Takeaway
Writing clean, modular SQL using CTEs and window functions accelerates exploratory analysis and feature engineering. Focus on understanding query execution order, mastering window functions like
LAG/LEAD, and utilizing CTEs to build robust data pipelines.
