SQL

Source: Concepts adapted from Ace the Data Science Interview — Chapter 8 (“SQL & DB Design”) by Kevin Huo & Nick Singh. Rewritten in original wording for this course.

Data science interviews love flashy ML buzzwords, but day-to-day analytics work is often SQL wrangling: pull rows, join facts, aggregate cleanly, explain churn or retention. Product and analytics teams almost always test query writing; early-stage startups may also probe basic schema design and why a join is slow.

This lesson uses a fixed Reddit-style dataset (users, posts) that is wrapped in before your query runs (create + insert seed SQL). Open the Data tab to preview tables (first 15 rows). Write only the SELECT (or WITH … SELECT) you need — do not recreate the tables.

Runtime is SQLite (seed + your query are wrapped and executed through Piston’s Python sqlite3). Prefer LEFT JOIN over RIGHT JOIN here; treat “right join” as swapping table order + left join.

How SQL interview questions are asked

Interviewers usually give hypothetical tables plus a business ask, then watch you translate requirements into SQL in a shared editor or take-home runner. They rarely quiz obscure vendor syntax. They care whether you can:

1. Restate the metric in plain language (“month-over-month retention”, “popular in category”).
2. Sketch the ideal result table, then work backwards with joins / CTEs.
3. Optionally discuss filters-before-joins and avoiding SELECT * when asked about performance.

Open-ended prompts (“why are users churning?”) need clarifying definitions before code.

Working backwards: imagine one denormalized table that already has every column you need, write the final SELECT, then replace missing pieces with joins, aggregates, and CTEs one layer at a time.

Schema (shipped seed)

Table	Columns
users	user_id, country, active_status (0/1), join_time
posts	post_id, user_id, subreddit_id, title, body, active_status, post_time

user_id is the join key. There are 20 users and 30 posts (≥15 each). Use the Data tab before each exercise.

Basic SQL commands

Core verbs you will see constantly:

• SELECT — choose columns (expressions, aliases with AS)
• WHERE — filter rows before grouping
• GROUP BY — collapse rows that share key values
• HAVING — filter after grouping
• ORDER BY — sort (ASC / DESC)
• DISTINCT — unique values
• UNION — stack compatible result sets (duplicates removed; UNION ALL keeps them)

Interview snippets often use PostgreSQL dialect; SQLite here is close enough for SELECT/JOIN/CTE/window practice. active_status is stored as 0/1 integers (SQLite has no native boolean).

Practice: SELECT, WHERE, DISTINCT, ORDER BY

Aggregation and filtering

Must-know aggregates: COUNT, SUM, AVG, MIN, MAX. Combine with GROUP BY. Put non-aggregated filters in WHERE; put conditions on aggregates in HAVING.

Comparisons: =, <> (or !=), <, >, AND / OR. Example pattern: active users outside the US → active_status = 1 AND country <> 'US'.

Practice: GROUP BY and HAVING

Joins

Joins combine rows from multiple tables on a shared key (users.user_id = posts.user_id).

Type	Keeps
INNER JOIN (JOIN)	Only matching keys on both sides
LEFT JOIN	All rows from the left table; NULLs when no match
RIGHT JOIN	All rows from the right table (emulate with swapped LEFT JOIN in SQLite)
FULL OUTER	All rows from both (not available on this runtime — skip or UNION left/right anti-patterns)

Self join: join a table to itself (aliases) to compare pairs of rows.

Example patterns (do not copy OCR typos from books — use clean aliases):

-- Distinct users who made at least one post
SELECT COUNT(DISTINCT u.user_id)
FROM users u
JOIN posts p ON u.user_id = p.user_id;

-- Share of users with ≥1 post (force real division)
SELECT ROUND(
  1.0 * COUNT(DISTINCT CASE WHEN p.post_id IS NOT NULL THEN u.user_id END)
      / COUNT(DISTINCT u.user_id),
  2
) AS pct_users
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id;

Join performance (concept)

Joins are often the expensive step. Habit checklist:

• Project needed columns, not SELECT *
• Filter / aggregate early before joining large facts
• Prefer fewer joins; stage with CTEs when clarity helps the optimizer and the interviewer
• Use LIMIT while exploring

Practice: INNER and LEFT JOIN

CTEs and subqueries

CTEs (WITH name AS (…) ) name intermediate result sets you can reference later — great for multi-step interview problems. Subqueries do the same inline and need an alias.

Hard SQL interviews usually reduce to: break the ask into CTE layers (counts → distributions → final metric). CTEs can be recursive; subqueries cannot (in the same friendly way). Prefer CTEs for readability in interviews unless the prompt forbids them.

Practice: WITH and nested SELECT

Window functions

Window functions compute across a related set of rows without collapsing the row set the way GROUP BY does. You keep detail columns and still attach aggregates.

SUM(expr) OVER (
  PARTITION BY …   -- like GROUP BY buckets
  ORDER BY …       -- processing order
  ROWS BETWEEN …   -- optional frame
)

Compared to “aggregate in a subquery then join back,” windows are usually shorter and clearer.

LAG / LEAD and RANK

• LAG(col, n) — value from n rows before the current row in the window order
• LEAD(col, n) — value from n rows after
• RANK() — rank with gaps on ties (also know DENSE_RANK, ROW_NUMBER)

Example: previous post time in the same subreddit for a user:

LAG(post_time, 1) OVER (
  PARTITION BY user_id, subreddit_id
  ORDER BY post_time ASC
)

Practice: OVER, LAG, RANK

Databases and systems (interview depth)

Beyond query syntax, some loops ask about normalization (reduce duplication vs join cost), indexing join keys, and when a warehouse / columnar store beats a transactional DB. You are not expected to be a DBA — know enough to say “filter early, index the join key, avoid exploding joins” and to sketch a star schema (facts + dimensions) if prompted.

Checklist before the interview

1. Restate the metric; define edge cases (inactive users, NULL joins).
2. Draw the ideal output grain (one row per user? per day?).
3. Layer CTEs: base filters → joins → aggregates → windows.
4. Sanity-check row counts and NULL behavior on LEFT JOIN.
5. If asked about speed: fewer columns, pre-aggregate, selective filters first.

Next up (planned): SQL interview problem sets using this same wrap-seed editor.