SQL interview questions · Data scientist
14 SQL interview questions for data scientists
Data science SQL screens test whether you can prepare data for analysis yourself: distributions, rolling statistics, cohorts and features built with window functions. Expect at least one question where a naive average or a wrong window frame gives a plausible but wrong answer.
What data scientist SQL interviews test
- Distributions: medians, quartiles and histograms
- Rolling and moving averages with the right window frame
- Cohorts and retention from event data
- Ranking, lag and lead to build features
- Conditional ratios without integer-division traps
Distributions
- Median salary by departmentReturn department and median_salary for each department, sorted by department.Pattern: PERCENTILE_CONT · ordered-set aggregatesPro
- Salary quartilesAssign every employee a pay_quartile with NTILE(4) over the whole company, ordered by salary highest first (quartile 1 = best paid). Return pay_quartile, employees (headcount), min_salary and max_salary for each quartile, sorted by pay_quartile.Pattern: NTILE() · bucketsPro
- Order value histogramFor completed orders, return bucket_start (the amount rounded DOWN to a multiple of 100: 0, 100, 200, ...) and order_count, one row per bucket that has orders, sorted by bucket_start.Pattern: FLOOR · histogram bucketsPro
- Paid above department averageReturn department, name, salary and dept_avg for employees whose salary is above their department's average salary. Sort by department, salary (highest first), then name.Pattern: AVG() OVER · compare to groupPro
Rolling statistics and features
- Running revenue totalFor completed orders, return order_date, amount and running_total (cumulative sum of amount by order_date, rounded to 2 decimals), oldest first.Pattern: SUM() OVER · running totalsPro
- 3-transaction moving averageFor account 102, return id, txn_date, amount and moving_avg: the average amount of this transaction and the two before it (fewer for the first two), ordering transactions by txn_date then id, rounded to 2 decimals. Sort by txn_date, id.Pattern: Window frames · ROWS vs RANGEPro
- 7-day moving average revenueTotal completed order amounts per order_date. Then, for each order_date from 2024-07-07 onward, return day, revenue (that day's completed total) and moving_avg_7d (completed revenue over the 7 calendar days ending that day, divided by 7, rounded to 2 decimals). Oldest first.Pattern: RANGE window frame · calendar moving averagePro
- Days until next orderFor every completed order, return customer_id, order_id, order_date and days_to_next (whole days until the same customer's next completed order; NULL for their last one). Sort by customer_id, order_date.Pattern: LEAD() · PARTITION BYPro
- First vs latest order valueFor each customer with at least two completed orders, return customer_id, first_amount (amount of their earliest completed order), latest_amount (amount of their most recent completed order) and amount_change (latest_amount - first_amount). Sort by customer_id.Pattern: FIRST_VALUE · LAST_VALUE · frame trapPro
- Three days of rising salesFind every run of three consecutive calendar days at the same kiosk where cups_sold strictly increased from day 1 to day 2 and from day 2 to day 3. Overlapping runs each count, and a closed day (no row) breaks a run. Return kiosk, start_date (day 1) and end_date (day 3). Sort by kiosk, start_date.Pattern: Self-join on date + 1 · consecutive-day trendsPro
Cohorts and rates
- Month-1 retention by signup cohortFor users who signed up (users.created_at) in January through March 2024, return cohort_month (YYYY-MM), cohort_size, retained (cohort users with at least one app open in the calendar month after their signup month) and retention_pct (retained as a percentage of cohort_size, rounded to 1 decimal). Oldest cohort first.Pattern: Cohorts · date_trunc · LEFT JOINPro
- New vs returning usersUsing app_opens, for each month return month (YYYY-MM), new_users (active that month for the first time ever), returning_users (active that month, first active in an earlier month) and cumulative_users (distinct users seen in any month up to and including this one). Oldest first.Pattern: First-seen month · running distinct countPro
- Refund rate per customerFor every customer who has placed an order, return customer_id, orders (all their orders, any status), refunds (orders with status 'refunded') and refund_rate (refunds as a percentage of orders, rounded to 2 decimals). Sort by refund_rate (highest first), then customer_id.Pattern: Conditional aggregation · FILTERPro
- Wallet balance from a ledgerReturn username and balance (credits minus debits) for every user, with 0 for users who have no ledger entries. Sort by balance (highest first), then username.Pattern: SUM(CASE) · signed amounts · LEFT JOIN + COALESCEPro
Interview tip: When a question says 'average', ask whether the median is the better summary, and check your window frame: ROWS and RANGE give different answers as soon as dates repeat.