SQL interview questions · Data analyst
23 SQL interview questions for data analysts
Data analyst SQL screens check whether you can turn a business question into a correct query, quickly and without silently wrong numbers. Expect aggregation and joins first, then one or two window-function or date questions, usually live in a shared editor.
What data analyst SQL interviews test
- Aggregating correctly: GROUP BY, HAVING, COUNT(DISTINCT) and conditional counts
- Joins that keep the right rows, including customers with no orders
- Rankings and top-N per group with window functions
- Month-by-month numbers, growth rates and date math
- Spotting traps: NULLs, duplicates, ties and integer division
Aggregation and grouping
- Revenue per customerFor completed orders only, return customer_id, order_count and total_amount per customer, highest total_amount first.Pattern: COUNT · SUM · GROUP BYFree
- Counting managersReturn one row with headcount (number of employees), with_manager (employees whose manager_id is filled in) and managers (number of different people who appear as someone's manager_id).Pattern: COUNT(*) · COUNT(col) · COUNT(DISTINCT col)Free
- High-value repeat buyersUsing completed orders, return customer_id, order_count and total_spent for customers with at least 2 completed orders AND total_spent over 300. Sort by customer_id.Pattern: GROUP BY · HAVINGFree
- 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
- Customers by number of ordersReturn completed_orders (a number of completed orders) and customers (how many customers have exactly that many), one row per count that occurs, including 0 for customers with no completed orders. Sort by completed_orders.Pattern: Aggregate of an aggregate · LEFT JOIN · COUNT(column)Pro
- Duplicate card chargesReturn account_id, merchant, amount, txn_date and copies for every charge that appears more than once. Sort by txn_date.Pattern: GROUP BY · HAVING to find duplicatesPro
Joins
- Every user and their planReturn username and plan for every user, with plan NULL when the user has no subscription. Sort by username.Pattern: INNER JOIN vs LEFT JOINFree
- Users who never subscribedReturn the username of every user with no subscription, sorted by username.Pattern: LEFT JOIN · IS NULLFree
- Order count, including zeroReturn name and order_count (number of orders of any status) for every customer, including customers with 0 orders. Sort by order_count (highest first), then name.Pattern: LEFT JOIN · COUNT(col) vs COUNT(*)Free
- Purchase date for every userReturn username and purchase_date (the date of the user's purchase event, NULL if they never purchased) for all users. Sort by username.Pattern: LEFT JOIN · filter in ON vs WHEREFree
- Earning more than their managerReturn employee, employee_salary, manager and manager_salary for every employee who earns more than their manager. Sort by employee.Pattern: Self-joinPro
- Bought oat milk, never coffee beansReturn the username of every shopper who has bought oat milk at least once and has never bought coffee beans in any basket, each shopper once, sorted by username.Pattern: EXISTS · NOT EXISTS · per-customer anti-joinPro
Window functions
- Top 3 earners per departmentReturn department, name, salary and salary_rank (DENSE_RANK by salary, highest first, within each department) for everyone with salary_rank <= 3. Sort by department, salary_rank, name.Pattern: Window functions · DENSE_RANK()Free
- Third-highest salary per departmentReturn department and third_highest_salary (the third-highest distinct salary in that department), one row per department. Departments with fewer than three different salaries are left out. Sort by department.Pattern: Nth highest · DENSE_RANK() vs ROW_NUMBER()Pro
- Second-largest chargeReturn one row with one column, second_largest_amount: the second-highest distinct amount in transactions (charges that tie count once). Write it so it would return NULL, not zero rows, if every charge had the same amount.Pattern: Scalar subquery · MAX below the MAXPro
- 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
- Share of total spendReturn category, total (sum of amount) and pct_of_total (that total as a percentage of all spend, rounded to 1 decimal), largest share first.Pattern: SUM() OVER () · percent of totalPro
- Largest charge per accountReturn account_id, id, merchant, amount and txn_date of each account's largest transaction, exactly one row per account. If amounts tie, keep the one with the higher id. Sort by account_id.Pattern: ROW_NUMBER() · deterministic tie-breakerPro
Dates and trends
- Monthly spendReturn month (formatted YYYY-MM) and total_spend (sum of amount) for each month, oldest first.Pattern: to_char · GROUP BY monthPro
- Month-over-month growthReturn month (YYYY-MM), total_spend and pct_change (percent change from the previous month, rounded to 1 decimal; NULL for the first month), oldest first.Pattern: CTE + LAG · growth ratePro
- Fast convertersReturn username and days_to_purchase (days from the user's created_at to their purchase event) for users who purchased within 7 days (day 7 counts). Fastest first, ties by username.Pattern: JOIN + date differencePro
- Consecutive-day loginsReturn the distinct user_id of every user with logins on two consecutive days. Sort by user_id.Pattern: LAG() · date arithmeticPro
- Longest login streakReturn user_id and longest_streak (most consecutive days logged in) for every user. Longest first, ties by user_id.Pattern: Gaps and islandsPro
Interview tip: Say your assumptions out loud before you type: what counts as a customer, whether refunds count, how ties should rank. Interviewers grade the reasoning as much as the query.