SQL Interview Questions for Forward Deployed Engineers

Window-function questions

Window functions are the single biggest separator in FDE SQL rounds. They answer "per group" questions without collapsing rows the way GROUP BY does. If you can only drill one area, drill these.

Find each customer's first purchase

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchased_at) gives each row a rank within the customer; filter to rank 1 in an outer query or CTE. Say why you cannot do this cleanly with GROUP BY alone (you would lose the other columns of that first row).

Compute a running total per customer over time

SUM(amount) OVER (PARTITION BY customer_id ORDER BY event_date) accumulates as the window slides. Be explicit about the ordering and, if asked, about the frame (default is unbounded preceding to current row), which is where subtle wrong answers hide.

Rank the top 3 products by revenue within each region

A "top N per group" question. Rank with RANK() or ROW_NUMBER() partitioned by region and ordered by revenue descending, then filter to the top 3. Mention the difference between RANK (ties share a rank, gaps follow) and ROW_NUMBER (arbitrary tiebreak) so you pick the right one on purpose.

Calculate month-over-month change with LAG

LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month) pulls the prior period onto the current row so you can subtract. Handle the first period, where LAG is NULL, deliberately rather than letting it silently drop out.

Bucket customers into deciles or quartiles

NTILE(4) OVER (ORDER BY total_spend) splits rows into even buckets for cohorting. This shows you can turn raw metrics into the segments a customer-facing report needs.

Join, grain, and aggregation questions

These test whether you can produce a correct number from more than one table. The classic failure is silent fan-out, joining a one-to-many relationship and double-counting. Strong candidates state the grain of every table before joining and sanity-check row counts after.

Total revenue per customer across orders and line items

Joining customers to orders to line items multiplies rows, so summing the order total after the join double-counts. Aggregate line items to the order grain first (in a CTE), then join up. Narrate "one row means one line item" versus "one row means one order" as you go.

Customers with no orders (anti-join)

A LEFT JOIN with a WHERE orders.id IS NULL filter, or NOT EXISTS, finds the gap. Point out that NOT IN breaks silently when the subquery returns a NULL, a classic trap.

Count distinct active users per day

COUNT(DISTINCT user_id) grouped by day. The subtlety is defining "active" and handling a user who appears many times in a day, so state your definition before writing it.

Filter groups with HAVING, not WHERE

"Customers with more than 5 orders" filters on an aggregate, so it belongs in HAVING after GROUP BY, not WHERE. Getting this boundary right signals you actually understand order of evaluation.

Pivot rows into columns with conditional aggregation

SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) turns a status column into per-status totals in one pass. This is the portable way to pivot without vendor-specific syntax.

Cohort, funnel, and correctness questions

Harder rounds push into analyses a customer would actually ask for, and into the NULL and correctness traps that separate careful engineers from the rest.

Monthly retention or cohort analysis

Assign each user to a signup-month cohort, then count how many are active in each later month. Build the cohort in one CTE, activity in another, and join on cohort plus period offset. Say up front how you define "active" and "retained".

Funnel conversion between steps

Count users who reached step 1, then step 2, then step 3, usually with conditional aggregation or successive LEFT JOINs on ordered events. Watch for users who skip or repeat a step.

NULL handling in filters and aggregates

NULL never equals anything, so use IS NULL, and remember aggregates silently skip NULLs while COUNT(*) does not. Interviewers plant a NULL to see if you notice. Reach for COALESCE where a default is correct.

Deduplicate rows before aggregating

If the source has duplicate events, SUM over them inflates the total. Dedupe with DISTINCT or a ROW_NUMBER = 1 CTE first, then aggregate. Always sanity-check row counts before and after.

Median or percentile without a built-in

If PERCENTILE_CONT is unavailable, rank rows with ROW_NUMBER and COUNT, then pick the middle. It shows you can reason about the definition, not just call a function.