Skip to content
Machine Learning & Data Science

SQL in ML interview questions and answers

55 questions, 55 with code. About 2 h 21 min to read them all.

By TestLab · Updated

Plan for an interview
55 questions

Track what you know and what to revise.

Sign in to track progress

You can open a few full answers without signing in. Sign in to read all

1

What are the different types of SQL JOIN?

SQL joins combine rows from two tables based on a condition. An INNER JOIN keeps only matching rows. A LEFT (OUTER) JOIN keeps every row of the left table, with NULLs where the right table has no match; a RIGHT JOIN is the mirror image. A FULL OUTER JOIN keeps all rows from both sides. A CROSS JOIN returns every combination (Cartesian product). A self-join joins a table to itself. Semi-joins (EXISTS) and anti-joins (NOT EXISTS) filter by the presence or absence of matches, and PostgreSQL’s LATERAL lets the right side reference the left row.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Join Types

Join Rows returned ML use
INNER Matches only Facts with valid dimensions
LEFT All left rows + matches Keep every training example, add optional features
RIGHT All right rows + matches Rare; rewrite as LEFT
FULL OUTER All rows from both Reconciling two sources
CROSS Every pair Entity × date grids, candidate generation
Self Table with itself Pairs of related events, graph edges
Semi (EXISTS) Left rows with a match, no duplicates Filter to active users
Anti (NOT EXISTS) Left rows without a match Users who never converted

Code Example

WITH a(id, a_val) AS (VALUES (1, 'a1'), (2, 'a2'), (3, 'a3')),
     b(id, b_val) AS (VALUES (2, 'b2'), (3, 'b3'), (4, 'b4'))
SELECT 'inner' AS join_type, count(*) AS rows FROM a JOIN b USING (id)
UNION ALL
SELECT 'left', count(*) FROM a LEFT JOIN b USING (id)
UNION ALL
SELECT 'right', count(*) FROM a RIGHT JOIN b USING (id)
UNION ALL
SELECT 'full', count(*) FROM a FULL JOIN b USING (id)
UNION ALL
SELECT 'cross', count(*) FROM a CROSS JOIN b
UNION ALL
SELECT 'anti (a not in b)', count(*) FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
-- Expected:
-- inner             | 2
-- left              | 3
-- right             | 3
-- full              | 4
-- cross             | 9
-- anti (a not in b) | 1
WITH a(id, a_val) AS (VALUES (1, 'a1'), (2, 'a2'), (3, 'a3')),
     b(id, b_val) AS (VALUES (2, 'b2'), (3, 'b3'), (4, 'b4'))
SELECT id, a_val, b_val FROM a FULL JOIN b USING (id) ORDER BY id;
-- Expected:
-- 1 | a1   | NULL
-- 2 | a2   | b2
-- 3 | a3   | b3
-- 4 | NULL | b4

Common Pitfalls

  • Conditions on the right table in WHERE after a LEFT JOIN (turns it into an inner join); put them in ON.

  • Accidental cross joins from missing join conditions.

  • Duplicate keys multiplying rows.

2

What is the difference between WHERE and HAVING clauses?

WHERE filters individual rows before grouping and aggregation; HAVING filters groups after aggregation. WHERE cannot use aggregate functions because aggregates do not exist yet at that stage, while HAVING conditions usually involve aggregates such as count(*) > 5 or avg(amount) > 100. When a condition can be applied to rows, put it in WHERE: it reduces the data early and is faster. Use HAVING only for conditions on group-level results.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Comparison

Aspect WHERE HAVING
Filters Rows Groups
Evaluated Before GROUP BY After aggregation
Aggregates allowed No Yes
Works without GROUP BY Yes Yes (treats the whole result as one group)
Performance Reduces rows early Runs after the work of grouping

Code Example

WITH orders(customer_id, amount, status) AS (
  VALUES ('c1', 50.0, 'paid'), ('c1', 70.0, 'paid'), ('c1', 500.0, 'refunded'),
         ('c2', 20.0, 'paid'), ('c3', 200.0, 'paid'), ('c3', 90.0, 'paid')
)
SELECT customer_id,
       count(*)    AS paid_orders,
       sum(amount) AS paid_spend
FROM orders
WHERE status = 'paid'               -- row filter: ignore refunded orders
GROUP BY customer_id
HAVING sum(amount) >= 100           -- group filter: customers with at least 100 paid spend
ORDER BY customer_id;
-- Expected:
-- c1 | 2 | 120.0
-- c3 | 2 | 290.0

Without the WHERE, c1’s refunded 500 would count toward the total. c2 passes the WHERE but its group total of 20 fails the HAVING.

Common Pitfalls

  • Writing WHERE count(*) > 1 (error: aggregates are not allowed in WHERE).

  • Putting row conditions in HAVING, which works only for grouped columns and wastes work.

  • Referring to a SELECT alias in WHERE (not allowed in standard SQL; repeat the expression or use a subquery).

3

How do you select distinct values from a column in SQL?

SELECT DISTINCT column FROM table returns each unique value once, removing duplicates from the result. With several columns, DISTINCT applies to the combination of all selected columns. NULL is treated as one distinct value. To count unique values use count(DISTINCT column) (which ignores NULLs). PostgreSQL’s DISTINCT ON (column) keeps one whole row per value, chosen by ORDER BY, and GROUP BY column gives the same unique list while also allowing aggregates such as how often each value occurs, which is useful for checking category cardinality before encoding.

Read full answer

Applies toPostgreSQL 16 (DISTINCT ON is PostgreSQL-specific)

Variants

Query Result
SELECT DISTINCT country FROM users Unique countries
SELECT DISTINCT country, plan FROM users Unique country-plan pairs
SELECT count(DISTINCT country) FROM users Number of unique non-NULL countries
SELECT country, count(*) FROM users GROUP BY country Unique values with frequencies
SELECT DISTINCT ON (user_id) * ... ORDER BY user_id, event_at DESC Latest row per user

Code Example

WITH users(user_id, country, plan, signup_at) AS (
  VALUES (1, 'IN', 'free', DATE '2026-01-05'), (2, 'IN', 'pro', DATE '2026-02-10'),
         (3, 'US', 'free', DATE '2026-03-01'), (4, NULL, 'free', DATE '2026-03-15'),
         (5, 'IN', 'free', DATE '2026-04-20')
)
SELECT DISTINCT country FROM users ORDER BY country;
-- Expected: IN, US, NULL (NULL sorts last in ascending order in PostgreSQL)
WITH users(user_id, country, plan, signup_at) AS (
  VALUES (1, 'IN', 'free', DATE '2026-01-05'), (2, 'IN', 'pro', DATE '2026-02-10'),
         (3, 'US', 'free', DATE '2026-03-01'), (4, NULL, 'free', DATE '2026-03-15'),
         (5, 'IN', 'free', DATE '2026-04-20')
)
SELECT count(DISTINCT country) AS unique_countries,
       count(DISTINCT (country, plan)) AS unique_country_plan_pairs
FROM users;
-- Expected: 2 | 4
-- Most recent signup per country, keeping the whole row (PostgreSQL)
WITH users(user_id, country, signup_at) AS (
  VALUES (1, 'IN', DATE '2026-01-05'), (2, 'IN', DATE '2026-02-10'),
         (3, 'US', DATE '2026-03-01'), (5, 'IN', DATE '2026-04-20')
)
SELECT DISTINCT ON (country) country, user_id, signup_at
FROM users
ORDER BY country, signup_at DESC;
-- Expected:
-- IN | 5 | 2026-04-20
-- US | 3 | 2026-03-01

The row-valued count(DISTINCT (country, plan)) counts the pairs (IN, free), (IN, pro), (US, free) and (NULL, free) as 4; rows are counted as long as the row value itself is not NULL.

Common Pitfalls

  • Using DISTINCT to hide duplicates caused by a wrong join instead of fixing the join.

  • DISTINCT on large tables being expensive; select only needed columns.

  • Forgetting that count(DISTINCT col) excludes NULL while SELECT DISTINCT includes it.

4

What does GROUP BY do in a SQL query?

GROUP BY collapses rows that share the same values in the listed columns into one row per group, and aggregate functions (count, sum, avg, min, max) then compute one value per group. Every column in the SELECT list must either be in the GROUP BY or inside an aggregate. Groups can be filtered after aggregation with HAVING, and extensions such as ROLLUP, CUBE and GROUPING SETS add subtotals. In ML work, GROUP BY entity_id is how raw events become one feature row per customer, product or session.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Logical Order of Evaluation

FROM → WHERE (filter rows) → GROUP BY → aggregates → HAVING (filter groups) → SELECT → ORDER BY → LIMIT

Code Example

WITH sessions(user_id, device, minutes, converted) AS (
  VALUES ('u1', 'mobile', 5, 0), ('u1', 'mobile', 12, 1), ('u1', 'desktop', 30, 0),
         ('u2', 'mobile', 3, 0), ('u3', 'desktop', 20, 1), ('u3', 'desktop', 25, 1)
)
SELECT user_id,
       count(*)                     AS sessions,
       sum(minutes)                 AS total_minutes,
       round(avg(converted), 2)     AS conversion_rate,
       count(DISTINCT device)       AS devices
FROM sessions
GROUP BY user_id
HAVING count(*) >= 2                -- keep users with at least 2 sessions
ORDER BY user_id;
-- Expected:
-- u1 | 3 | 47 | 0.33 | 2
-- u3 | 2 | 45 | 1.00 | 1

u2 has one session, so HAVING removes it after aggregation.

-- Group by two columns: one row per user and device
WITH sessions(user_id, device, minutes) AS (
  VALUES ('u1', 'mobile', 5), ('u1', 'mobile', 12), ('u1', 'desktop', 30)
)
SELECT user_id, device, sum(minutes) AS minutes
FROM sessions
GROUP BY user_id, device
ORDER BY user_id, device;
-- Expected:
-- u1 | desktop | 30
-- u1 | mobile  | 17

Common Pitfalls

  • Selecting a non-aggregated column not in GROUP BY (an error in standard SQL; MySQL may return an arbitrary value).

  • NULLs forming their own group.

  • Expecting groups in a particular order without ORDER BY.

5

How do you aggregate data in SQL with COUNT, AVG, SUM, MAX and MIN?

Aggregate functions reduce many rows to one value: COUNT counts rows (count(*)) or non-NULL values (count(col)), SUM adds, AVG averages, and MIN/MAX find extremes. Without GROUP BY they summarize the whole table; with GROUP BY they produce one row per group, which is how per-customer or per-product ML features are built. All of them except count(*) ignore NULLs, count(DISTINCT col) counts unique values, FILTER (WHERE ...) restricts an aggregate to some rows, and HAVING filters groups after aggregation.

Read full answer

Applies toPostgreSQL 16 (standard SQL; FILTER is PostgreSQL/SQLite, use CASE elsewhere)

Functions

Function Returns NULL behaviour
count(*) Number of rows Counts all rows
count(col) Non-NULL values Skips NULLs
count(DISTINCT col) Unique non-NULL values Skips NULLs
sum(col) Total Skips NULLs; NULL if all are NULL
avg(col) Mean Skips NULLs (not treated as 0)
min(col) / max(col) Extremes Skip NULLs; work on dates and text too

Code Example

WITH orders(order_id, customer_id, amount, status) AS (
  VALUES (1, 'c1', 20.0, 'paid'), (2, 'c1', 40.0, 'paid'), (3, 'c1', NULL, 'cancelled'),
         (4, 'c2', 100.0, 'paid'), (5, 'c2', 10.0, 'refunded')
)
SELECT customer_id,
       count(*)                                   AS orders,
       count(amount)                              AS orders_with_amount,
       sum(amount)                                AS total,
       round(avg(amount), 2)                      AS avg_amount,
       min(amount)                                AS min_amount,
       max(amount)                                AS max_amount,
       count(DISTINCT status)                     AS distinct_statuses,
       count(*) FILTER (WHERE status = 'paid')    AS paid_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
-- Expected:
-- c1 | 3 | 2 | 60.0  | 30.00 | 20.0 | 40.0  | 2 | 2
-- c2 | 2 | 2 | 110.0 | 55.00 | 10.0 | 100.0 | 2 | 1

c1’s average is 30 (60 / 2), not 20 (60 / 3), because avg skips the NULL amount. If missing should count as 0, use avg(COALESCE(amount, 0)).

Common Pitfalls

  • Integer averages: some engines return integers for avg of integer columns (PostgreSQL returns numeric).

  • sum of an empty or all-NULL group returning NULL, not 0.

  • Aggregating after a one-to-many join, which inflates sums and counts.

6

What is a subquery, and what is a typical use case?

A subquery is a query nested inside another query, in parentheses. It can return a single value (a scalar subquery, e.g. comparing each row with the overall average), a list used with IN or EXISTS (filtering rows that have related rows), or a table used in FROM (a derived table, e.g. aggregating first and then joining). A correlated subquery refers to columns of the outer query and is evaluated per outer row. Typical uses are filtering against an aggregate (“customers who spent more than average”), existence checks (“users with no orders”), and pre-aggregating before a join.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Types

Type Example use
Scalar WHERE amount > (SELECT avg(amount) FROM orders)
IN / EXISTS Customers with at least one refund
NOT EXISTS Users who never purchased (safer than NOT IN with NULLs)
Derived table (in FROM) Aggregate per customer, then join
Correlated Latest order per customer

Code Example

WITH orders(order_id, customer_id, amount) AS (
  VALUES (1, 'c1', 20.0), (2, 'c1', 80.0), (3, 'c2', 50.0), (4, 'c3', 10.0)
),
customers(customer_id) AS (
  VALUES ('c1'), ('c2'), ('c3'), ('c4')
)
SELECT 'above_average' AS check_name, string_agg(order_id::text, ',' ORDER BY order_id) AS result
FROM orders
WHERE amount > (SELECT avg(amount) FROM orders)                  -- scalar subquery (avg = 40)
UNION ALL
SELECT 'never_ordered', string_agg(customer_id, ',' ORDER BY customer_id)
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)   -- correlated
UNION ALL
SELECT 'top_spender', customer_id
FROM (SELECT customer_id, sum(amount) AS spend FROM orders GROUP BY customer_id) totals   -- derived table
WHERE spend = (SELECT max(spend) FROM (SELECT sum(amount) AS spend FROM orders GROUP BY customer_id) s);
-- Expected:
-- above_average | 2,3
-- never_ordered | c4
-- top_spender   | c1

Orders 2 (80) and 3 (50) are above the average of 40; c4 has no orders; c1 spent the most (100).

Subquery or CTE?

A CTE (WITH) is often clearer for multi-step logic and can be referenced several times; modern optimizers treat simple CTEs and subqueries similarly.

Common Pitfalls

  • NOT IN (subquery) returning no rows when the subquery contains a NULL.

  • A scalar subquery returning more than one row (runtime error).

  • Slow correlated subqueries on large tables; rewrite with joins or window functions.

7

How do database indexes work, and how do they relate to ML?

An index is a separate data structure, most often a B-tree, that keeps selected column values sorted with pointers to the table rows, so the database can find matching rows without scanning the whole table, much like a book’s index. Indexes speed up filters, joins, sorting and uniqueness checks, at the cost of extra storage and slower inserts and updates. For ML work they matter in three places: building training data (fast point-in-time joins on entity and timestamp), serving features online with low-latency lookups by key, and vector search, where specialized indexes (HNSW, IVFFlat in pgvector) find nearest-neighbour embeddings for retrieval and recommendations.

Read full answer

Applies toPostgreSQL 16, pgvector 0.7+

Index Types (PostgreSQL)

Type Good for ML-related example
B-tree Equality, ranges, sorting (user_id, event_at) for feature windows
Hash Equality only Key lookups
GIN Arrays, JSONB, full-text Tag features, text search
BRIN Huge, naturally ordered tables Append-only event logs by time
HNSW / IVFFlat (pgvector) Approximate nearest neighbours Embedding similarity search
Partial / expression Subsets or computed values WHERE status = 'active'

Code Example

CREATE TABLE events (
  user_id   text NOT NULL,
  event_at  timestamptz NOT NULL,
  amount    numeric
);

-- Composite index matching the feature query: equality on user_id, range on event_at
CREATE INDEX events_user_time_idx ON events (user_id, event_at);

-- Covering index: INCLUDE lets the query be answered from the index alone
CREATE INDEX events_user_time_amt_idx ON events (user_id, event_at) INCLUDE (amount);

-- Feature query that benefits: spend in the 30 days before a cutoff
EXPLAIN
SELECT sum(amount)
FROM events
WHERE user_id = 'u42'
  AND event_at >= TIMESTAMPTZ '2026-10-01 00:00+00'
  AND event_at <  TIMESTAMPTZ '2026-10-31 00:00+00';

-- Vector index for embedding search (pgvector)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE item_embeddings (item_id int PRIMARY KEY, embedding vector(384));
CREATE INDEX ON item_embeddings USING hnsw (embedding vector_cosine_ops);

Column order matters: an index on (user_id, event_at) helps queries filtering on user_id (alone or with a time range) but not queries filtering only on event_at.

Common Pitfalls

  • Indexes that are never used but slow every insert during large feature loads; drop or create them after bulk loads.

  • Expecting an index to help a query that returns most of the table (a sequential scan is faster).

  • Approximate vector indexes trading recall for speed; tune ef_search and measure recall.

8

How would you optimize a slow SQL query?

Optimizing a slow query starts with measuring, not guessing. Run EXPLAIN (ANALYZE, BUFFERS) (PostgreSQL) to see the actual plan: which steps take the time, whether tables are scanned sequentially, and where row estimates are far off. Common fixes are adding the right index (on filter and join columns, composite in the right order), filtering earlier and selecting fewer columns, rewriting non-sargable conditions (functions on indexed columns, leading-wildcard LIKE), replacing correlated subqueries with joins or window functions, aggregating before joining to avoid row explosions, refreshing statistics (ANALYZE), and partitioning or pre-aggregating very large tables.

Read full answer

Applies toPostgreSQL 16 (concepts apply to other engines)

Checklist

Symptom in the plan Likely fix
Seq Scan on a big table with a selective filter Index the filter column
Index not used: WHERE date(created_at) = ... Use a range: created_at >= d AND created_at < d + 1
Estimated rows far from actual ANALYZE; extended statistics for correlated columns
Nested loop over many rows Missing join index, or bad estimates
Huge row counts after a join Aggregate one side first; check key uniqueness
Sort / hash spilling to disk Increase work_mem for the query, reduce rows earlier
Correlated subquery per row Rewrite as a join, LATERAL, or window function
Scanning all history Partition by date; filter on the partition key

Code Example: Before and After

-- Slow: the function on created_at prevents index use, and SELECT * reads every column
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM events
WHERE date(created_at) = DATE '2026-11-01' AND user_id = 'u42';

-- Faster: sargable range condition, composite index, only needed columns
CREATE INDEX IF NOT EXISTS events_user_created_idx ON events (user_id, created_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT event_type, created_at
FROM events
WHERE user_id = 'u42'
  AND created_at >= TIMESTAMPTZ '2026-11-01 00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-11-02 00:00+00';

The composite index on (user_id, created_at) serves the equality filter first and then the time range; the plan should change from a sequential scan to an index scan.

-- Correlated subquery (runs once per customer) rewritten with a single aggregation
SELECT c.customer_id, o.order_count
FROM customers c
LEFT JOIN (SELECT customer_id, count(*) AS order_count FROM orders GROUP BY customer_id) o
  USING (customer_id);

Common Pitfalls

  • Adding indexes for every column, which slows writes and wastes space.

  • Testing on a tiny development database with different data distributions.

  • Optimizing without checking whether the query result is even needed that often (cache or materialize it).

9

How do you handle missing values in a SQL dataset?

Handling missing values in SQL starts with measuring them: count(*) - count(col) per column, by segment and over time, because a sudden rise usually means a pipeline problem. Then choose a strategy per column: filter rows only when the value is essential (e.g. the label), impute with a constant, the mean or median (COALESCE(col, value) with statistics from training data), use group-wise imputation via window functions, carry the last known value forward for time series, and add missing-indicator columns. Also remember SQL’s NULL rules: NULL = NULL is not true, aggregates skip NULLs, and NOT IN with NULLs returns nothing.

Read full answer

Applies toPostgreSQL 16

Strategies

Strategy SQL
Measure count(*) - count(col), avg((col IS NULL)::int)
Drop rows WHERE label IS NOT NULL
Constant COALESCE(country, 'unknown')
Mean / median COALESCE(x, (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY x) FROM train))
Group mean COALESCE(x, avg(x) OVER (PARTITION BY segment))
Last value carried forward Window over a “group id” built from count(x) OVER (ORDER BY t)
Indicator (x IS NULL)::int AS x_missing

Code Example: Median Imputation, Indicator and Forward Fill

WITH readings(sensor, t, temp) AS (
  VALUES ('s1', 1, 20.0), ('s1', 2, NULL), ('s1', 3, NULL), ('s1', 4, 23.0),
         ('s2', 1, NULL), ('s2', 2, 30.0)
),
grouped AS (
  SELECT *, count(temp) OVER (PARTITION BY sensor ORDER BY t) AS grp   -- increases at each non-NULL value
  FROM readings
)
SELECT sensor, t, temp,
       (temp IS NULL)::int AS temp_missing,
       max(temp) OVER (PARTITION BY sensor, grp) AS temp_ffill,
       COALESCE(temp, (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY temp) FROM readings)) AS temp_median_filled
FROM grouped
ORDER BY sensor, t;
-- Expected (median of 20, 23, 30 = 23):
-- s1 | 1 | 20.0 | 0 | 20.0 | 20
-- s1 | 2 | NULL | 1 | 20.0 | 23
-- s1 | 3 | NULL | 1 | 20.0 | 23
-- s1 | 4 | 23.0 | 0 | 23.0 | 23
-- s2 | 1 | NULL | 1 | NULL | 23
-- s2 | 2 | 30.0 | 0 | 30.0 | 30

The running count(temp) stays the same across consecutive NULLs, so each NULL shares a group with the last non-NULL value, and max over that group carries it forward. s2’s first reading has nothing before it, so it stays NULL.

Common Pitfalls

  • WHERE col <> 'x' silently excluding NULL rows.

  • Imputing with statistics from the full dataset including test rows.

  • Treating empty strings or sentinel values (−1, 9999) as valid; convert them with NULLIF(col, '').

10

How do you join two tables and keep only rows with matching keys?

An INNER JOIN returns only rows whose join keys match in both tables: SELECT ... FROM a JOIN b ON a.key = b.key (the INNER keyword is optional). Rows without a match on either side are dropped, and NULL keys never match, because NULL = NULL is not true. If a key appears several times on one side, every matching combination is returned, so check key uniqueness to avoid unexpected row multiplication. To keep rows of the first table that have a match without bringing in columns or duplicates from the second, use a semi-join with EXISTS.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Code Example

WITH customers(customer_id, name) AS (
  VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen'), (NULL, 'Ghost')
),
orders(order_id, customer_id, amount) AS (
  VALUES (101, 1, 20.0), (102, 1, 35.0), (103, 3, 15.0), (104, 4, 50.0), (105, NULL, 5.0)
)
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
-- Expected:
-- 1 | Asha | 101 | 20.0
-- 1 | Asha | 102 | 35.0
-- 3 | Chen | 103 | 15.0

Ben (no orders), order 104 (customer 4 does not exist) and the NULL-keyed rows are all excluded. Asha appears twice because she has two orders.

Semi-Join: Customers Who Have Ordered (No Duplicates)

WITH customers(customer_id, name) AS (
  VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen')
),
orders(order_id, customer_id) AS (
  VALUES (101, 1), (102, 1), (103, 3)
)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.customer_id;
-- Expected: 1 | Asha
--           3 | Chen

Common Pitfalls

  • Inner joins silently dropping training examples with missing dimension rows; compare row counts before and after.

  • Joining on columns with different types or formats ('001' vs 1).

  • Filtering a LEFT JOIN‘s right table in WHERE, which turns it into an inner join.

11

How do you merge multiple SQL result sets without duplicates?

UNION combines the rows of two or more queries and removes duplicates, while UNION ALL keeps every row. The queries must return the same number of columns with compatible types, in the same order; column names come from the first query. UNION has to sort or hash the combined result to find duplicates, so it is slower than UNION ALL; when the sources cannot overlap, or you want duplicates removed by a business key rather than by entire rows, use UNION ALL and then deduplicate explicitly with row_number(). Related operators: INTERSECT (rows in both) and EXCEPT (rows in the first but not the second).

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Set Operators

Operator Result
UNION Rows from either query, duplicates removed
UNION ALL All rows, duplicates kept (fastest)
INTERSECT Rows in both queries
EXCEPT (MINUS in Oracle) Rows in the first query not in the second

Code Example

WITH web_signups(email, source) AS (
  VALUES ('a@x.com', 'web'), ('b@x.com', 'web'), ('b@x.com', 'web')
),
app_signups(email, source) AS (
  VALUES ('b@x.com', 'app'), ('c@x.com', 'app')
)
SELECT email FROM web_signups
UNION
SELECT email FROM app_signups
ORDER BY email;
-- Expected: a@x.com, b@x.com, c@x.com (3 rows)
-- Keep one row per email but prefer the earliest source by priority
WITH web_signups(email, source, priority) AS (
  VALUES ('a@x.com', 'web', 1), ('b@x.com', 'web', 1)
),
app_signups(email, source, priority) AS (
  VALUES ('b@x.com', 'app', 2), ('c@x.com', 'app', 2)
),
combined AS (
  SELECT * FROM web_signups
  UNION ALL
  SELECT * FROM app_signups
)
SELECT email, source
FROM (SELECT *, row_number() OVER (PARTITION BY email ORDER BY priority) AS rn FROM combined) c
WHERE rn = 1
ORDER BY email;
-- Expected:
-- a@x.com | web
-- b@x.com | web
-- c@x.com | app

Plain UNION on (email, source) would keep both b@x.com rows because the source differs; the window-function approach deduplicates by email only.

Common Pitfalls

  • Columns in a different order between the queries (they are matched by position, not name).

  • Using UNION “to be safe” on huge tables where UNION ALL would do.

  • ORDER BY placed inside an individual branch instead of at the end.

12

How do you write a SQL query that pivots rows into columns?

Pivoting turns values from one column into separate columns, for example one row per customer with a column of spend for each product category, which is the wide format most ML libraries expect. The portable way is conditional aggregation: GROUP BY the row key and compute sum(amount) FILTER (WHERE category = 'x') (PostgreSQL) or SUM(CASE WHEN category = 'x' THEN amount END) (any SQL) for each target column. Some engines have a PIVOT operator (SQL Server, Oracle, Snowflake, BigQuery), and PostgreSQL’s tablefunc extension offers crosstab. Pivot columns must be listed explicitly; for unknown categories, generate the SQL dynamically.

Read full answer

Applies toPostgreSQL 16 (portable CASE version shown too)

Code Example: Conditional Aggregation

WITH purchases(customer_id, category, amount) AS (
  VALUES ('c1', 'books', 20.0), ('c1', 'books', 15.0), ('c1', 'games', 60.0),
         ('c2', 'music', 10.0), ('c2', 'books', 5.0),
         ('c3', 'games', 30.0)
)
SELECT customer_id,
       COALESCE(sum(amount) FILTER (WHERE category = 'books'), 0) AS spend_books,
       COALESCE(sum(amount) FILTER (WHERE category = 'games'), 0) AS spend_games,
       COALESCE(sum(amount) FILTER (WHERE category = 'music'), 0) AS spend_music,
       count(*) AS purchases
FROM purchases
GROUP BY customer_id
ORDER BY customer_id;
-- Expected:
-- c1 | 35.0 | 60.0 | 0    | 3
-- c2 | 5.0  | 0    | 10.0 | 2
-- c3 | 0    | 30.0 | 0    | 1

The portable form replaces each FILTER with SUM(CASE WHEN category = 'books' THEN amount ELSE 0 END).

PIVOT Operator (Snowflake / SQL Server Style)

SELECT *
FROM (SELECT customer_id, category, amount FROM purchases) src
PIVOT (SUM(amount) FOR category IN ('books', 'games', 'music')) AS p;

Column naming and quoting rules differ between engines; check the dialect’s documentation.

Unpivoting (Columns Back to Rows)

SELECT customer_id, v.category, v.amount
FROM wide_spend
CROSS JOIN LATERAL (VALUES ('books', spend_books), ('games', spend_games)) AS v(category, amount);

Common Pitfalls

  • Missing COALESCE, leaving NULLs where the model expects 0.

  • New categories appearing in production that the fixed column list ignores; monitor or generate the list from a training-time category table.

  • Pivoting thousands of sparse categories; group rare ones first or keep the long format.

13

What is data normalization in SQL, and how does it affect ML models?

“Normalization” means two different things in SQL work. Database normalization organizes tables to remove redundancy (1NF, 2NF, 3NF): customers, orders and products live in separate tables linked by keys, which keeps data consistent. For ML, this means training data must be denormalized with joins into one wide row per example, and those joins must be correct and point-in-time. Feature normalization (scaling) rescales numeric features, with min-max to [0, 1] or z-score standardization, so that distance-based and gradient-based models (k-NN, SVM, neural networks, regularized linear models) are not dominated by large-scale features; tree models do not need it. Both can be done in SQL.

Read full answer

Applies toPostgreSQL 16

The Two Meanings

Database normalization Feature scaling
Goal Consistency, no duplicated facts Comparable feature scales
Technique Split into related tables (3NF) Min-max, z-score, log
ML impact Requires careful joins/denormalization Affects convergence and distance-based models
Risk Joins duplicating rows, wrong historical values Leakage if statistics include test data

Code Example: Z-Score Standardization with Training Statistics

WITH train(customer_id, income, age) AS (
  VALUES ('c1', 30000, 25), ('c2', 50000, 35), ('c3', 70000, 45)
),
stats AS (
  SELECT avg(income) AS mu_income, stddev_samp(income) AS sd_income,
         avg(age) AS mu_age, stddev_samp(age) AS sd_age
  FROM train
),
scoring(customer_id, income, age) AS (
  VALUES ('n1', 60000, 30), ('n2', 30000, 50)
)
SELECT s.customer_id,
       round((s.income - st.mu_income) / NULLIF(st.sd_income, 0), 3) AS income_z,
       round((s.age - st.mu_age) / NULLIF(st.sd_age, 0), 3)          AS age_z
FROM scoring s
CROSS JOIN stats st
ORDER BY s.customer_id;
-- Training: income mean 50000, SD 20000; age mean 35, SD 10
-- Expected:
-- n1 |  0.500 | -0.500
-- n2 | -1.000 |  1.500

Without scaling, income (tens of thousands) would dominate age (tens) in a k-NN distance; after standardization both are measured in standard deviations. The statistics come only from training rows.

Common Pitfalls

  • Confusing the two meanings in conversation; clarify which one is meant.

  • Computing scaling statistics on all data including the test set.

  • Denormalizing with joins that duplicate rows (one-to-many joins before aggregation).

14

How do you extract time-based features from a SQL datetime field?

Time-based features turn a timestamp into model inputs: calendar parts (hour, day of week, month, quarter, week of year), flags (weekend, business hours, holiday via a calendar table, month end), cyclical encodings (sine and cosine of hour or month, so 23:00 is close to 00:00), and durations (days since signup, days since the last event). In PostgreSQL use extract(field FROM ts), date_trunc, to_char and date subtraction. Convert to the user’s local time zone first (ts AT TIME ZONE 'Asia/Kolkata'), because “hour of day” in UTC can mislead the model.

Read full answer

Applies toPostgreSQL 16

Features

Feature PostgreSQL
Hour extract(hour FROM ts)
Day of week (ISO, Mon=1) extract(isodow FROM ts)
Month / quarter extract(month FROM ts), extract(quarter FROM ts)
Weekend flag extract(isodow FROM ts) IN (6, 7)
Cyclical hour sin(2 * pi() * extract(hour FROM ts) / 24)
Days since ts::date - signup_date
Month end ts::date = (date_trunc('month', ts) + interval '1 month - 1 day')::date

Code Example

WITH events(event_id, event_at_utc, signup_date) AS (
  VALUES (1, TIMESTAMPTZ '2026-11-07 18:30:00+00', DATE '2026-10-01'),   -- Saturday
         (2, TIMESTAMPTZ '2026-11-09 03:15:00+00', DATE '2026-11-01')    -- Monday
),
localized AS (
  SELECT event_id, signup_date, event_at_utc AT TIME ZONE 'Asia/Kolkata' AS local_ts
  FROM events
)
SELECT event_id,
       local_ts,
       extract(hour FROM local_ts)::int                              AS hour,
       extract(isodow FROM local_ts)::int                            AS iso_weekday,
       (extract(isodow FROM local_ts) IN (6, 7))::int                AS is_weekend,
       round(sin(2 * pi() * extract(hour FROM local_ts) / 24)::numeric, 3) AS hour_sin,
       round(cos(2 * pi() * extract(hour FROM local_ts) / 24)::numeric, 3) AS hour_cos,
       local_ts::date - signup_date                                  AS days_since_signup
FROM localized
ORDER BY event_id;
-- Expected (India is UTC+5:30):
-- 1 | 2026-11-08 00:00:00 | 0 | 7 | 1 | 0.000 | 1.000  | 38
-- 2 | 2026-11-09 08:45:00 | 8 | 1 | 0 | 0.866 | -0.500 | 8

Event 1 happens on Saturday evening in UTC but just after midnight on Sunday in India, which shows why the time zone must match the user’s experience.

Common Pitfalls

  • Using UTC hours for user-behaviour features.

  • Treating day of week or hour as plain numbers in linear models; use one-hot or cyclical encodings.

  • extract(dow) (Sunday = 0) and isodow (Sunday = 7) mixed up between queries.

15

What are SQL window functions, and how do they help feature engineering?

Window functions compute a value for each row from a set of related rows (its “window”) without collapsing rows the way GROUP BY does. The OVER (PARTITION BY ... ORDER BY ... frame) clause defines the window: partition by entity, order by time, and optionally limit the frame to a range of rows or time. For feature engineering they are essential: ranking (row_number, rank, ntile), lags and leads (lag, lead), running and rolling aggregates (sum, avg, count with frames), group comparisons (value vs partition average) and deduplication (latest row per key).

Read full answer

Applies toPostgreSQL 16 (standard SQL:2003 window functions)

Function Families

Family Functions Feature examples
Ranking row_number, rank, dense_rank, ntile, percent_rank Order sequence number, spend decile
Offset lag, lead, first_value, last_value Previous amount, first purchase category
Aggregate sum, avg, count, min, max, stddev with OVER Running total, 7-day average, share of group

Code Example

WITH orders(customer_id, order_date, amount) AS (
  VALUES ('c1', DATE '2026-10-01', 40), ('c1', DATE '2026-10-05', 60),
         ('c1', DATE '2026-10-20', 20), ('c2', DATE '2026-10-02', 100)
)
SELECT customer_id, order_date, amount,
       row_number() OVER w                                         AS order_number,
       sum(amount) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_spend,
       lag(amount) OVER w                                          AS prev_amount,
       first_value(amount) OVER w                                  AS first_amount,
       round(amount::numeric / avg(amount) OVER (PARTITION BY customer_id), 2) AS vs_customer_avg
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, order_date;
-- Expected:
-- c1 | 2026-10-01 |  40 | 1 |  40 | NULL | 40  | 1.00
-- c1 | 2026-10-05 |  60 | 2 | 100 | 40   | 40  | 1.50
-- c1 | 2026-10-20 |  20 | 3 | 120 | 60   | 40  | 0.50
-- c2 | 2026-10-02 | 100 | 1 | 100 | NULL | 100 | 1.00

c1’s average order is 40, so the second order is 1.5 times it. Note that vs_customer_avg uses the customer’s average over all orders, including later ones: fine for description, but leakage for a feature at a past time; use a frame ending at CURRENT ROW or 1 PRECEDING for that.

Common Pitfalls

  • last_value with the default frame returns the current row; specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

  • Filtering on a window function in WHERE (not allowed); wrap it in a subquery or CTE.

  • Window aggregates over all time leaking future information.

16

How do you calculate moving averages in SQL?

A moving (rolling) average is the mean of a value over a sliding window, such as the last 7 days of sales. In SQL, use avg(x) OVER (PARTITION BY entity ORDER BY time ROWS BETWEEN n − 1 PRECEDING AND CURRENT ROW) for the last n rows, or, in PostgreSQL, RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW for a time-based window that handles missing dates correctly. Early rows have partial windows, so add a count to know when the window is full. For a forecasting feature, end the window at 1 PRECEDING so the current value is not included.

Read full answer

Applies toPostgreSQL 16

Window Frames

Frame Meaning
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW Current row and 2 before (3 rows)
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING Previous 3 rows, excluding current (feature-safe)
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW All rows in the last 7 calendar days
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Cumulative (expanding) mean

Code Example

WITH daily(day, sales) AS (
  VALUES (DATE '2026-11-01', 10), (DATE '2026-11-02', 20), (DATE '2026-11-03', 30),
         (DATE '2026-11-04', 40), (DATE '2026-11-06', 60)          -- 11-05 missing
)
SELECT day, sales,
       round(avg(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)   AS ma3_rows,
       round(avg(sales) OVER (ORDER BY day RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW), 2) AS ma3_days,
       round(avg(sales) OVER (ORDER BY day ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING), 2)   AS prev3_avg,
       count(*) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)              AS rows_in_window
FROM daily
ORDER BY day;
-- Expected:
-- 2026-11-01 | 10 | 10.00 | 10.00 | NULL  | 1
-- 2026-11-02 | 20 | 15.00 | 15.00 | 10.00 | 2
-- 2026-11-03 | 30 | 20.00 | 20.00 | 15.00 | 3
-- 2026-11-04 | 40 | 30.00 | 30.00 | 20.00 | 3
-- 2026-11-06 | 60 | 43.33 | 50.00 | 30.00 | 3

On 11-06 the row-based average uses 11-03, 11-04 and 11-06 (43.33), while the day-based window covers 11-04 to 11-06 and finds only two rows (50.00); with the missing day filled as 0 it would be 33.33. Choose the behaviour deliberately, ideally by filling gaps with a calendar first.

Common Pitfalls

  • ROWS windows over gappy data silently spanning more days.

  • Omitting ORDER BY inside OVER, which averages the whole partition.

  • Including the current value in a feature used to predict it.

17

How do you create lagged features in SQL?

Lagged features give each row the value of a variable from an earlier period, for example yesterday’s sales or the previous order’s amount. In SQL, the LAG(column, n, default) OVER (PARTITION BY entity ORDER BY time) window function returns the value n rows earlier within each entity; LEAD looks forward (useful for building labels, never features). Derived features include differences (x - LAG(x)), percentage changes and time since the previous event. Because LAG works on rows, fill missing dates with a calendar first if you need “the value 7 days ago” rather than “7 rows ago”.

Read full answer

Applies toPostgreSQL 16 (standard SQL window functions)

Code Example

WITH orders(customer_id, order_date, amount) AS (
  VALUES ('c1', DATE '2026-10-01', 40.0),
         ('c1', DATE '2026-10-09', 55.0),
         ('c1', DATE '2026-10-30', 30.0),
         ('c2', DATE '2026-10-05', 20.0),
         ('c2', DATE '2026-10-06', 25.0)
)
SELECT customer_id, order_date, amount,
       lag(amount) OVER w                          AS prev_amount,
       lag(amount, 2) OVER w                       AS amount_2_orders_ago,
       amount - lag(amount) OVER w                 AS change_vs_prev,
       order_date - lag(order_date) OVER w         AS days_since_prev,
       lead(order_date) OVER w - order_date        AS days_to_next      -- label-style, not a feature
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, order_date;
-- Expected:
-- c1 | 2026-10-01 | 40.0 | NULL | NULL | NULL  | NULL | 8
-- c1 | 2026-10-09 | 55.0 | 40.0 | NULL | 15.0  | 8    | 21
-- c1 | 2026-10-30 | 30.0 | 55.0 | 40.0 | -25.0 | 21   | NULL
-- c2 | 2026-10-05 | 20.0 | NULL | NULL | NULL  | NULL | 1
-- c2 | 2026-10-06 | 25.0 | 20.0 | NULL | 5.0   | 1    | NULL

days_to_next uses LEAD, which looks into the future: it can define a target such as “orders again within 14 days”, but must never be used as an input feature.

Calendar-Based Lags

For daily series with gaps, build a full calendar with generate_series, LEFT JOIN the data with COALESCE(value, 0), then apply LAG(value, 7) to get the true value from 7 days earlier.

Common Pitfalls

  • Missing PARTITION BY, so one customer’s lag comes from another customer.

  • Ties in the ORDER BY column making lags non-deterministic; add a tiebreaker.

  • Lag NULLs on first rows; fill with a default (lag(x, 1, 0)) only if 0 is meaningful.

18

How do you compute a ratio feature within groups in SQL?

Ratio features within groups compare a value with its group, for example a customer’s spend as a share of their segment’s total, a product’s sales relative to its category average, or a user’s purchases divided by their visits. Window functions compute group totals and averages without collapsing rows: amount / sum(amount) OVER (PARTITION BY group). For ratios of two counts per entity (conversion rate), aggregate first with GROUP BY. Always cast to numeric to avoid integer division and use NULLIF(denominator, 0) to avoid division-by-zero errors.

Read full answer

Applies toPostgreSQL 16 (standard window functions)

Common Ratio Features

Feature SQL
Share of group total x / sum(x) OVER (PARTITION BY g)
Relative to group mean x / avg(x) OVER (PARTITION BY g)
Rate per entity sum(purchases)::numeric / NULLIF(sum(visits), 0)
Share of a sub-type count(*) FILTER (WHERE type = 'refund')::numeric / count(*)
Rank within group percent_rank() OVER (PARTITION BY g ORDER BY x)

Code Example

WITH sales(store, category, product, revenue, units) AS (
  VALUES ('s1', 'snacks', 'chips',   300, 100),
         ('s1', 'snacks', 'nuts',    100,  20),
         ('s1', 'drinks', 'cola',    200,  80),
         ('s1', 'drinks', 'water',   200, 200),
         ('s1', 'drinks', 'juice',     0,   0)
)
SELECT product, category,
       round(revenue::numeric / NULLIF(sum(revenue) OVER (PARTITION BY category), 0), 3) AS share_of_category,
       round(revenue::numeric / NULLIF(avg(revenue) OVER (PARTITION BY category), 0), 3) AS vs_category_avg,
       round(revenue::numeric / NULLIF(units, 0), 2)                                     AS price_per_unit
FROM sales
ORDER BY category, product;
-- Expected:
-- cola  | drinks | 0.500 | 1.500 | 2.50
-- juice | drinks | 0.000 | 0.000 | NULL
-- water | drinks | 0.500 | 1.500 | 1.00
-- chips | snacks | 0.750 | 1.500 | 3.00
-- nuts  | snacks | 0.250 | 0.500 | 5.00

The drinks total is 400 with an average of 133.33, so cola’s share is 0.5 and it sells at 1.5 times the category average; juice has no units, so its price per unit is NULL instead of an error.

Common Pitfalls

  • Integer division (200 / 400 = 0 in many engines).

  • Ratios with tiny denominators being extremely noisy; add smoothing, e.g. (purchases + 1) / (visits + 10).

  • Group statistics computed over all time, including data after the prediction time.

19

How do you discretize a continuous variable in SQL?

Discretizing a continuous variable assigns each value to an interval (bin), turning, for example, income into bands. SQL offers three main methods: fixed, domain-defined edges with CASE or width_bucket(x, ARRAY[...]); equal-width bins with width_bucket(x, min, max, n); and equal-frequency (quantile) bins with ntile(n) OVER (ORDER BY x) or edges from percentile_cont. Equal-frequency bins handle skewed data better, fixed edges are most interpretable, and for ML the edges should be computed on training data and reused at scoring time.

Read full answer

Applies toPostgreSQL 16

Comparison

Method SQL Pros Cons
Fixed edges width_bucket(x, ARRAY[0, 18, 65]) Interpretable, stable Needs domain knowledge
Equal width width_bucket(x, 0, 100, 5) Simple Skew leaves bins nearly empty
Equal frequency ntile(4) OVER (ORDER BY x) Balanced counts Edges change with data unless stored
Quantile edges percentile_cont(ARRAY[...]) → stored array Reusable at scoring Ties can merge edges

Code Example: Quantile Edges Learned Once, Then Applied

WITH train(x) AS (
  VALUES (1.0), (2.0), (3.0), (4.0), (5.0), (6.0), (7.0), (8.0), (9.0)
),
edges AS (
  SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY x) AS cuts
  FROM train
),
new_data(id, x) AS (
  VALUES (1, 0.5), (2, 3.0), (3, 5.5), (4, 7.0), (5, 20.0)
)
SELECT n.id, n.x, e.cuts, width_bucket(n.x, e.cuts) + 1 AS quartile_bin
FROM new_data n
CROSS JOIN edges e
ORDER BY n.id;
-- cuts = {3,5,7}
-- Expected:
-- 1 |  0.5 | {3,5,7} | 1
-- 2 |  3.0 | {3,5,7} | 2
-- 3 |  5.5 | {3,5,7} | 3
-- 4 |  7.0 | {3,5,7} | 4
-- 5 | 20.0 | {3,5,7} | 4

width_bucket(x, thresholds) returns 0 below the first threshold and k when x is at or above the k-th threshold, so adding 1 gives bins 1-4; a value equal to an edge goes into the higher bin. Store cuts in a parameters table for production scoring.

Common Pitfalls

  • Equal-width bins on skewed data, leaving most rows in one bin.

  • Discretizing when the model (trees, GBMs) can use the raw value; binning throws away information.

  • Inconsistent edge handling between SQL and Python preprocessing.

20

How do you join transactions to a dimension table to extract ML features?

Joining transactional (fact) data to dimension tables enriches each transaction with attributes of the customer, product, store or date, which you then aggregate into features. The keys to correct features are: join on the surrogate or business key with the expected cardinality (many facts to one dimension row), use point-in-time joins to slowly changing dimensions (the attribute value valid at the transaction time, not today’s value), use LEFT JOIN so transactions with missing dimension rows are not silently dropped, and aggregate after joining to one row per entity at the label time.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Checklist

Concern Solution
Cardinality Dimension key unique (or unique for each validity period)
Historical attributes t.txn_at >= d.valid_from AND (t.txn_at < d.valid_to OR d.valid_to IS NULL)
Missing dimension rows LEFT JOIN + COALESCE(..., 'unknown')
Leakage Only transactions before the cutoff
Aggregation GROUP BY customer_id with FILTER for category-specific features

Code Example: Point-in-Time Join and Customer Features

WITH transactions(txn_id, customer_id, product_id, txn_date, amount) AS (
  VALUES (1, 'c1', 'p1', DATE '2026-03-10', 50.0),
         (2, 'c1', 'p2', DATE '2026-07-05', 30.0),
         (3, 'c2', 'p1', DATE '2026-08-01', 20.0),
         (4, 'c2', 'p9', DATE '2026-08-15', 10.0)      -- product missing from the dimension
),
product_dim(product_id, category, valid_from, valid_to) AS (
  VALUES ('p1', 'outdoor', DATE '2026-01-01', DATE '2026-06-01'),
         ('p1', 'sports',  DATE '2026-06-01', NULL),     -- recategorized in June
         ('p2', 'outdoor', DATE '2026-01-01', NULL)
),
enriched AS (
  SELECT t.*, COALESCE(d.category, 'unknown') AS category
  FROM transactions t
  LEFT JOIN product_dim d
    ON d.product_id = t.product_id
   AND t.txn_date >= d.valid_from
   AND (d.valid_to IS NULL OR t.txn_date < d.valid_to)
  WHERE t.txn_date < DATE '2026-09-01'                  -- feature cutoff
)
SELECT customer_id,
       count(*)                                         AS txns,
       sum(amount)                                      AS spend,
       sum(amount) FILTER (WHERE category = 'outdoor')  AS outdoor_spend,
       count(DISTINCT category)                         AS categories
FROM enriched
GROUP BY customer_id
ORDER BY customer_id;
-- Expected:
-- c1 | 2 | 80.0 | 80.0 | 1
-- c2 | 2 | 30.0 | NULL | 2

Transaction 1 (March) uses p1’s old category “outdoor”; transaction 3 (August) uses “sports”. The missing product p9 is kept as “unknown” instead of disappearing.

Common Pitfalls

  • Joining to the current dimension row for historical transactions (leakage and wrong attributes).

  • Duplicate dimension keys multiplying fact rows; check count(*) before and after the join.

  • FILTER sums returning NULL rather than 0; wrap in COALESCE if the model needs 0.

21

How do you identify and remove duplicate records in SQL?

Duplicate handling in SQL starts by defining what “duplicate” means: identical rows, or the same business key (e.g. the same order_id loaded twice) with possibly different timestamps. Identify duplicates with GROUP BY key HAVING count(*) > 1, then keep one row per key with row_number() OVER (PARTITION BY key ORDER BY preferred_column DESC) and filter rn = 1, which lets you choose which duplicate survives (the latest load, the most complete row). In PostgreSQL, DISTINCT ON (key) does the same concisely. To delete in place, delete rows whose rn > 1 using the table’s primary key or ctid.

Read full answer

Applies toPostgreSQL 16 (standard SQL for row_number)

Code Example: Find and Keep the Latest Row per Key

WITH raw_orders(order_id, customer_id, amount, loaded_at) AS (
  VALUES (1, 'c1', 20.0, TIMESTAMP '2026-11-01 10:00'),
         (1, 'c1', 20.0, TIMESTAMP '2026-11-01 10:05'),
         (2, 'c2', 35.0, TIMESTAMP '2026-11-01 11:00'),
         (3, 'c3', 15.0, TIMESTAMP '2026-11-01 12:00'),
         (3, 'c3', 18.0, TIMESTAMP '2026-11-02 09:00')   -- corrected amount
)
SELECT order_id, count(*) AS copies
FROM raw_orders
GROUP BY order_id
HAVING count(*) > 1
ORDER BY order_id;
-- Expected: 1 | 2
--           3 | 2
WITH raw_orders(order_id, customer_id, amount, loaded_at) AS (
  VALUES (1, 'c1', 20.0, TIMESTAMP '2026-11-01 10:00'),
         (1, 'c1', 20.0, TIMESTAMP '2026-11-01 10:05'),
         (2, 'c2', 35.0, TIMESTAMP '2026-11-01 11:00'),
         (3, 'c3', 15.0, TIMESTAMP '2026-11-01 12:00'),
         (3, 'c3', 18.0, TIMESTAMP '2026-11-02 09:00')
)
SELECT order_id, customer_id, amount, loaded_at
FROM (SELECT *, row_number() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) AS rn
      FROM raw_orders) r
WHERE rn = 1
ORDER BY order_id;
-- Expected:
-- 1 | c1 | 20.0 | 2026-11-01 10:05:00
-- 2 | c2 | 35.0 | 2026-11-01 11:00:00
-- 3 | c3 | 18.0 | 2026-11-02 09:00:00

Deleting Duplicates in Place (PostgreSQL)

DELETE FROM orders o
USING (SELECT ctid, row_number() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) AS rn
       FROM orders) d
WHERE o.ctid = d.ctid AND d.rn > 1;

ALTER TABLE orders ADD PRIMARY KEY (order_id);   -- prevent new duplicates

Common Pitfalls

  • SELECT DISTINCT * when duplicates differ in a timestamp column (they are not removed).

  • ORDER BY in the window without a tiebreaker, so the surviving row changes between runs.

  • Deduplicating after joins, where the duplication came from the join itself.

22

How can you handle outliers in SQL before training ML models?

Outlier handling in SQL has two steps: detect unusual values, then decide what to do with them. Detection uses percentiles (percentile_cont) for IQR fences (below Q1 − 1.5·IQR or above Q3 + 1.5·IQR), z-scores from avg and stddev (often |z| > 3), or per-group versions with window functions. Treatment options are capping (winsorizing) at chosen percentiles with LEAST/GREATEST, log transforms for skewed positive values, flag columns so the model can learn from them, or removal only when values are clearly errors. Compute thresholds on training data and store them for scoring.

Read full answer

Applies toPostgreSQL 16

Options

Method SQL Notes
IQR fences percentile_cont(0.25), (0.75) Robust; good for skewed data
z-score (x - avg(x) OVER ()) / stddev_samp(x) OVER () Assumes rough normality; outliers inflate SD
Winsorize LEAST(GREATEST(x, p01), p99) Keeps rows, limits influence
Log transform ln(1 + x) For positive, right-skewed values
Flag (x > upper)::int AS is_outlier Preserves information
Remove WHERE x BETWEEN lower AND upper Only for impossible values (errors)

Code Example: IQR Fences, Capping and Flags

WITH orders(order_id, amount) AS (
  VALUES (1, 20.0), (2, 25.0), (3, 30.0), (4, 35.0), (5, 40.0),
         (6, 45.0), (7, 50.0), (8, 55.0), (9, 400.0)
),
fences AS (
  SELECT q1, q3, q1 - 1.5 * (q3 - q1) AS lower_fence, q3 + 1.5 * (q3 - q1) AS upper_fence
  FROM (SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
               percentile_cont(0.75) WITHIN GROUP (ORDER BY amount) AS q3
        FROM orders) q
)
SELECT o.order_id, o.amount,
       LEAST(GREATEST(o.amount, f.lower_fence), f.upper_fence) AS amount_capped,
       (o.amount > f.upper_fence OR o.amount < f.lower_fence)::int AS is_outlier
FROM orders o
CROSS JOIN fences f
WHERE o.order_id IN (1, 8, 9)
ORDER BY o.order_id;
-- Quartiles: Q1 = 30, Q3 = 50, IQR = 20, fences [0, 80]
-- Expected:
-- 1 |  20.0 | 20 | 0
-- 8 |  55.0 | 55 | 0
-- 9 | 400.0 | 80 | 1

percentile_cont returns double precision, so the capped column shows plain numbers. Save fences as a table from training data and join it at scoring time.

Common Pitfalls

  • Deleting legitimate extreme values (large but real orders often matter most).

  • z-scores computed with the outliers included, which hides them.

  • Thresholds computed on the full dataset including test data.

23

How do you format and concatenate columns for text-based ML features in SQL?

Text features often combine several columns into one string, for example a product’s title, brand and category for a text classifier or an embedding model. In SQL, use concat_ws(separator, ...), which skips NULLs, rather than ||, which returns NULL if any part is NULL; clean values with lower, trim, regexp_replace and initcap; format numbers and dates with to_char or format(); and aggregate many rows into one document per entity with string_agg(... ORDER BY ...). Labelled templates (“brand: Acme | category: shoes”) help language models understand the fields.

Read full answer

Applies toPostgreSQL 16 (concat_ws and string_agg are also in MySQL, SQL Server 2017+, Snowflake)

Functions

Task PostgreSQL
Concatenate, skipping NULLs ‘concat_ws(’ ‘, a, b, c)`

| Concatenate, NULL-propagating | a || ' ' || b | | Normalize | lower(trim(regexp_replace(x, '\s+', ' ', 'g'))) | | Format numbers/dates | to_char(price, 'FM999990.00'), to_char(d, 'YYYY-MM') | | Templates | format('brand: %s; category: %s', brand, category) | | Many rows → one text | string_agg(note, ' ' ORDER BY created_at) |

Code Example

WITH products(product_id, title, brand, category, price) AS (
  VALUES (1, '  Trail Running SHOES ', 'Acme', 'Footwear', 89.5),
         (2, 'Coffee Maker', NULL, 'Kitchen', 45.0)
),
reviews(product_id, created_at, note) AS (
  VALUES (1, DATE '2026-10-01', 'great grip'), (1, DATE '2026-10-03', 'runs small'),
         (2, DATE '2026-10-02', 'loud')
)
SELECT p.product_id,
       concat_ws(' | ',
                 'title: ' || lower(trim(p.title)),
                 'brand: ' || lower(p.brand),
                 'category: ' || lower(p.category),
                 'price: ' || to_char(p.price, 'FM999990.00'))           AS product_text,
       string_agg(r.note, '; ' ORDER BY r.created_at)                    AS review_text
FROM products p
LEFT JOIN reviews r USING (product_id)
GROUP BY p.product_id, p.title, p.brand, p.category, p.price
ORDER BY p.product_id;
-- Expected:
-- 1 | title: trail running shoes | brand: acme | category: footwear | price: 89.50 | great grip; runs small
-- 2 | title: coffee maker | category: kitchen | price: 45.00                    | loud

Product 2 has no brand: 'brand: ' || NULL is NULL, and concat_ws drops it, so the text has no empty “brand:” field.

Common Pitfalls

  • || silently turning the whole feature into NULL.

  • string_agg without ORDER BY, giving a different text on each run.

  • Including identifiers or label-revealing fields (e.g. “status: refunded”) in text features.

24

How do you bin categorical variables in SQL for an ML model?

Binning categorical variables groups many category values into fewer, more useful buckets before encoding them for a model. Common SQL approaches are: grouping rare categories into 'other' using counts from the training data (window count(*) OVER (PARTITION BY category) or a frequency table), mapping to business groups with CASE or a lookup table (cities to regions, product SKUs to departments), and target-based grouping, which orders categories by their training-set label rate and buckets them. Always learn the mapping on training data and store it in a table, so scoring uses exactly the same bins and unseen categories fall into a default bucket.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Approaches

Approach SQL When
Rare-category grouping Frequency table + CASE WHEN n < threshold THEN 'other' High cardinality, long tail
Business mapping CASE or join to a lookup table Known hierarchy
Target-rate buckets ntile over the per-category label rate Many categories with signal
Unseen values COALESCE(mapping.bin, 'other') Scoring new data

Code Example: Rare Categories Grouped from a Training Frequency Table

WITH train(order_id, country, churned) AS (
  VALUES (1, 'IN', 0), (2, 'IN', 1), (3, 'IN', 0), (4, 'US', 1),
         (5, 'US', 0), (6, 'US', 0), (7, 'NP', 1), (8, 'LK', 0)
),
category_map AS (                                     -- learned on training data only
  SELECT country,
         CASE WHEN count(*) >= 2 THEN country ELSE 'other' END AS country_bin
  FROM train
  GROUP BY country
),
scoring(order_id, country) AS (
  VALUES (101, 'IN'), (102, 'NP'), (103, 'BR')        -- BR never seen in training
)
SELECT s.order_id, s.country, COALESCE(m.country_bin, 'other') AS country_bin
FROM scoring s
LEFT JOIN category_map m USING (country)
ORDER BY s.order_id;
-- Expected:
-- 101 | IN | IN
-- 102 | NP | other
-- 103 | BR | other

Business Mapping with CASE

SELECT order_id,
       CASE WHEN country IN ('IN', 'NP', 'LK', 'BD') THEN 'south_asia'
            WHEN country IN ('US', 'CA') THEN 'north_america'
            ELSE 'rest_of_world' END AS region
FROM orders;

After binning, one-hot encode in SQL ((country_bin = 'IN')::int AS is_in) or in Python.

Common Pitfalls

  • Recomputing the frequency table on scoring data, which changes bins over time.

  • Target-rate binning computed on the same rows used for evaluation (leakage); use training folds only.

  • Forgetting a bucket for NULL and unseen values.

25

What SQL techniques can you use for data sampling?

SQL offers several sampling techniques, each suited to a different goal. Random sampling with ORDER BY random() LIMIT n is simple but sorts the whole table; TABLESAMPLE (BERNOULLI row-level or SYSTEM block-level) is much faster on large tables; deterministic hash sampling (hash(id) % 100 < 10) returns the same rows every run and keeps entities together; stratified sampling with row_number() OVER (PARTITION BY class ORDER BY random()) takes a fixed number or fraction per group; and time-based sampling selects recent periods. For ML, use stratified or hash-based sampling so class balance and train/test separation are controlled.

Read full answer

Applies toPostgreSQL 16 (TABLESAMPLE, hashtext); BigQuery/Snowflake equivalents noted

Techniques

Technique SQL Notes
Simple random ORDER BY random() LIMIT 1000 Exact size; slow on big tables
Bernoulli TABLESAMPLE BERNOULLI (10) ~10% of rows; REPEATABLE (42) for reproducibility
System TABLESAMPLE SYSTEM (10) Samples pages; fastest, but clustered
Hash-based abs(hashtext(user_id)) % 100 < 10 Deterministic, groups stay together
Stratified row_number() OVER (PARTITION BY label ORDER BY random()) Fixed count per class
Downsampling the majority Keep all positives, sample negatives Record the sampling rate to reweight

Code Example: Stratified Sample with a Fixed Seed

SELECT setseed(0.42);                    -- makes random() reproducible in this session

WITH labelled(id, label) AS (
  SELECT g, CASE WHEN g % 10 = 0 THEN 1 ELSE 0 END      -- 10% positives
  FROM generate_series(1, 1000) AS g
),
ranked AS (
  SELECT id, label, row_number() OVER (PARTITION BY label ORDER BY random()) AS rn
  FROM labelled
)
SELECT label, count(*) AS sampled
FROM ranked
WHERE rn <= 50                                          -- 50 per class
GROUP BY label
ORDER BY label;
-- Expected: 0 | 50
--           1 | 50

Bernoulli Sample of a Real Table

SELECT count(*) FROM events TABLESAMPLE BERNOULLI (5) REPEATABLE (42);   -- about 5% of rows

BigQuery uses TABLESAMPLE SYSTEM (5 PERCENT) and FARM_FINGERPRINT for hashing; Snowflake uses SAMPLE (5) and HASH().

Common Pitfalls

  • Sampling rows when the model works on users; sample users and keep all their rows.

  • Downsampling negatives without correcting predicted probabilities afterwards.

  • TABLESAMPLE SYSTEM on tables where rows are physically ordered by time, which biases the sample.

26

How do you run an ML model stored in a database like SQL Server?

Databases with built-in language integration can execute stored models next to the data. In SQL Server Machine Learning Services, a trained Python or R model is serialized (pickle for Python, serialize() for R) and stored as VARBINARY(MAX) in a table; sp_execute_external_script loads it inside the external runtime, receives the input query as a data frame, and returns predictions as a result set that can be inserted into a table. For faster scoring without Python at run time, SQL Server’s PREDICT T-SQL function runs ONNX models (Azure SQL Edge, Azure SQL Managed Instance) or models trained with the RevoScale libraries natively. Similar patterns exist in PostgreSQL (PL/Python) and Snowflake (Snowpark Python UDFs).

Read full answer

Applies toSQL Server 2019+ Machine Learning Services (Python); T-SQL

Steps

Step How
Enable external scripts sp_configure 'external scripts enabled', 1
Train In Python, then serialize the model
Store INSERT INTO models (name, version, model_blob)
Score sp_execute_external_script with @input_data_1 and the model as a parameter
Persist INSERT INTO predictions EXEC ...

Code Example (T-SQL)

CREATE TABLE dbo.models (
  model_name   varchar(50) NOT NULL,
  version      varchar(10) NOT NULL,
  model_blob   varbinary(max) NOT NULL,
  PRIMARY KEY (model_name, version)
);
-- model_blob is inserted from Python with pickle.dumps(model)

DECLARE @model varbinary(max) =
  (SELECT model_blob FROM dbo.models WHERE model_name = 'churn' AND version = 'v3');

INSERT INTO dbo.churn_predictions (customer_id, churn_probability)
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import pickle
model = pickle.loads(model_bytes)
features = InputDataSet[["tenure_months", "tickets_30d"]]
OutputDataSet = InputDataSet[["customer_id"]].copy()
OutputDataSet["churn_probability"] = model.predict_proba(features)[:, 1]
',
  @input_data_1 = N'SELECT customer_id, tenure_months, tickets_30d FROM dbo.customer_features',
  @params = N'@model_bytes varbinary(max)',
  @model_bytes = @model;

InputDataSet is a pandas DataFrame built from @input_data_1, and OutputDataSet becomes the result set inserted into the predictions table. The Python environment inside SQL Server must have the same library versions used for training.

Common Pitfalls

  • Version mismatches between the training environment and the database’s Python runtime (pickles may fail to load).

  • Unpickling models from untrusted sources (pickle can execute code); restrict who can write to the model table.

  • Heavy scoring workloads competing with transactional queries on the same server.

27

How do you write a SQL stored procedure that calls an ML scoring function?

A scoring stored procedure wraps three steps in one callable unit: select the rows to score, call a scoring function (a SQL function implementing the model, an in-database ML function, or an extension that calls Python), and write the results to a predictions table with the model version and timestamp. In PostgreSQL, write the model as a SQL or PL/pgSQL function and the batch job as a PROCEDURE invoked with CALL; procedures can commit in batches. In SQL Server the scoring call can be PREDICT (ONNX models) or sp_execute_external_script; in Snowflake, a Snowpark Python UDF.

Read full answer

Applies toPostgreSQL 16 (PL/pgSQL)

Code Example

-- Scoring function: logistic regression with stored coefficients
CREATE TABLE model_coefficients (
  model_version text PRIMARY KEY,
  intercept numeric, b_tenure numeric, b_tickets numeric, active boolean
);
INSERT INTO model_coefficients VALUES ('v3', -1.0, -0.05, 0.6, true);

CREATE OR REPLACE FUNCTION churn_score(tenure_months numeric, tickets_30d numeric, OUT score numeric, OUT version text)
LANGUAGE sql STABLE AS $$
  SELECT 1 / (1 + exp(-(intercept + b_tenure * tenure_months + b_tickets * tickets_30d))), model_version
  FROM model_coefficients
  WHERE active
$$;

CREATE TABLE customer_features (customer_id text PRIMARY KEY, tenure_months int, tickets_30d int);
INSERT INTO customer_features VALUES ('c1', 20, 0), ('c2', 2, 3);

CREATE TABLE churn_predictions (
  customer_id text, score numeric, model_version text, scored_at timestamptz
);

-- Procedure: score all customers and log results
CREATE OR REPLACE PROCEDURE score_customers()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO churn_predictions (customer_id, score, model_version, scored_at)
  SELECT f.customer_id, s.score, s.version, now()
  FROM customer_features f
  CROSS JOIN LATERAL churn_score(f.tenure_months, f.tickets_30d) AS s;
  RAISE NOTICE 'scored % customers', (SELECT count(*) FROM customer_features);
END;
$$;

CALL score_customers();

SELECT customer_id, round(score, 3) AS score, model_version
FROM churn_predictions ORDER BY customer_id;
-- Expected:
-- c1 | 0.119 | v3
-- c2 | 0.668 | v3

For c1 the linear score is −1.0 − 0.05 × 20 = −2.0, and 1 / (1 + e²) ≈ 0.119; for c2 it is −1.0 − 0.05 × 2 + 0.6 × 3 = 0.7, giving ≈ 0.668.

Common Pitfalls

  • Scoring without recording which model version produced each prediction.

  • Several active coefficient rows (the function would return multiple rows); enforce one with a partial unique index.

  • Very large batches in one transaction; procedures can COMMIT between chunks.

28

Can you update an ML model directly from SQL, and how?

Yes, in several ways. In warehouses with built-in ML, a SQL statement retrains the model: BigQuery ML’s CREATE OR REPLACE MODEL rebuilds it on current data (some model types support warm_start), and Snowflake and Redshift ML offer similar commands. For models stored as parameters in tables (logistic regression coefficients, naive Bayes counts, item similarity matrices), SQL can update the parameters with UPDATE or MERGE, for example by adding new counts incrementally. A SQL scheduler or trigger can also start an external training job. Updates should still go through evaluation and a registry before replacing the production model.

Read full answer

Applies toBigQuery ML; PostgreSQL 16 for parameter tables

Options

Approach Example
Retrain in warehouse CREATE OR REPLACE MODEL ... AS SELECT ...
Warm start BigQuery ML OPTIONS (warm_start = TRUE) for supported models
Incremental count-based models MERGE new counts into a naive Bayes table
Parameter swap Insert new coefficients with a new version, flip the active version
Trigger external training Scheduled job reads a “retrain requested” table

Code Example: Incrementally Updating a Naive Bayes Count Table (PostgreSQL)

CREATE TABLE nb_counts (label text, term text, n bigint, PRIMARY KEY (label, term));
INSERT INTO nb_counts VALUES ('spam', 'free', 30), ('ham', 'free', 5), ('ham', 'meeting', 40);

-- New labelled messages arrive as (label, term) tokens
WITH new_tokens(label, term) AS (
  VALUES ('spam', 'free'), ('spam', 'free'), ('spam', 'winner'), ('ham', 'meeting')
)
INSERT INTO nb_counts (label, term, n)
SELECT label, term, count(*) FROM new_tokens GROUP BY label, term
ON CONFLICT (label, term) DO UPDATE SET n = nb_counts.n + EXCLUDED.n;

SELECT * FROM nb_counts ORDER BY label, term;
-- Expected:
-- ham  | free    | 5
-- ham  | meeting | 41
-- spam | free    | 32
-- spam | winner  | 1

The model is “updated” without retraining from scratch, because naive Bayes depends only on counts; probabilities are computed at prediction time with Laplace smoothing.

Retraining in BigQuery ML

CREATE OR REPLACE MODEL `proj.ml.demand_model`
OPTIONS (model_type = 'boosted_tree_regressor', input_label_cols = ['units']) AS
SELECT * EXCEPT (sale_date) FROM `proj.ml.demand_training`
WHERE sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR);

Common Pitfalls

  • Replacing the production model in place with no evaluation step or rollback.

  • Incremental updates that never forget old patterns; add decay or periodic full retrains.

29

What role does SQL play in ML model deployment?

SQL plays several roles in deploying ML models. It builds the features used at prediction time (ideally the same SQL that built the training data), runs batch scoring by applying models inside the warehouse (BigQuery ML ML.PREDICT, Snowflake model methods, SQL Server PREDICT) or by feeding a scoring job, stores predictions in tables that applications and dashboards read, records model metadata in a registry table, and powers monitoring queries that join predictions to outcomes for drift and accuracy. Real-time serving usually happens in an API, but its features and logs still flow through SQL.

Read full answer

Applies toPostgreSQL 16; BigQuery ML for in-warehouse prediction

Deployment Patterns

Pattern SQL’s role
Batch scoring in warehouse INSERT INTO predictions SELECT ... FROM ML.PREDICT(...)
Batch scoring in Python SQL extracts features; results written back with COPY or a connector
Real-time API SQL builds online features (synced to a feature store); predictions logged to SQL
Embedded rules Simple models (logistic regression) expressed as SQL formulas
Monitoring Drift and performance queries on prediction logs

Code Example: Scoring a Logistic Model in Plain SQL

A logistic regression exported as coefficients can be scored anywhere SQL runs:

WITH coefficients(intercept, b_tenure, b_tickets, b_monthly) AS (
  VALUES (-1.2, -0.05, 0.40, 0.90)
),
customers(customer_id, tenure_months, tickets_30d, is_monthly) AS (
  VALUES ('c1', 24, 0, 0), ('c2', 3, 2, 1), ('c3', 12, 1, 1)
)
SELECT customer_id,
       round(1 / (1 + exp(-(k.intercept + k.b_tenure * tenure_months
                            + k.b_tickets * tickets_30d + k.b_monthly * is_monthly))), 3) AS churn_probability
FROM customers
CROSS JOIN coefficients k
ORDER BY churn_probability DESC;
-- Expected:
-- c2 | 0.587
-- c3 | 0.378
-- c1 | 0.083

For c2 the linear score is −1.2 − 0.05 × 3 + 0.40 × 2 + 0.90 = 0.35, and 1 / (1 + e^−0.35) ≈ 0.587; for c1 it is −1.2 − 0.05 × 24 = −2.4, giving ≈ 0.083.

BigQuery ML Batch Scoring

INSERT INTO `proj.ml.predictions` (customer_id, churn_probability, model_version, scored_at)
SELECT customer_id,
       (SELECT prob FROM UNNEST(predicted_churned_probs) WHERE label = 1) AS churn_probability,
       'v7', CURRENT_TIMESTAMP()
FROM ML.PREDICT(MODEL `proj.ml.churn_model`, (SELECT * FROM `proj.ml.scoring_features`));

Common Pitfalls

  • Re-implementing feature logic differently in the serving path (training-serving skew).

  • Predictions stored without model version and timestamp.

30

Why is in-database analytics significant for machine learning?

In-database analytics runs analysis and ML where the data already live, in the database or warehouse, instead of exporting data to separate tools. Its significance is less data movement (faster, cheaper, and safer for sensitive data), scale from the warehouse’s distributed engine, governance (access controls, auditing and lineage stay in one place), simpler pipelines (features, training and scoring as SQL that analysts can maintain), and fresher predictions because scoring runs next to new data. Examples: BigQuery ML, Snowflake ML functions and Snowpark, Redshift ML, SQL Server Machine Learning Services, and PostgreSQL extensions such as pgvector (vector similarity) and PostgresML.

Read full answer

Applies toBigQuery ML, Snowflake, PostgreSQL 16 + pgvector (general overview)

Benefits and Limits

Benefits Limits
No large exports Limited algorithm choice and tuning
Uses warehouse compute at scale Harder custom preprocessing and debugging
Central security and governance Vendor lock-in
Analysts can build models in SQL Deep learning and advanced pipelines still need Python
Batch scoring next to fresh data Cost can grow quickly on pay-per-query engines

Examples

Platform Capability
BigQuery ML CREATE MODEL (linear/logistic, boosted trees, k-means, ARIMA_PLUS, imported ONNX/TensorFlow), ML.PREDICT
Snowflake ML functions (forecasting, anomaly detection), Snowpark Python UDFs, model registry
PostgreSQL + pgvector Store embeddings, nearest-neighbour search with indexes
SQL Server sp_execute_external_script (Python/R), PREDICT for ONNX models

Code Example: Vector Similarity with pgvector

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE items (id int PRIMARY KEY, title text, embedding vector(3));
INSERT INTO items VALUES
  (1, 'running shoes', '[0.9, 0.1, 0.0]'),
  (2, 'trail shoes',   '[0.8, 0.2, 0.1]'),
  (3, 'coffee maker',  '[0.0, 0.1, 0.9]');

-- Nearest items to a query embedding by cosine distance (<=>)
SELECT id, title, round((embedding <=> '[0.85, 0.15, 0.05]')::numeric, 3) AS cosine_distance
FROM items
ORDER BY embedding <=> '[0.85, 0.15, 0.05]'
LIMIT 2;
-- Expected: ids 1 and 2 (the two shoe items) with small distances; coffee maker excluded

Real embeddings have hundreds of dimensions and come from a model; an HNSW index (CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)) makes search fast.

When to Move Data to Python Instead

Custom deep learning, complex feature pipelines, extensive experimentation and models the database cannot express.

Common Pitfalls

  • Assuming in-database models match the flexibility of Python libraries.

  • Ignoring compute costs of training on full tables.

31

What are recursive SQL queries, and how do they prepare hierarchical data?

A recursive query is a CTE written with WITH RECURSIVE that refers to itself. It has an anchor part (the starting rows, such as the top of a hierarchy) and a recursive part joined to the previous step’s results, combined with UNION or UNION ALL; it repeats until no new rows appear. It is the standard way to walk hierarchies and graphs stored as parent-child tables: org charts, product category trees, bills of materials, account ownership chains. For ML, it produces features such as depth, full path, top-level ancestor, number of descendants and aggregates rolled up the tree, which hierarchical models and hierarchical classification need.

Read full answer

Applies toPostgreSQL 16 (standard SQL; SQL Server omits the RECURSIVE keyword)

Typical Outputs

Output Use in ML
Depth / level Feature or grouping level
Path ('Electronics > Phones > Android') Hierarchical labels, text features
Root ancestor Top-level category for pooling
Descendant counts / rolled-up sums Size of a manager’s team, category sales
Parent at each level Columns for hierarchical (multi-level) models

Code Example: Category Tree

WITH RECURSIVE categories(id, parent_id, name) AS (
  VALUES (1, NULL::int, 'Electronics'),
         (2, 1, 'Phones'),
         (3, 2, 'Android'),
         (4, 2, 'iPhone'),
         (5, 1, 'Laptops'),
         (6, NULL::int, 'Books')
),
tree AS (
  SELECT id, parent_id, name, 1 AS depth, name::text AS path, id AS root_id
  FROM categories
  WHERE parent_id IS NULL                         -- anchor: top-level categories
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || ' > ' || c.name, t.root_id
  FROM categories c
  JOIN tree t ON c.parent_id = t.id               -- recursive step: children of the previous level
)
SELECT id, name, depth, path, root_id
FROM tree
ORDER BY path;
-- Expected:
-- 6 | Books       | 1 | Books                            | 6
-- 1 | Electronics | 1 | Electronics                      | 1
-- 5 | Laptops     | 2 | Electronics > Laptops            | 1
-- 2 | Phones      | 2 | Electronics > Phones             | 1
-- 3 | Android     | 3 | Electronics > Phones > Android   | 1
-- 4 | iPhone      | 3 | Electronics > Phones > iPhone    | 1

Joining this result to product sales gives features like “sales of the top-level category” or labels at each level of the hierarchy.

Common Pitfalls

  • Cycles in the data (a node that is its own ancestor) causing infinite recursion; use UNION, a depth limit, or PostgreSQL 14’s CYCLE clause.

  • Type mismatches between the anchor and recursive parts (cast text columns explicitly, as with name::text).

  • Running deep recursion over huge tables without indexes on parent_id.

32

How can you generate graph-based features from SQL data?

Graph features from SQL treat relationships stored in tables as edges between entities: customers and the devices, cards or addresses they share; users who follow or message each other; products bought together. With an edge table (src, dst), SQL can compute degree (number of connections), shared-neighbour counts (how many devices two accounts share), neighbour aggregates (average fraud rate of a user’s connections), two-hop reach via self-joins, and connected components or paths with WITH RECURSIVE. These features are powerful for fraud detection, recommendations and churn. For PageRank, community detection or graph embeddings at scale, export edges to a graph library (NetworkX, igraph, a graph database) or use PostgreSQL’s Apache AGE extension.

Read full answer

Applies toPostgreSQL 16

Feature Ideas

Feature SQL pattern
Degree count(*) GROUP BY src
Shared entities Self-join on the shared attribute
Neighbour label rate Join edges to labels, avg(label) per node
Two-hop neighbours Edge table joined to itself
Connected component Recursive CTE propagating the minimum node ID

Code Example: Accounts Linked by Shared Devices

WITH logins(account_id, device_id) AS (
  VALUES ('a1', 'd1'), ('a2', 'd1'), ('a2', 'd2'), ('a3', 'd2'),
         ('a4', 'd3'), ('a5', 'd3'), ('a6', 'd4')
),
labels(account_id, is_fraud) AS (
  VALUES ('a1', 1), ('a2', 0), ('a3', 0), ('a4', 1), ('a5', 0), ('a6', 0)
),
edges AS (                                     -- accounts connected through a shared device
  SELECT DISTINCT l1.account_id AS src, l2.account_id AS dst
  FROM logins l1
  JOIN logins l2 ON l1.device_id = l2.device_id AND l1.account_id <> l2.account_id
)
SELECT a.account_id,
       count(e.dst)                AS linked_accounts,
       COALESCE(sum(lb.is_fraud), 0) AS linked_fraud_accounts
FROM labels a
LEFT JOIN edges e ON e.src = a.account_id
LEFT JOIN labels lb ON lb.account_id = e.dst
GROUP BY a.account_id
ORDER BY a.account_id;
-- Expected:
-- a1 | 1 | 0
-- a2 | 2 | 1
-- a3 | 1 | 0
-- a4 | 1 | 0
-- a5 | 1 | 1
-- a6 | 0 | 0

Account a2 shares devices with a1 (fraud) and a3, so it gets one linked fraud account. When labels come from the training period only, this “neighbour fraud” feature does not leak.

Connected Components with a Recursive CTE

WITH RECURSIVE reach(root, node) AS (
  SELECT src, src FROM edges
  UNION
  SELECT r.root, e.dst FROM reach r JOIN edges e ON e.src = r.node
)
SELECT node, min(root) AS component_id FROM reach GROUP BY node;

UNION (not UNION ALL) removes duplicates, which stops the recursion on cycles. It is fine for small graphs; for millions of edges use a graph tool.

Common Pitfalls

  • Hub nodes (a public Wi-Fi device) linking thousands of accounts; cap degree or exclude hubs.

  • Using labels discovered after the prediction time in neighbour features.

33

What are CTEs, and how can they help with feature generation?

A Common Table Expression (CTE) is a named temporary result defined with WITH name AS (SELECT ...) and used by the query that follows. CTEs break a complex query into readable, testable steps, and each step can build on earlier ones. For feature generation this is ideal: one CTE defines the label time per entity, others compute aggregates from different source tables relative to that time, and the final SELECT joins them into one training row per entity. WITH RECURSIVE handles hierarchical data. In PostgreSQL 12+, simple CTEs are inlined by the planner, so they usually cost nothing extra; MATERIALIZED forces a one-time computation.

Read full answer

Applies toPostgreSQL 16 (standard SQL)

Why CTEs Help

Benefit Example
Readability One CTE per feature group
Reuse Reference the same CTE several times
Point-in-time logic A cutoff CTE used by every feature CTE
Testing Run each CTE alone while developing
Recursion Org charts, category trees

Code Example: Training Rows from Several Sources

WITH customers(customer_id, cutoff) AS (
  VALUES ('c1', DATE '2026-10-01'), ('c2', DATE '2026-10-01')
),
orders(customer_id, order_date, amount) AS (
  VALUES ('c1', DATE '2026-09-10', 40.0), ('c1', DATE '2026-09-25', 60.0),
         ('c1', DATE '2026-10-05', 99.0),                       -- after the cutoff: excluded
         ('c2', DATE '2026-06-01', 20.0)
),
tickets(customer_id, opened_on) AS (
  VALUES ('c2', DATE '2026-09-20'), ('c2', DATE '2026-09-28')
),
order_features AS (
  SELECT c.customer_id,
         count(o.order_date) FILTER (WHERE o.order_date >= c.cutoff - 30) AS orders_30d,
         COALESCE(sum(o.amount), 0)                                      AS spend_total,
         c.cutoff - max(o.order_date)                                    AS days_since_order
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.order_date < c.cutoff
  GROUP BY c.customer_id, c.cutoff
),
ticket_features AS (
  SELECT c.customer_id, count(t.opened_on) AS tickets_30d
  FROM customers c
  LEFT JOIN tickets t ON t.customer_id = c.customer_id
                     AND t.opened_on < c.cutoff AND t.opened_on >= c.cutoff - 30
  GROUP BY c.customer_id
)
SELECT o.customer_id, o.orders_30d, o.spend_total, o.days_since_order, t.tickets_30d
FROM order_features o
JOIN ticket_features t USING (customer_id)
ORDER BY o.customer_id;
-- Expected:
-- c1 | 2 | 100.0 | 6   | 0
-- c2 | 0 | 20.0  | 122 | 2

Aggregating each source in its own CTE before joining avoids the row multiplication you get from joining orders and tickets directly. Subtracting an integer from a date gives a date; subtracting two dates gives days.

Common Pitfalls

  • Joining several one-to-many tables before aggregating (duplicated rows inflate sums).

  • Forgetting the cutoff in one CTE, which leaks future data.

34

How do you write a SQL query that extracts time series features?

Time series features in SQL describe each entity’s history up to a cut-off: lags (LAG), differences and growth rates, rolling statistics (mean, max, standard deviation over ROWS or RANGE frames), counts in time windows, recency (time since the last event), calendar features (day of week, month) and trend (regr_slope). A good query first builds a complete daily (or hourly) series per entity with a calendar from generate_series, so missing days become zeros instead of gaps, then computes window features, and finally keeps only rows at the label time.

Read full answer

Applies toPostgreSQL 16

Feature Types

Feature SQL
Lag lag(sales, 1) OVER w
Change sales - lag(sales, 7) OVER w
Rolling mean avg(sales) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
Rolling std stddev_samp(sales) OVER (...)
Trend regr_slope(sales, day_index) over a window
Calendar extract(isodow FROM day)
Recency day - max(day) FILTER (WHERE sales > 0) OVER (...)

Code Example: Complete Series and Window Features

WITH sales(store, sale_date, units) AS (
  VALUES ('s1', DATE '2026-11-01', 10), ('s1', DATE '2026-11-02', 12),
         ('s1', DATE '2026-11-04', 9),  ('s1', DATE '2026-11-05', 15)
),
calendar AS (
  SELECT 's1' AS store, d::date AS day
  FROM generate_series(DATE '2026-11-01', DATE '2026-11-05', interval '1 day') AS d
),
daily AS (
  SELECT c.store, c.day, COALESCE(s.units, 0) AS units
  FROM calendar c
  LEFT JOIN sales s ON s.store = c.store AND s.sale_date = c.day
)
SELECT store, day, units,
       lag(units) OVER w                                                   AS units_lag1,
       round(avg(units) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma3,
       max(units) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS max3,
       extract(isodow FROM day)::int                                       AS weekday
FROM daily
WINDOW w AS (PARTITION BY store ORDER BY day)
ORDER BY day;
-- Expected:
-- s1 | 2026-11-01 | 10 | NULL | 10.00 | 10 | 7
-- s1 | 2026-11-02 | 12 | 10   | 11.00 | 12 | 1
-- s1 | 2026-11-03 |  0 | 12   |  7.33 | 12 | 2
-- s1 | 2026-11-04 |  9 | 0    |  7.00 | 12 | 3
-- s1 | 2026-11-05 | 15 | 9    |  8.00 | 15 | 4

The calendar fills 11-03 with 0, so ROWS frames truly cover three days. For forecasting the next day, shift the features by one row (or use frames ending at 1 PRECEDING) so a row never uses its own target.

Common Pitfalls

  • Rolling frames over gappy data, which mix different time spans.

  • Features using the current day’s value when predicting that day.

  • Early rows with partial windows; add a count(*) OVER (...) column or drop them.

35

How can you use regular expressions in SQL for NLP tasks?

Regular expressions in SQL let you clean and extract structure from text before it reaches a model. In PostgreSQL, ~ and ~* test matches (case-sensitive and case-insensitive), regexp_replace normalizes text (remove URLs, HTML, punctuation, extra whitespace), regexp_matches / regexp_substr (PostgreSQL 15+) extract entities such as emails, hashtags, prices or order numbers, regexp_split_to_table tokenizes, and regexp_count (15+) counts patterns, which makes simple features. Other dialects use REGEXP_LIKE, REGEXP_REPLACE and REGEXP_EXTRACT (BigQuery, Snowflake, MySQL 8).

Read full answer

Applies toPostgreSQL 16 (dialect differences noted)

Common NLP Uses

Task PostgreSQL
Lowercase and strip punctuation regexp_replace(lower(t), '[^a-z0-9s]', ' ', 'g')
Remove URLs regexp_replace(t, 'https?://S+', ' ', 'g')
Collapse whitespace regexp_replace(t, 's+', ' ', 'g')
Extract hashtags regexp_matches(t, '#(w+)', 'g')
Count exclamation marks regexp_count(t, '!')
Flag a pattern t ~* 'mrefundM' (word boundaries m, M)
Tokenize regexp_split_to_table(t, 's+')

Code Example: Cleaning and Feature Extraction

WITH reviews(review_id, body) AS (
  VALUES (1, 'LOVE it!!! See https://shop.example/p/1 #happy #fast'),
         (2, 'Broken on arrival, want a REFUND.'),
         (3, 'ok   product, price was $19.99')
)
SELECT review_id,
       trim(regexp_replace(regexp_replace(lower(regexp_replace(body, 'https?://\S+', ' ', 'g')),
                                          '[^a-z0-9\s]', ' ', 'g'), '\s+', ' ', 'g')) AS clean_text,
       regexp_count(body, '!')                                        AS exclamations,
       (body ~* '\mrefund\M')::int                                    AS mentions_refund,
       (SELECT count(*) FROM regexp_matches(body, '#(\w+)', 'g'))     AS hashtag_count,
       substring(body FROM '\$([0-9]+\.[0-9]{2})')::numeric           AS price
FROM reviews
ORDER BY review_id;
-- Expected:
-- 1 | love it see happy fast              | 3 | 0 | 2 | NULL
-- 2 | broken on arrival want a refund     | 0 | 1 | 0 | NULL
-- 3 | ok product price was 19 99          | 0 | 0 | 0 | 19.99

substring(... FROM pattern) returns the first capture group, here the price without the dollar sign.

When to Move to Python

Regex covers cleaning and simple features. Tokenization with linguistic rules, lemmatization, embeddings and named-entity recognition belong in Python (spaCy, Hugging Face), or use PostgreSQL full-text search (to_tsvector) for stemming.

Common Pitfalls

  • Forgetting the 'g' flag, so only the first match is replaced.

  • Different regex flavours between databases (\m word boundaries are PostgreSQL-specific; others use \b).

  • Running heavy regexes over huge tables without filtering first.

36

How do you replace missing values with the column mean in SQL?

Mean imputation in SQL uses COALESCE(column, mean), where the mean comes from a window function avg(column) OVER () (computed on non-NULL values, since avg ignores NULLs) or from a stored parameter. You can impute per group with avg(column) OVER (PARTITION BY group), and it is good practice to add a missing-indicator column so the model can learn whether missingness itself matters. For an ML pipeline, compute the mean on the training data only and store it, then apply the same value at scoring time to avoid leakage.

Read full answer

Applies toPostgreSQL 16 (portable window functions)

Code Example: Select with Imputation

WITH patients(patient_id, ward, age, cholesterol) AS (
  VALUES (1, 'A', 50, 200.0),
         (2, 'A', 60, NULL),
         (3, 'A', 40, 220.0),
         (4, 'B', 70, 180.0),
         (5, 'B', 65, NULL)
)
SELECT patient_id, ward,
       cholesterol,
       COALESCE(cholesterol, round(avg(cholesterol) OVER (), 1))                  AS chol_global_mean,
       COALESCE(cholesterol, round(avg(cholesterol) OVER (PARTITION BY ward), 1)) AS chol_ward_mean,
       (cholesterol IS NULL)::int                                                 AS chol_missing
FROM patients
ORDER BY patient_id;
-- Expected:
-- 1 | A | 200.0 | 200.0 | 200.0 | 0
-- 2 | A | NULL  | 200.0 | 210.0 | 1
-- 3 | A | 220.0 | 220.0 | 220.0 | 0
-- 4 | B | 180.0 | 180.0 | 180.0 | 0
-- 5 | B | NULL  | 200.0 | 180.0 | 1

The global mean is (200 + 220 + 180) / 3 = 200; ward A’s is 210 and ward B’s is 180.

Updating a Table In Place

UPDATE patients_train
SET cholesterol = sub.mean_chol
FROM (SELECT avg(cholesterol) AS mean_chol FROM patients_train) sub
WHERE patients_train.cholesterol IS NULL;

Prefer keeping raw data unchanged and imputing in a view or feature table, so the original missingness is never lost.

Common Pitfalls

  • Computing the mean over train and test together.

  • Mean imputation shrinking variance and weakening correlations; consider median for skewed data or model-based imputation.

  • Using avg on an integer column and expecting decimals in some engines (PostgreSQL returns numeric; others may truncate).

37

How do you min-max normalize a column to the 0-1 range in SQL?

Min-max normalization rescales a column to [0, 1] with (x − min) / (max − min). In SQL, window functions compute the minimum and maximum without a separate subquery: min(x) OVER () and max(x) OVER (), or per group with PARTITION BY. Guard against division by zero with NULLIF(max − min, 0) when all values are equal, cast to numeric or double precision to avoid integer division, and for scoring new data reuse the min and max from the training data, storing them in a parameters table, so the transformation is identical.

Read full answer

Applies toPostgreSQL 16 (portable window functions)

Code Example

WITH sales(store, region, revenue) AS (
  VALUES ('s1', 'north', 100), ('s2', 'north', 300), ('s3', 'north', 200),
         ('s4', 'south', 50),  ('s5', 'south', 50)
)
SELECT store, region, revenue,
       round((revenue - min(revenue) OVER ())::numeric
             / NULLIF(max(revenue) OVER () - min(revenue) OVER (), 0), 3) AS revenue_scaled,
       round((revenue - min(revenue) OVER w)::numeric
             / NULLIF(max(revenue) OVER w - min(revenue) OVER w, 0), 3) AS scaled_in_region
FROM sales
WINDOW w AS (PARTITION BY region)
ORDER BY store;
-- Expected:
-- s1 | north | 100 | 0.200 | 0.000
-- s2 | north | 300 | 1.000 | 1.000
-- s3 | north | 200 | 0.600 | 0.500
-- s4 | south |  50 | 0.000 | NULL
-- s5 | south |  50 | 0.000 | NULL

The south region has identical values, so NULLIF returns NULL instead of an error; replace with 0 using COALESCE if your model needs a value.

Using Training Parameters at Scoring Time

CREATE TABLE scaling_params AS
SELECT 'revenue' AS feature, min(revenue) AS min_val, max(revenue) AS max_val
FROM training_sales;

SELECT s.store,
       LEAST(GREATEST((s.revenue - p.min_val)::numeric / NULLIF(p.max_val - p.min_val, 0), 0), 1) AS revenue_scaled
FROM new_sales s
CROSS JOIN scaling_params p
WHERE p.feature = 'revenue';

Clipping with LEAST/GREATEST keeps values outside the training range inside [0, 1].

Common Pitfalls

  • Integer division producing only 0 and 1.

  • Outliers squashing everyone else near 0; consider robust scaling or log transforms.

  • Computing min and max on the combined train and test data (leakage).

38

How do you create a rolling time-window count feature in SQL?

A rolling count feature, such as “number of transactions by this customer in the previous 7 days”, is computed with a window function over time. In PostgreSQL, a RANGE frame with an interval does it directly: count(*) OVER (PARTITION BY customer_id ORDER BY event_at RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW). Use ROWS frames only when you want a fixed number of rows, not a time span. To avoid leakage, exclude the current event when the feature describes history before the event, and for label-time features join on events strictly before the cut-off time.

Read full answer

Applies toPostgreSQL 11+ (RANGE with interval offsets)

ROWS vs RANGE

Frame Meaning Example
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW Last 7 rows, regardless of dates Last 7 orders
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW All rows within 7 days before Orders in the past week

Code Example

WITH txns(customer_id, txn_at) AS (
  VALUES ('c1', TIMESTAMP '2026-11-01 10:00'),
         ('c1', TIMESTAMP '2026-11-03 09:00'),
         ('c1', TIMESTAMP '2026-11-07 12:00'),
         ('c1', TIMESTAMP '2026-11-12 08:00'),
         ('c2', TIMESTAMP '2026-11-02 15:00')
)
SELECT customer_id, txn_at,
       count(*) OVER w7 AS txns_last_7d_incl_current,
       count(*) OVER w7 - 1 AS txns_prior_7d
FROM txns
WINDOW w7 AS (PARTITION BY customer_id ORDER BY txn_at
              RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)
ORDER BY customer_id, txn_at;
-- Expected:
-- c1 | 2026-11-01 10:00 | 1 | 0
-- c1 | 2026-11-03 09:00 | 2 | 1
-- c1 | 2026-11-07 12:00 | 3 | 2
-- c1 | 2026-11-12 08:00 | 2 | 1
-- c2 | 2026-11-02 15:00 | 1 | 0

On 11-12 08:00 the window starts at 11-05 08:00, so only the 11-07 transaction and the current one count. If several rows share the exact same timestamp, CURRENT ROW in a RANGE frame includes all of them; subtracting 1 then still counts the others as “prior”.

For Engines Without RANGE Intervals

Use a self-join: count rows with t2.txn_at >= t1.txn_at - INTERVAL '7 days' AND t2.txn_at < t1.txn_at, or a calendar table with daily counts and a ROWS BETWEEN 6 PRECEDING sum.

Common Pitfalls

  • Using ROWS when dates have gaps, which silently changes the time span.

  • Including the current (or future) event in a feature meant to predict it.

  • Time zone mismatches between events and the label time.

39

How do you write a SQL function that bins continuous variables?

Binning (discretization) turns a continuous variable into categories, either with fixed edges chosen by domain knowledge (age bands), equal-width bins over the range, or equal-frequency (quantile) bins with about the same number of rows each. In SQL, fixed edges are a CASE expression or a lookup table; equal-width bins use width_bucket(x, min, max, n); quantile bins use ntile(n) OVER (ORDER BY x). Wrapping the logic in a SQL function keeps the definition consistent across training and scoring queries.

Read full answer

Applies toPostgreSQL 16

Methods

Method SQL Use when
Fixed edges CASE or width_bucket(x, ARRAY[...]) Business-defined bands
Equal width width_bucket(x, lo, hi, n) Roughly uniform data
Equal frequency ntile(n) OVER (ORDER BY x) Skewed data

For scoring, store the edges learned on training data and reuse them; recomputing quantiles on new data changes the bins.

Code Example: Function with Fixed Edges

CREATE OR REPLACE FUNCTION age_band(age numeric)
RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
  SELECT CASE
           WHEN age IS NULL THEN 'unknown'
           WHEN age < 18 THEN 'under_18'
           WHEN age < 30 THEN '18_29'
           WHEN age < 45 THEN '30_44'
           WHEN age < 65 THEN '45_64'
           ELSE '65_plus'
         END
$$;

WITH customers(customer_id, age, income) AS (
  VALUES (1, 16, 0), (2, 25, 30000), (3, 44, 52000), (4, 45, 61000), (5, 70, 40000), (6, NULL, 45000)
)
SELECT customer_id,
       age_band(age) AS age_band,
       width_bucket(income, ARRAY[0, 25000, 50000]) AS income_bin,    -- edges stored from training
       ntile(3) OVER (ORDER BY income) AS income_tercile
FROM customers
ORDER BY customer_id;
-- Expected:
-- 1 | under_18 | 1 | 1
-- 2 | 18_29    | 2 | 1
-- 3 | 30_44    | 3 | 3
-- 4 | 45_64    | 3 | 3
-- 5 | 65_plus  | 2 | 2
-- 6 | unknown  | 2 | 2

width_bucket(x, ARRAY[0, 25000, 50000]) returns 1 for 0 ≤ x < 25000, 2 for 25000 ≤ x < 50000 and 3 for x ≥ 50000. The tercile ordering is 0, 30000, 40000, 45000, 52000, 61000, giving two rows per tercile.

Common Pitfalls

  • Boundary values landing in unexpected bins; document whether edges are inclusive.

  • Recomputing quantile bins on scoring data.

  • Forgetting a bin for NULLs.

40

How do you compute TF-IDF scores for text data in SQL?

TF-IDF in SQL is built from a table of tokens per document. First split text into terms (PostgreSQL: regexp_split_to_table(lower(text), '\W+')), then compute term frequency (count of the term in the document divided by the document’s total terms), document frequency (number of documents containing the term), and inverse document frequency idf = ln(N / df), where N is the number of documents. The score is tf × idf: high for terms frequent in one document but rare across the collection. Variants add smoothing such as ln((1 + N) / (1 + df)) + 1, which is scikit-learn’s default.

Read full answer

Applies toPostgreSQL 16

Code Example

WITH docs(doc_id, body) AS (
  VALUES (1, 'the cat sat on the mat'),
         (2, 'the dog sat'),
         (3, 'cat and dog')
),
tokens AS (
  SELECT doc_id, term
  FROM docs, regexp_split_to_table(lower(body), '\W+') AS term
  WHERE term <> ''
),
tf AS (
  SELECT doc_id, term,
         count(*)::numeric / sum(count(*)) OVER (PARTITION BY doc_id) AS tf
  FROM tokens
  GROUP BY doc_id, term
),
df AS (
  SELECT term, count(DISTINCT doc_id) AS df FROM tokens GROUP BY term
),
n AS (
  SELECT count(*) AS n_docs FROM docs
)
SELECT tf.doc_id, tf.term,
       round(tf.tf, 3)                                AS tf,
       round(ln(n.n_docs::numeric / df.df), 3)        AS idf,
       round(tf.tf * ln(n.n_docs::numeric / df.df), 3) AS tf_idf
FROM tf
JOIN df USING (term)
CROSS JOIN n
WHERE tf.doc_id = 1
ORDER BY tf_idf DESC, term;
-- Expected for document 1 (6 tokens; N = 3):
-- mat | 0.167 | 1.099 | 0.183
-- on  | 0.167 | 1.099 | 0.183
-- the | 0.333 | 0.405 | 0.135
-- cat | 0.167 | 0.405 | 0.068
-- sat | 0.167 | 0.405 | 0.068

“the” appears twice in document 1 but also in document 2, so its IDF is ln(3/2) ≈ 0.405; “mat” and “on” appear only in document 1, so IDF = ln(3) ≈ 1.099. (Output columns: term, tf, idf, tf_idf after doc_id 1.)

Production Notes

  • Remove stop words with a join to a stop-word table, and stem with PostgreSQL’s to_tsvector.

  • Store results in a sparse table (doc_id, term, tf_idf); normalize each document vector to unit length before cosine similarity.

  • For large corpora, compute TF-IDF in Python (scikit-learn) or use embeddings (pgvector).

Common Pitfalls

  • Integer division in tf or N / df (cast to numeric).

  • Counting documents per term without DISTINCT.

  • Empty tokens from leading or trailing punctuation.

41

How do you handle very large datasets in SQL for ML?

Handling very large datasets in SQL for ML means doing as much work as possible inside the database, where the data live, and moving only compact results. Push down filtering, joins and aggregation into SQL; keep tables partitioned and clustered on the columns you filter by; build pre-aggregated feature tables instead of scanning raw events each time; use sampling (TABLESAMPLE or hash-based deterministic samples) for exploration and prototyping; export training data in columnar formats (Parquet) rather than through row-by-row cursors; and consider in-database ML (BigQuery ML, Snowflake) for models that fit those engines.

Read full answer

Applies toPostgreSQL 16; BigQuery/Snowflake for warehouse-scale data

Techniques

Technique Why
Select only needed columns Columnar warehouses charge by bytes scanned
Partition pruning and clustering Read only relevant data
Pre-aggregated feature tables Avoid repeated full scans
Deterministic hash sampling Reproducible, keeps all rows of a user together
Approximate functions (APPROX_COUNT_DISTINCT, APPROX_QUANTILES) Much faster on huge tables
Export to Parquet / use connectors (COPY, EXPORT DATA, Arrow) Fast bulk transfer
Chunked reads in Python pandas.read_sql(..., chunksize=...) limits memory
In-database ML No data movement

Code Example: Deterministic 10% User Sample (PostgreSQL)

WITH users(user_id) AS (
  SELECT 'user_' || g FROM generate_series(1, 1000) AS g
)
SELECT count(*) AS sampled_users
FROM users
WHERE abs(hashtext(user_id)) % 100 < 10;
-- Expected: roughly 100 (the same users on every run)

Hashing the user ID (rather than random()) returns the same users every time and keeps all of a user’s rows together, so train/test splits built the same way never share users. In BigQuery use MOD(ABS(FARM_FINGERPRINT(user_id)), 100) < 10.

Moving Results to Python

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://user:password@localhost/analytics")
query = "SELECT user_id, events_30d, spend_90d, churned FROM ml.training_snapshot WHERE snapshot_date = '2026-11-01'"
chunks = pd.read_sql(query, engine, chunksize=200_000)
for i, chunk in enumerate(chunks):
    chunk.to_parquet(f"training_part_{i:03d}.parquet", index=False)

Common Pitfalls

  • SELECT * on wide tables in a pay-per-scan warehouse.

  • ORDER BY random() LIMIT n, which sorts the whole table.

  • Pulling raw events into pandas to aggregate what SQL could aggregate.

42

What is the role of partitioning in large-scale SQL databases?

Partitioning splits a large table into smaller physical pieces (partitions) by a key, most often a date range, but also lists of values (region) or hash buckets, while queries still see one logical table. Its main benefit is partition pruning: queries filtering on the partition key read only the relevant partitions, which cuts scan time and cost. It also makes maintenance cheaper (drop or archive an old month instantly instead of deleting millions of rows, load or rebuild one partition at a time) and supports parallel processing. For ML, partitioning by event date matches how features, labels and time-based splits are queried.

Read full answer

Applies toPostgreSQL 16 (declarative partitioning); BigQuery/Snowflake equivalents noted

Partitioning Types

Type Key example Good for
Range event_date by month or day Time series, logs, events
List region IN ('EU', 'US') Known categories
Hash hash(user_id) % 16 Even spread when there is no natural range

In BigQuery, tables are partitioned by date and optionally clustered by columns; Snowflake uses automatic micro-partitions with optional clustering keys.

Code Example (PostgreSQL)

CREATE TABLE events (
  event_id   bigint,
  user_id    text NOT NULL,
  event_at   timestamptz NOT NULL,
  event_type text NOT NULL
) PARTITION BY RANGE (event_at);

CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

INSERT INTO events VALUES
  (1, 'u1', '2026-10-15 09:00+00', 'click'),
  (2, 'u1', '2026-11-02 10:00+00', 'purchase'),
  (3, 'u2', '2026-11-03 11:00+00', 'click');

-- Only the November partition is scanned (check with EXPLAIN)
EXPLAIN SELECT user_id, count(*) FROM events
WHERE event_at >= '2026-11-01' AND event_at < '2026-12-01'
GROUP BY user_id;

SELECT tableoid::regclass AS partition, count(*) FROM events GROUP BY 1 ORDER BY 1;
-- Expected: events_2026_10 | 1
--           events_2026_11 | 2

-- Retention: remove a whole month instantly
-- ALTER TABLE events DETACH PARTITION events_2026_10;  DROP TABLE events_2026_10;

Common Pitfalls

  • Filtering on an expression of the key (e.g. date(event_at) = ... in some engines) that prevents pruning; filter on the column directly.

  • Too many tiny partitions, which slow planning.

  • Partitioning by a column that queries rarely filter on.

43

How can you efficiently update a large SQL-based ML model?

Efficient updates of a large SQL-based model avoid rebuilding everything on every run. Recompute features incrementally (only new or changed rows via watermarks or CDC, and MERGE/upserts), keep large tables partitioned by date so jobs touch only recent partitions, and use warm starts where the training method allows (BigQuery ML supports warm_start for some model types; in Python, incremental learners use partial_fit). Score in batches over changed entities only, and swap new outputs in atomically (write a new table or partition, then rename or switch a view), so users never see half-updated results.

Read full answer

Applies toPostgreSQL 16 (MERGE, partitions), BigQuery ML for warm start

Techniques

Technique Saves
Incremental feature updates (watermark, CDC) Reprocessing unchanged history
MERGE / INSERT ... ON CONFLICT Full table rewrites
Date partitioning + partition pruning Scanning old data
Pre-aggregated summary tables Recomputing long-window aggregates
Warm-start or incremental training Full retraining
Score only changed entities Re-scoring everyone
Blue/green tables + view swap Downtime and partial reads

Code Example: MERGE Changed Scores and Swap Atomically (PostgreSQL 15+)

-- New scores for entities whose features changed today
CREATE TEMP TABLE new_scores(entity_id text PRIMARY KEY, score numeric, model_version text);
INSERT INTO new_scores VALUES ('a', 0.81, 'v7'), ('c', 0.12, 'v7');

CREATE TABLE IF NOT EXISTS current_scores(entity_id text PRIMARY KEY, score numeric, model_version text);
INSERT INTO current_scores VALUES ('a', 0.70, 'v6'), ('b', 0.40, 'v6')
ON CONFLICT DO NOTHING;

MERGE INTO current_scores t
USING new_scores s ON t.entity_id = s.entity_id
WHEN MATCHED THEN UPDATE SET score = s.score, model_version = s.model_version
WHEN NOT MATCHED THEN INSERT (entity_id, score, model_version) VALUES (s.entity_id, s.score, s.model_version);

SELECT * FROM current_scores ORDER BY entity_id;
-- Expected: a | 0.81 | v7
--           b | 0.40 | v6
--           c | 0.12 | v7

-- For a full model change, build a new table and swap the view in one transaction
BEGIN;
CREATE OR REPLACE VIEW scores AS SELECT * FROM current_scores;
COMMIT;

Only changed rows are written; unchanged entity b keeps its previous score.

Common Pitfalls

  • Mixing scores from different model versions without recording the version.

  • Incremental aggregates that drift from a full recompute; reconcile periodically.

  • Long-running updates locking tables that dashboards read.

44

How do you keep SQL data for ML consistent and reliable?

Reliable ML data in SQL comes from enforcing expectations at every layer. Use schema constraints (types, NOT NULL, primary and foreign keys, CHECK), automated data tests in the pipeline (uniqueness, accepted values, ranges, referential integrity, row-count and null-rate thresholds, freshness), deduplication with window functions, consistent definitions in one shared SQL model for training and serving, and transactions and idempotent jobs so partial runs never leave half-written tables. Monitor distributions over time to catch silent changes in upstream systems.

Read full answer

Applies toPostgreSQL 16, dbt tests / Great Expectations

Checks

Check Example
Types and constraints amount numeric CHECK (amount >= 0), NOT NULL keys
Uniqueness One row per (user_id, snapshot_date)
Referential integrity Every order’s user exists
Accepted values status IN ('active', 'cancelled')
Volume Today’s rows within ±30% of the 7-day average
Freshness max(loaded_at) within the last 2 hours
Null rate Feature nulls below a threshold
Distribution Mean and quantiles stable vs last week

Code Example: Deduplicate and Run Quality Checks

WITH raw_orders(order_id, user_id, amount, loaded_at) AS (
  VALUES (1, 'u1', 20.0, TIMESTAMP '2026-11-01 10:00'),
         (1, 'u1', 20.0, TIMESTAMP '2026-11-01 10:05'),   -- duplicate load
         (2, 'u2', -5.0, TIMESTAMP '2026-11-01 11:00'),   -- invalid amount
         (3, NULL, 15.0, TIMESTAMP '2026-11-01 12:00')    -- missing user
),
deduped AS (
  SELECT *
  FROM (SELECT *, row_number() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) AS rn
        FROM raw_orders) r
  WHERE rn = 1
)
SELECT count(*)                                   AS rows_after_dedup,
       count(*) FILTER (WHERE amount < 0)         AS negative_amounts,
       count(*) - count(user_id)                  AS missing_user_ids,
       (SELECT count(*) FROM raw_orders) - count(*) AS duplicates_removed
FROM deduped;
-- Expected: 3 | 1 | 1 | 1

A pipeline step fails (raises an error or stops the DAG) when any check column is above its threshold, so bad data never reaches training.

Common Pitfalls

  • Testing only in development; run checks on every production load.

  • NOT IN with a subquery that contains NULLs returns no rows; use NOT EXISTS.

  • Fixing data issues inside the model code rather than at the source.

45

How would you design a system that regularly feeds SQL data to a model?

A system that feeds a model from SQL has four parts: source tables that land raw data reliably (CDC or batch loads), scheduled transformations (dbt or SQL jobs run by Airflow, Dagster or the warehouse scheduler) that build validated, point-in-time correct feature tables, a delivery path to the model (batch export to Parquet/object storage, a direct query with a watermark, or sync into an online feature store for real-time serving), and monitoring of freshness, row counts and feature drift. Incremental processing with a high-water mark keeps each run cheap, and idempotent jobs make reruns safe.

Read full answer

Applies toPostgreSQL 16 (portable SQL), Airflow/dbt for orchestration

Architecture

Layer Choice
Ingestion CDC (Debezium, Fivetran) or nightly batch loads
Transformation dbt models: staging → features → training/scoring tables
Validation dbt tests, row-count and null-rate checks; fail the run on violations
Delivery (batch) Export to Parquet; Python reads with pandas.read_parquet or read_sql
Delivery (online) Materialize latest features to Redis/feature store (Feast)
Scheduling Daily/hourly DAG with retries and alerts
Monitoring Freshness (max(updated_at)), drift queries, SLA alerts

Code Example: Incremental Load with a Watermark (PostgreSQL)

CREATE TABLE etl_watermarks (job_name text PRIMARY KEY, last_loaded_at timestamptz NOT NULL);
INSERT INTO etl_watermarks VALUES ('user_features', '2026-11-01 00:00+00');

-- Each run: recompute features only for users with new events since the watermark
WITH changed_users AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_at > (SELECT last_loaded_at FROM etl_watermarks WHERE job_name = 'user_features')
)
INSERT INTO user_features (user_id, events_30d, last_event_at, computed_at)
SELECT e.user_id,
       count(*) FILTER (WHERE e.event_at >= now() - interval '30 days'),
       max(e.event_at),
       now()
FROM events e
JOIN changed_users c USING (user_id)
GROUP BY e.user_id
ON CONFLICT (user_id) DO UPDATE
  SET events_30d = EXCLUDED.events_30d,
      last_event_at = EXCLUDED.last_event_at,
      computed_at = EXCLUDED.computed_at;

UPDATE etl_watermarks
SET last_loaded_at = (SELECT max(event_at) FROM events)
WHERE job_name = 'user_features';

Run the insert and the watermark update in one transaction so a failure does not skip data. ON CONFLICT makes reruns idempotent (requires a primary key on user_id).

Common Pitfalls

  • Late-arriving events older than the watermark; reprocess a lookback window (e.g. the last 2 days).

  • Training features built by one query and serving features by another, so they drift apart.

  • No freshness alert, so the model silently scores stale data.

46

Which SQL reporting features help analyze ML model performance?

Several SQL features make model performance reports concise. Conditional aggregation (FILTER, SUM(CASE ...)) builds confusion matrices; window functions compute ranks for AUC, running totals for gain/lift charts and week-over-week changes with LAG; GROUPING SETS, ROLLUP and CUBE produce totals and per-segment breakdowns in one query; date_trunc and generate_series create complete time series; percentile_cont gives error quantiles; and materialized views or scheduled tables store results for dashboards.

Read full answer

Applies toPostgreSQL 16

Features

Feature Use in model reporting
FILTER / CASE TP, FP, TN, FN counts
ROLLUP / GROUPING SETS Metrics per segment plus overall totals
Window functions (rank, sum() OVER, lag) AUC, lift curves, period-over-period change
ntile(10) Deciles for lift and gain tables
percentile_cont Median and P90 absolute error
date_trunc, generate_series Time buckets with no gaps
Materialized views Precomputed reports refreshed on a schedule

Code Example: Accuracy by Segment with Totals (ROLLUP)

WITH preds(region, device, correct) AS (
  VALUES ('EU', 'mobile', 1), ('EU', 'mobile', 0), ('EU', 'desktop', 1),
         ('US', 'mobile', 1), ('US', 'desktop', 1), ('US', 'desktop', 0)
)
SELECT COALESCE(region, 'ALL') AS region,
       COALESCE(device, 'ALL') AS device,
       count(*) AS n,
       round(avg(correct), 3) AS accuracy
FROM preds
GROUP BY ROLLUP (region, device)
ORDER BY GROUPING(region), region, GROUPING(device), device;
-- Expected:
-- EU  | desktop | 1 | 1.000
-- EU  | mobile  | 2 | 0.500
-- EU  | ALL     | 3 | 0.667
-- US  | desktop | 2 | 0.500
-- US  | mobile  | 1 | 1.000
-- US  | ALL     | 3 | 0.667
-- ALL | ALL     | 6 | 0.667

Lift Table with ntile

-- Decile 1 = highest scores; compare each decile's positive rate with the overall rate
SELECT decile, count(*) AS n, avg(actual) AS positive_rate,
       avg(actual) / (SELECT avg(actual) FROM scored) AS lift
FROM (SELECT actual, ntile(10) OVER (ORDER BY score DESC) AS decile FROM scored) d
GROUP BY decile
ORDER BY decile;

COALESCE is safe here because the sample has no real NULL regions; otherwise use GROUPING() to label total rows.

Common Pitfalls

  • Confusing ROLLUP total rows with genuine NULL values.

  • Small segments with noisy metrics; always show the count.

  • ntile splitting ties arbitrarily between deciles.

47

How can SQL help visualize data distributions before modelling?

SQL can summarize distributions before data reach a model, producing compact tables that a chart tool or notebook plots. Use width_bucket or floor(value / width) to build histograms, percentile_cont for quantiles and box-plot statistics, count(*) - count(column) for missing values, GROUP BY on categories for frequency tables (and label balance), and comparisons across time windows to spot drift. Running these in the database avoids moving millions of rows into Python just to draw a chart.

Read full answer

Applies toPostgreSQL 16

Profiling Queries

Question SQL
Histogram width_bucket(x, min, max, n_buckets) + GROUP BY
Quantiles / box plot percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY x)
Missing values count(*) - count(x)
Category frequencies GROUP BY category ORDER BY count(*) DESC
Class balance avg(label::numeric)
Outliers Rows outside Q1 − 1.5·IQR, Q3 + 1.5·IQR

Code Example: Histogram, Quantiles and Nulls

WITH orders(order_id, amount) AS (
  VALUES (1, 12.0), (2, 18.0), (3, 25.0), (4, 31.0), (5, 44.0),
         (6, 47.0), (7, 52.0), (8, 95.0), (9, NULL), (10, 15.0)
)
SELECT bucket,
       (bucket - 1) * 25 AS bucket_start,
       count(*) AS n,
       repeat('#', count(*)::int) AS bar
FROM (SELECT width_bucket(amount, 0, 100, 4) AS bucket     -- 4 buckets of width 25
      FROM orders
      WHERE amount IS NOT NULL) b
GROUP BY bucket
ORDER BY bucket;
-- Expected:
-- 1 |  0 | 3 | ###
-- 2 | 25 | 4 | ####
-- 3 | 50 | 1 | #
-- 4 | 75 | 1 | #
WITH orders(amount) AS (
  VALUES (12.0), (18.0), (25.0), (31.0), (44.0), (47.0), (52.0), (95.0), (NULL), (15.0)
)
SELECT count(*) AS total_rows,
       count(*) - count(amount) AS missing,
       percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY amount) AS quartiles
FROM orders;
-- Expected: 10 | 1 | {18,31,47}

The bucket query feeds a bar chart directly; the quartiles give a box plot and outlier fences (95 is beyond Q3 + 1.5 × IQR = 90.5).

Common Pitfalls

  • Values exactly at the upper bound fall into an extra bucket with width_bucket (bucket n + 1).

  • Ignoring NULLs, which count(column) and avg silently skip.

  • Profiling the full history when the model will see recent data; compare time windows.

48

How can you use SQL to report a model's performance metrics?

Reporting model performance in SQL starts from a predictions table joined to actual outcomes, then computes metrics with aggregates: classification counts and precision/recall/F1 with conditional aggregation, regression errors (MAE, RMSE, bias) with avg and sqrt, and calibration by grouping scores into buckets. Grouping by model version, date and segment turns these into a monitoring report, and saving the results into a metrics table lets dashboards show trends and alerts. Ranking metrics such as AUC can also be computed in SQL with window functions.

Read full answer

Applies toPostgreSQL 16

Metrics and SQL Building Blocks

Metric SQL
Precision, recall, F1 count(*) FILTER (...) for TP/FP/FN
MAE / RMSE avg(abs(y - y_hat)), sqrt(avg(power(y - y_hat, 2)))
Bias avg(y_hat - y)
Calibration width_bucket(score, 0, 1, 10), compare avg(score) with avg(actual)
AUC Rank scores with rank(); Mann-Whitney formula
Trends GROUP BY date_trunc('week', predicted_at), model_version

Code Example: Weekly Report with F1 and AUC

WITH scored(week, actual, score) AS (
  VALUES ('2026-W44', 1, 0.9), ('2026-W44', 0, 0.4), ('2026-W44', 1, 0.6), ('2026-W44', 0, 0.7),
         ('2026-W45', 1, 0.8), ('2026-W45', 0, 0.2), ('2026-W45', 1, 0.3), ('2026-W45', 0, 0.1)
),
ranked AS (
  SELECT *, rank() OVER (PARTITION BY week ORDER BY score) AS r     -- ties would need average ranks
  FROM scored
),
agg AS (
  SELECT week,
         count(*) FILTER (WHERE actual = 1 AND score >= 0.5) AS tp,
         count(*) FILTER (WHERE actual = 0 AND score >= 0.5) AS fp,
         count(*) FILTER (WHERE actual = 1 AND score <  0.5) AS fn,
         count(*) FILTER (WHERE actual = 1) AS n_pos,
         count(*) FILTER (WHERE actual = 0) AS n_neg,
         sum(r) FILTER (WHERE actual = 1) AS pos_rank_sum
  FROM ranked
  GROUP BY week
)
SELECT week,
       round(2.0 * tp / NULLIF(2 * tp + fp + fn, 0), 3) AS f1,
       round((pos_rank_sum - n_pos * (n_pos + 1) / 2.0) / (n_pos * n_neg), 3) AS auc
FROM agg
ORDER BY week;
-- Expected: 2026-W44 | 0.800 | 0.750
--           2026-W45 | 0.667 | 1.000

In week 44, positives rank 4 and 2 (sum 6): AUC = (6 − 3) / 4 = 0.75. In week 45 every positive outscores every negative, so AUC = 1, even though the 0.5 threshold misses one positive (F1 0.667).

Common Pitfalls

  • Reporting metrics on predictions whose outcomes are not yet mature (e.g. churn labels need 30 days).

  • Averaging weekly metrics instead of computing them over pooled data.

  • Integer division in ratios.

49

Can SQL be used to visualize a classifier's false positives and negatives?

Yes: SQL prepares the data, and a BI tool or notebook draws it. SQL can classify each prediction as TP, FP, TN or FN with a CASE expression, then aggregate errors by threshold, segment, time or score bucket into tidy tables that dashboards (Metabase, Looker, Superset, Tableau) plot as bar charts, heatmaps or trend lines. For quick checks, SQL can even draw a text bar with repeat(). The most useful views are error counts by segment, errors over time, and how FP and FN counts trade off as the threshold changes.

Read full answer

Applies toPostgreSQL 16 (generate_series, repeat), any BI tool

Useful Error Views

View SQL shape Chart
Errors by segment GROUP BY segment with FP/FN counts Grouped bars
Errors over time GROUP BY date_trunc('week', predicted_at) Lines
Threshold trade-off Cross join with thresholds from generate_series Lines of FP and FN vs threshold
Score distribution of errors width_bucket(score, 0, 1, 10) Histogram
Example rows WHERE outcome = 'FN' ORDER BY score DESC LIMIT 20 Table for review

Code Example: FP/FN by Threshold with a Text Bar

WITH scored(actual, score) AS (
  VALUES (1, 0.90), (1, 0.70), (1, 0.40), (0, 0.60),
         (0, 0.30), (0, 0.20), (0, 0.10), (1, 0.80)
),
thresholds AS (
  SELECT round(t / 10.0, 1) AS threshold FROM generate_series(3, 7, 2) AS t
)
SELECT th.threshold,
       count(*) FILTER (WHERE s.actual = 0 AND s.score >= th.threshold) AS false_pos,
       count(*) FILTER (WHERE s.actual = 1 AND s.score <  th.threshold) AS false_neg,
       repeat('#', (count(*) FILTER (WHERE s.actual = 0 AND s.score >= th.threshold))::int) AS fp_bar,
       repeat('*', (count(*) FILTER (WHERE s.actual = 1 AND s.score <  th.threshold))::int) AS fn_bar
FROM thresholds th
CROSS JOIN scored s
GROUP BY th.threshold
ORDER BY th.threshold;
-- Expected:
-- 0.3 | 2 | 0 | ## |
-- 0.5 | 1 | 1 | #  | *
-- 0.7 | 0 | 1 |    | *

Raising the threshold removes false positives but adds false negatives; the chart of these two columns helps pick a threshold based on their relative costs.

Common Pitfalls

  • Plotting raw counts across segments of very different sizes; show rates too.

  • Hiding the threshold used; errors only exist relative to a threshold.

50

How do you build a confusion matrix for a classifier using SQL?

A confusion matrix in SQL comes from a table of predictions joined to true labels: apply the decision threshold with a CASE expression, then count rows for each combination of actual and predicted class. For binary classification, conditional aggregation (SUM(CASE ...) or PostgreSQL’s COUNT(*) FILTER (WHERE ...)) gives true positives, false positives, true negatives and false negatives in one row, from which precision, recall and accuracy follow. For multiple classes, GROUP BY actual, predicted gives the long form, which can be pivoted.

Read full answer

Applies toPostgreSQL 16 (FILTER clause); portable `SUM(CASE ...)` shown too

Code Example: Binary Confusion Matrix and Metrics

WITH scored(id, actual, score) AS (
  VALUES (1, 1, 0.90), (2, 1, 0.70), (3, 1, 0.40),
         (4, 0, 0.60), (5, 0, 0.30), (6, 0, 0.20),
         (7, 0, 0.10), (8, 1, 0.80)
),
labelled AS (
  SELECT actual, CASE WHEN score >= 0.5 THEN 1 ELSE 0 END AS predicted
  FROM scored
),
cm AS (
  SELECT count(*) FILTER (WHERE actual = 1 AND predicted = 1) AS tp,
         count(*) FILTER (WHERE actual = 0 AND predicted = 1) AS fp,
         count(*) FILTER (WHERE actual = 0 AND predicted = 0) AS tn,
         count(*) FILTER (WHERE actual = 1 AND predicted = 0) AS fn
  FROM labelled
)
SELECT tp, fp, tn, fn,
       round(tp::numeric / NULLIF(tp + fp, 0), 3)       AS precision,
       round(tp::numeric / NULLIF(tp + fn, 0), 3)       AS recall,
       round((tp + tn)::numeric / (tp + fp + tn + fn), 3) AS accuracy
FROM cm;
-- Expected: tp 3 | fp 1 | tn 3 | fn 1 | precision 0.750 | recall 0.750 | accuracy 0.750

The ::numeric cast avoids integer division, and NULLIF avoids division by zero when there are no positive predictions.

Multi-Class (Long Form, Portable SQL)

WITH preds(actual, predicted) AS (
  VALUES ('cat', 'cat'), ('cat', 'dog'), ('dog', 'dog'), ('bird', 'bird'), ('dog', 'dog'), ('bird', 'cat')
)
SELECT actual,
       SUM(CASE WHEN predicted = 'bird' THEN 1 ELSE 0 END) AS pred_bird,
       SUM(CASE WHEN predicted = 'cat'  THEN 1 ELSE 0 END) AS pred_cat,
       SUM(CASE WHEN predicted = 'dog'  THEN 1 ELSE 0 END) AS pred_dog
FROM preds
GROUP BY actual
ORDER BY actual;
-- Expected: bird | 1 | 1 | 0
--           cat  | 0 | 1 | 1
--           dog  | 0 | 0 | 2

Common Pitfalls

  • Integer division making precision 0.

  • Rows with NULL labels silently counted as negatives; filter them out first.

  • Joining predictions to labels with duplicate keys, which inflates counts.

51

How would you version control ML training datasets in SQL?

Versioning datasets in SQL means making every training set reproducible: given a model, you can rebuild exactly the rows and feature values it was trained on. Common techniques are immutable snapshot tables or partitions keyed by snapshot_date/dataset_version, slowly changing dimension type 2 tables with valid_from/valid_to so any past state can be queried, warehouse time travel (Snowflake AT, BigQuery FOR SYSTEM_TIME AS OF), and zero-copy clones. The SQL that builds the dataset is versioned in Git (dbt), and a manifest table records version, query commit, row count and a checksum.

Read full answer

Applies toPostgreSQL 16 (snapshots, SCD2), Snowflake/BigQuery time travel

Techniques

Technique Pros Cons
Snapshot tables per version Simple, exact Storage grows
SCD type 2 history Any point in time; compact More complex queries
Time travel Built in Limited retention (days)
Zero-copy clone (Snowflake) Instant, cheap Warehouse-specific
Manifest + checksum Detects silent changes Needs discipline

Code Example: SCD2 Table Queried “As Of” a Date (PostgreSQL)

WITH customer_history(customer_id, plan, valid_from, valid_to) AS (
  VALUES ('c1', 'basic',   DATE '2026-01-01', DATE '2026-06-01'),
         ('c1', 'premium', DATE '2026-06-01', NULL),
         ('c2', 'basic',   DATE '2026-03-15', NULL)
)
SELECT customer_id, plan
FROM customer_history
WHERE valid_from <= DATE '2026-05-01'
  AND (valid_to IS NULL OR valid_to > DATE '2026-05-01')
ORDER BY customer_id;
-- Expected: c1 | basic
--           c2 | basic

Manifest for Each Dataset Version

CREATE TABLE dataset_versions (
  dataset_version text PRIMARY KEY,
  as_of_date      date NOT NULL,
  sql_commit      text NOT NULL,
  row_count       bigint NOT NULL,
  checksum        text NOT NULL
);

-- Checksum of the snapshot content (order-independent via string_agg with ORDER BY)
SELECT count(*) AS row_count,
       md5(string_agg(customer_id || ':' || plan, ',' ORDER BY customer_id)) AS checksum
FROM ml.training_snapshot
WHERE dataset_version = 'v2026_11_01';

Rebuilding the dataset later and comparing the checksum confirms it is identical.

Common Pitfalls

  • Using “latest” dimension tables to rebuild old training data, which leaks future values.

  • Time travel retention expiring before you need to reproduce a model.

  • Not recording the SQL code version alongside the data version.

52

What is data lineage, and how can you track it with SQL?

Data lineage records where data came from and how it was transformed on its way to a table, report, feature or model: which source tables, which queries or jobs, which versions, and when. It is essential for debugging (“why did this feature change?”), impact analysis before changing a table, reproducibility of models, and compliance (GDPR deletion, audits). With SQL you can track it at three levels: table-level dependencies (from dbt’s DAG, information_schema view definitions or query logs), run-level metadata tables that record each job’s inputs, outputs and row counts, and row-level provenance columns such as source_system and load_batch_id.

Read full answer

Applies toPostgreSQL 16, dbt (lineage graph), warehouse access history views

Approaches

Level How
Table dependencies dbt ref() builds a DAG automatically; view dependencies in pg_depend / information_schema.view_table_usage
Query history Snowflake ACCESS_HISTORY, BigQuery INFORMATION_SCHEMA.JOBS (which tables each query read and wrote)
Run metadata pipeline_runs table: job, inputs, outputs, code version, row counts, timestamps
Row provenance source_system, load_batch_id, ingested_at columns
Open standards OpenLineage events collected by tools like Marquez or DataHub

Code Example: View Dependencies and a Run Log (PostgreSQL)

-- Which base tables does a view read from?
SELECT view_name, table_name
FROM information_schema.view_table_usage
WHERE view_schema = 'ml' AND view_name = 'training_features';

-- A simple run log that every pipeline step writes to
CREATE TABLE pipeline_runs (
  run_id        bigserial PRIMARY KEY,
  job_name      text NOT NULL,
  input_tables  text[] NOT NULL,
  output_table  text NOT NULL,
  git_commit    text NOT NULL,
  rows_written  bigint,
  started_at    timestamptz NOT NULL,
  finished_at   timestamptz
);

INSERT INTO pipeline_runs (job_name, input_tables, output_table, git_commit, rows_written, started_at, finished_at)
VALUES ('build_features', ARRAY['raw.orders', 'raw.customers'], 'ml.training_features', 'a1b2c3d', 120000,
        '2026-11-01 02:00+00', '2026-11-01 02:07+00');

-- Lineage question: which runs produced the table used to train model v2, and from what?
SELECT job_name, input_tables, git_commit, rows_written, finished_at
FROM pipeline_runs
WHERE output_table = 'ml.training_features'
ORDER BY finished_at DESC
LIMIT 1;
-- Expected: build_features | {raw.orders,raw.customers} | a1b2c3d | 120000 | 2026-11-01 02:07:00+00

Store the run_id (or snapshot date) in the model registry, so a model points to the exact data run that trained it.

Common Pitfalls

  • Lineage that stops at table level when a column-level change broke a feature.

  • Ad-hoc manual queries that bypass the pipeline and leave no record.

53

How do you log and track model predictions in a SQL database?

Logging predictions in SQL means writing every prediction to an append-only table with the entity ID, timestamp, model version, the score and decision, and ideally the feature values (or a reference to them), keyed so that outcomes can be joined later when labels arrive. This table supports auditing (“why was this customer declined?”), monitoring of score distributions and drift, and true performance measurement once ground truth is known. Partition it by date, keep it immutable, and store the threshold used so decisions can be reproduced.

Read full answer

Applies toPostgreSQL 16

Table Design

Column Purpose
prediction_id Unique key
entity_id Customer, transaction or item
predicted_at When the prediction was made (timestamptz)
model_version Which model produced it
score, threshold, decision Output and how it was used
features (jsonb) Inputs as seen at prediction time
request_id Link to application logs

Outcomes go in a separate table (outcomes(entity_id, outcome_at, label)) because they arrive later.

Code Example (PostgreSQL)

WITH prediction_log(prediction_id, entity_id, predicted_at, model_version, score, threshold) AS (
  VALUES (1, 'c1', TIMESTAMPTZ '2026-10-01 10:00+00', 'v2', 0.91, 0.5),
         (2, 'c2', TIMESTAMPTZ '2026-10-01 10:05+00', 'v2', 0.20, 0.5),
         (3, 'c3', TIMESTAMPTZ '2026-10-01 11:00+00', 'v2', 0.65, 0.5),
         (4, 'c4', TIMESTAMPTZ '2026-10-02 09:00+00', 'v3', 0.40, 0.5)
),
outcomes(entity_id, label) AS (
  VALUES ('c1', 1), ('c2', 0), ('c3', 0)          -- c4 has no outcome yet
)
SELECT p.model_version,
       count(*)                                   AS predictions,
       count(o.label)                             AS with_outcome,
       round(avg(p.score), 3)                     AS mean_score,
       round(avg(CASE WHEN p.score >= p.threshold THEN 1.0 ELSE 0.0 END), 3) AS positive_rate,
       round(avg(CASE WHEN (p.score >= p.threshold) = (o.label = 1) THEN 1.0 END), 3) AS accuracy_on_labelled
FROM prediction_log p
LEFT JOIN outcomes o ON o.entity_id = p.entity_id
GROUP BY p.model_version
ORDER BY p.model_version;
-- Expected:
-- v2 | 3 | 3 | 0.587 | 0.667 | 0.667
-- v3 | 1 | 0 | 0.400 | 0.000 | NULL

count(o.label) counts only labelled rows, and the accuracy expression returns NULL for unlabelled predictions so avg ignores them. For v2, c1 (0.91, label 1) and c2 (0.20, label 0) are correct, c3 (0.65, label 0) is a false positive, so accuracy is 2/3.

Common Pitfalls

  • Overwriting predictions on re-scoring instead of appending new rows.

  • Storing timestamps without time zones.

  • Joining outcomes on entity alone when an entity has many predictions; join on the prediction or a time window.

54

How can you manage an ML model's full lifecycle with SQL tools?

SQL tools can cover most of the ML lifecycle around the model itself: data extraction and validation, feature and label tables, versioned training snapshots, in-database training for simple models (BigQuery ML, Snowflake ML, PostgreSQL extensions), batch scoring, prediction logging, and monitoring of drift and accuracy. The key is to treat the warehouse as the system of record, with every stage as a table or view keyed by model version and date, managed with version-controlled SQL (dbt) and a scheduler. Training complex models usually moves to Python, but its inputs and outputs still live in SQL tables.

Read full answer

Applies toPostgreSQL 16, dbt, BigQuery ML / Snowflake (in-warehouse ML)

Lifecycle Stages

Stage SQL artefact
Data extraction Staging views over raw tables
Validation Tests: not-null, unique keys, accepted values, row counts (dbt tests)
Features and labels Point-in-time feature tables, label tables
Training data versioning Snapshot tables keyed by snapshot_date
Training CREATE MODEL in-warehouse, or Python reading the snapshot
Registry model_registry(model_version, trained_at, data_snapshot, metrics, status)
Batch scoring INSERT INTO predictions SELECT ... FROM ML.PREDICT(...)
Monitoring Drift and performance queries joining predictions to outcomes
Retirement Registry status changes; old predictions retained for audit

Code Example: Registry and Monitoring Tables (PostgreSQL)

CREATE TABLE model_registry (
  model_version  text PRIMARY KEY,
  trained_at     timestamptz NOT NULL,
  data_snapshot  date NOT NULL,
  auc            numeric(5, 4),
  status         text NOT NULL CHECK (status IN ('candidate', 'production', 'retired'))
);

CREATE TABLE predictions (
  prediction_id  bigserial PRIMARY KEY,
  model_version  text NOT NULL REFERENCES model_registry,
  entity_id      text NOT NULL,
  scored_at      timestamptz NOT NULL,
  score          numeric(6, 5) NOT NULL
);

INSERT INTO model_registry VALUES
  ('v1', '2026-09-01', '2026-08-31', 0.8010, 'retired'),
  ('v2', '2026-10-01', '2026-09-30', 0.8150, 'production');

-- Only one production model at a time
CREATE UNIQUE INDEX one_production ON model_registry ((status)) WHERE status = 'production';

SELECT model_version, data_snapshot, auc
FROM model_registry
WHERE status = 'production';
-- Expected: v2 | 2026-09-30 | 0.8150

The partial unique index enforces “one production model” in the database itself, and every prediction references the model version that produced it.

When to Leave SQL

Deep learning, complex custom pipelines, and heavy hyperparameter searches belong in Python; keep the data contracts (input snapshot, prediction table) in SQL.

Common Pitfalls

  • No link between predictions and model or data versions, which makes incidents impossible to trace.

  • Feature logic duplicated in SQL for training and in application code for serving, drifting apart.

55

How can you automate model retraining and evaluation from within SQL?

Automating retraining from SQL works best when the database owns the data steps and a scheduler owns the workflow. Scheduled SQL (dbt models, cron, Airflow or the warehouse’s own scheduler) rebuilds a point-in-time correct training table on a fixed cadence; the model is then retrained either inside the warehouse (BigQuery ML CREATE OR REPLACE MODEL, Snowflake ML functions) or by a Python job reading that table. Evaluation metrics are written to a metrics table, and a SQL check compares the candidate with the current production model on the same holdout before promotion. Drift and performance queries can trigger retraining instead of a fixed schedule.

Read full answer

Applies toPostgreSQL 16 (portable SQL), BigQuery ML for the in-warehouse example

Strategy

Step SQL-side mechanism
Rebuild features and labels Scheduled INSERT ... SELECT / dbt incremental models with a snapshot date
Train BigQuery ML CREATE OR REPLACE MODEL, or a Python job reading the table
Evaluate ML.EVALUATE (BigQuery) or metric queries over a predictions table
Compare Query candidate vs champion metrics on the same holdout window
Promote Update a model_registry row (status = 'production') only if better
Trigger Schedule, or a drift/performance query exceeding a threshold

Code Example: Champion vs Challenger Check (PostgreSQL)

-- Metrics written by each training run (sample rows)
WITH model_metrics(model_version, eval_window, auc, logloss) AS (
  VALUES ('v12', '2026-10', 0.812, 0.401),
         ('v13', '2026-10', 0.826, 0.392)
),
registry(model_version, status) AS (
  VALUES ('v12', 'production'),
         ('v13', 'candidate')
)
SELECT c.model_version AS candidate,
       p.model_version AS production,
       c.auc - p.auc   AS auc_gain,
       CASE WHEN c.auc >= p.auc + 0.005 AND c.logloss <= p.logloss
            THEN 'promote' ELSE 'keep production' END AS decision
FROM registry rc
JOIN model_metrics c ON c.model_version = rc.model_version AND rc.status = 'candidate'
JOIN registry rp ON rp.status = 'production'
JOIN model_metrics p ON p.model_version = rp.model_version AND p.eval_window = c.eval_window;
-- Expected: v13 | v12 | 0.014 | promote

In-Warehouse Retraining (BigQuery ML)

CREATE OR REPLACE MODEL `proj.ml.churn_model`
OPTIONS (model_type = 'logistic_reg', input_label_cols = ['churned']) AS
SELECT * EXCEPT (customer_id, snapshot_date)
FROM `proj.ml.training_snapshots`
WHERE snapshot_date < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

SELECT * FROM ML.EVALUATE(MODEL `proj.ml.churn_model`,
  (SELECT * FROM `proj.ml.training_snapshots`
   WHERE snapshot_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)));

The most recent 30 days are held out by time, so evaluation reflects future data.

Common Pitfalls

  • Retraining on a table rebuilt with today’s feature values for old labels (leakage).

  • Comparing models evaluated on different windows.

  • Auto-promoting without a minimum improvement and guardrail checks.

Sign in to keep reading

You've opened 2 full answers. Sign in to read all 55 SQL in ML answers, mark what you know and pick up where you left off.

Report a mistake

What's wrong?

All Machine Learning & Data Science subjects