Before a model ever sees your data, SQL is what shaped it — aggregated it, cleaned it, sampled it, and turned raw rows into features. This path moves past querying into the statistical and feature-engineering patterns data scientists reach for while a notebook is still empty.
A data scientist's SQL work happens before the modeling does. This path assumes you can already write a
SELECT — it's built around what comes next: summarizing a column honestly, spotting the
outliers that would poison a model, engineering features with window functions, and assembling a dataset
you can hand to Python (or anything else) with confidence that it's reproducible and correctly split.
Before you engineer a single feature, you need to know what's actually in the column — its center,
its spread, and how much it varies. AVG, STDDEV, and VARIANCE
run inside the database, on the full table, without pulling a single row into memory first.
SELECT
department_id,
AVG(salary) AS mean_salary,
STDDEV(salary) AS salary_stddev,
VARIANCE(salary) AS salary_variance
FROM employees
GROUP BY department_id;The mean lies about skewed data — a handful of extreme values can drag it far from where most of your
data actually sits. PERCENTILE_CONT and PERCENTILE_DISC give you the median
and quartiles directly, and NTILE buckets a distribution into equal groups without a
single line of application code.
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS p25,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75
FROM employees;Where an analyst uses window functions for reporting, a data scientist uses the same functions to build features: a rolling average as a smoothed signal, a lagged value as a "previous period" input, a rank as a normalized position within a group. Same syntax, different purpose — the output column feeds a model instead of a dashboard.
SELECT
customer_id,
order_date,
revenue,
AVG(revenue) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS revenue_7day_avg,
LAG(revenue, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_revenue
FROM orders;A handful of bad rows — a negative age, a duplicate order, a NULL where a number was expected — can quietly distort a model far more than they'd distort a report. The interquartile range (IQR) gives you a defensible, purely-SQL way to flag values that don't belong, before they ever reach a training set.
WITH bounds AS (
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
FROM transactions
)
SELECT t.*
FROM transactions t, bounds b
WHERE t.amount < b.q1 - 1.5 * (b.q3 - b.q1)
OR t.amount > b.q3 + 1.5 * (b.q3 - b.q1);Real feature-prep queries stack up: filter, join, aggregate, then aggregate again. CTEs keep that pipeline readable as named, sequential steps instead of a wall of nested subqueries. Recursive CTEs go further, walking hierarchical or graph-shaped data — an org chart, a referral network — that a single flat query can't traverse.
WITH RECURSIVE referral_chain AS (
SELECT id, referred_by, 1 AS depth
FROM users
WHERE referred_by IS NULL
UNION ALL
SELECT u.id, u.referred_by, rc.depth + 1
FROM users u
JOIN referral_chain rc ON u.referred_by = rc.id
)
SELECT * FROM referral_chain;Pulling a full table just to prototype against it wastes time and compute. A deterministic hash-based
split — the same row always lands in the same bucket — gives you a reproducible train/test split without
a random seed drifting between runs, and NTILE over a partition gives you stratified
sampling that respects group balance.
SELECT
*,
CASE WHEN MOD(customer_id, 10) < 8 THEN 'train' ELSE 'test' END AS split
FROM customers;CASE is how you bin a continuous value into ranges or one-hot encode a category without
leaving the database. PIVOT-style aggregation turns long, categorical data into the wide,
one-column-per-feature shape most model-training code expects.
SELECT
customer_id,
CASE
WHEN age < 25 THEN 'under_25'
WHEN age BETWEEN 25 AND 40 THEN '25_to_40'
ELSE 'over_40'
END AS age_bucket,
SUM(CASE WHEN category = 'electronics' THEN amount ELSE 0 END) AS electronics_spend,
SUM(CASE WHEN category = 'apparel' THEN amount ELSE 0 END) AS apparel_spend
FROM purchases
GROUP BY customer_id, age_bucket;The last step is packaging everything above into something reusable. A view keeps a feature definition as one source of truth that always reflects live data; materializing it into a table trades that freshness for speed when a training job needs to hit the same dataset repeatedly without recomputing it.
CREATE VIEW customer_features AS
SELECT
customer_id,
AVG(order_amount) AS avg_order_value,
COUNT(*) AS order_count,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;Build something end-to-end using the statistics, feature-engineering, and data-quality patterns from this path.
Data Scientist SQL interviews lean on statistical reasoning, window functions, and feature-engineering scenarios — exactly what this path covered.
The interview.php question bank, filtered to the functions category.
Deep, worked-answer coverage of the functions this path leans on most.
Data-shaped problems, the format most data science interviews actually use.
25 questions covering everything above. Score 80% or higher to earn your Data Scientist badge on your dashboard.
25 questions · pass at 80%+ to earn your Data Scientist badge on your dashboard. One-time payment, lifetime access.