SQL Mock Interview Questions in 2026 — Practice Prompts, Answer Structure, and Scoring Rubric

SQL mock interview questions in 2026 are less about memorizing syntax and more about proving you can reason from messy business questions to correct, testable queries. Interviewers still ask joins, windows, aggregation, and retention questions, but strong candidates also clarify grain, handle duplicate events, choose the right timestamp, and explain edge cases. This guide gives you practice prompts, an answer structure, and a scoring rubric for analytics, product, data science, and analytics engineering interviews.

SQL mock interview questions in 2026: what interviewers are testing

The interviewer wants to know if you can convert an ambiguous request into a query that answers the actual business question. That means you need more than “SELECT, JOIN, GROUP BY.” You need to understand tables, entities, event grain, time windows, null behavior, attribution, and whether your result can be trusted.

A typical SQL interview now includes one or more of these patterns:

• Aggregate by user, account, product, day, cohort, or marketplace side.
• Join facts to dimensions without multiplying rows accidentally.
• Use window functions for ranking, deduplication, cumulative totals, or previous events.
• Calculate funnels, retention, churn, activation, conversion, or revenue.
• Debug a flawed query or explain why a metric changed.
• Translate a product question into both query logic and business caveats.

In 2026, many companies also ask SQL alongside experimentation, metrics, or Python. They may care less about dialect-specific perfection and more about whether your logic is sound. If you forget exact syntax for date arithmetic, say the intent and use clear pseudocode. Do not hand-wave grain or edge cases.

A repeatable SQL answer structure

Use this structure before writing code. It makes your solution easier to follow and reduces bugs.

1. Clarify the business question. What exactly should the output show? What time range? What population? What counts as conversion, activation, churn, or revenue?
2. Inspect the schema. Identify tables, primary keys, foreign keys, timestamps, status fields, and whether records are events, snapshots, or dimensions.
3. State the grain. Say the grain of the final output and of each intermediate CTE: one row per user, order, account-day, session, or event.
4. Plan the joins. Decide which table is the base. Watch for one-to-many joins that duplicate revenue or events.
5. Filter carefully. Apply time windows, status exclusions, test users, bot traffic, cancellations, and null handling explicitly.
6. Build in CTEs. Use CTEs for readability: base population, cleaned events, aggregated metrics, final output.
7. Use windows when needed. Ranking, first event, previous event, deduplication, rolling counts, and cohort calculations often need windows.
8. Validate. Explain how you would sanity check row counts, totals, duplicates, and sample users.

A strong opening sounds like: “I’ll first define the grain and conversion event, then build the eligible user set, join the events at user level, and aggregate by cohort week.” That is much better than silently writing a query.

Scoring rubric for SQL mock interviews

| Dimension | 1-2: weak signal | 3: adequate | 4-5: strong signal | |---|---|---|---| | Clarification | Starts coding immediately | Asks basic schema questions | Clarifies metric definition, population, time window, and output grain | | Data modeling | Confuses events and entities | Understands tables but misses grain risks | Identifies keys, grain, join direction, and row multiplication risk | | SQL correctness | Syntax-heavy but wrong logic | Mostly correct with some edge gaps | Correct query structure, filters, aggregations, windows, and null handling | | Edge cases | Ignores duplicates and missing data | Mentions one or two | Handles duplicates, cancellations, time zones, late events, and test data | | Communication | Silent coding | Explains pieces | Narrates reasoning clearly and validates assumptions | | Testing | No sanity checks | Checks final output only | Checks intermediate counts, sample records, and reconciliation totals | | Business judgment | Answers literal query only | Some business interpretation | Explains caveats and how the result should be used |

Practice prompt bank

Use these prompts with a timer. Give yourself 25 minutes for a full solution or 12 minutes for a whiteboard outline.

1. Find each user’s first purchase date and total revenue in the first 30 days after signup. Watch for canceled orders and timezone boundaries.
2. Calculate weekly retention for users who signed up in January. Define active behavior clearly and avoid counting signup as retention unless specified.
3. For each product category, find the top three products by revenue last month. Use window functions and decide how to handle ties.
4. Compute checkout conversion by acquisition channel. Join sessions, users, and orders without double counting users with multiple sessions.
5. Find sellers whose response time worsened by more than 20% week over week. Think about medians versus averages and outliers.
6. Calculate the seven-day activation rate for a B2B product. Define activation as completing two key actions within seven days of account creation.
7. Deduplicate event logs where the same event_id may appear multiple times. Keep the latest ingestion record or the earliest event timestamp depending on the metric.
8. Identify users who purchased in March but not April. Use anti-joins or conditional aggregation.
9. Calculate rolling seven-day active users. Be careful: summing daily active users is not the same as unique users over seven days.
10. Find the second order for every customer and the days between first and second order. Use LAG or ROW_NUMBER.
11. Build a funnel from product view to cart to purchase. Decide whether the funnel is event-level, session-level, or user-level.
12. Calculate net revenue after refunds and discounts. Do not subtract refunds twice if refund rows already have negative amounts.
13. Find accounts with at least five active seats in each of the last four weeks. This tests grouping, calendar logic, and account-level grain.
14. Rank creators by revenue per active follower. Handle creators with small denominators and inactive followers.
15. Debug a query that joins orders to order_items and then sums order_total. Explain why revenue is inflated.
16. Calculate trial-to-paid conversion by signup cohort and plan. Include trials that have not had enough time to convert as a caveat.

Worked pattern: first purchase and 30-day revenue

Assume tables: users(user_id, signup_at, acquisition_channel), orders(order_id, user_id, order_at, status, gross_revenue, discount_amount). The business question is: for users who signed up in Q1, what was their first purchase date and net revenue within 30 days of signup?

A strong answer clarifies that net revenue excludes canceled orders and subtracts discounts. It also says whether the 30-day window is inclusive of the signup timestamp and whether time zones matter. Then it writes a readable query:

WITH q1_users AS (
  SELECT user_id, signup_at, acquisition_channel
  FROM users
  WHERE signup_at >= DATE '2026-01-01'
    AND signup_at < DATE '2026-04-01'
),
valid_orders AS (
  SELECT
    order_id,
    user_id,
    order_at,
    gross_revenue - COALESCE(discount_amount, 0) AS net_revenue
  FROM orders
  WHERE status = 'completed'
),
orders_in_window AS (
  SELECT
    u.user_id,
    u.acquisition_channel,
    o.order_at,
    o.net_revenue
  FROM q1_users u
  LEFT JOIN valid_orders o
    ON u.user_id = o.user_id
   AND o.order_at >= u.signup_at
   AND o.order_at < u.signup_at + INTERVAL '30 days'
)
SELECT
  user_id,
  acquisition_channel,
  MIN(order_at) AS first_purchase_at,
  COALESCE(SUM(net_revenue), 0) AS revenue_30d
FROM orders_in_window
GROUP BY user_id, acquisition_channel;

Then validate. Count Q1 users before and after the left join. Confirm users with no orders still appear with zero revenue. Reconcile total 30-day revenue against a broader order query for the same population. Sample a few users manually. Mention that if orders can have multiple rows per status update, you need to deduplicate to one row per order before summing.

Worked pattern: weekly retention

Retention prompts are where many candidates lose points because they skip definitions. Ask: retention of users or accounts? Active means login, meaningful action, purchase, or any event? Week zero included? Calendar weeks or rolling seven-day windows? For product analytics, cohort week usually starts at signup week, and retention week one means activity in the week after signup week.

A clear outline:

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', signup_at) AS cohort_week
  FROM users
  WHERE signup_at >= DATE '2026-01-01'
    AND signup_at < DATE '2026-02-01'
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('week', event_at) AS active_week
  FROM events
  WHERE event_name = 'meaningful_action'
),
cohort_activity AS (
  SELECT
    c.cohort_week,
    DATE_DIFF('week', c.cohort_week, a.active_week) AS week_number,
    c.user_id
  FROM cohorts c
  JOIN activity a
    ON c.user_id = a.user_id
   AND a.active_week >= c.cohort_week
)
SELECT
  cohort_week,
  week_number,
  COUNT(DISTINCT user_id) AS retained_users
FROM cohort_activity
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;

Dialect syntax varies, but the logic matters: assign users to cohorts, define meaningful activity, calculate week offset, count distinct users. To get retention rate, join to cohort sizes and divide. Add a caveat for immature cohorts that have not yet reached later weeks.

Common SQL traps

The biggest trap is row multiplication. If you join orders to order_items and then sum orders.order_total, an order with three items may count three times. Sum item-level revenue if using item rows, or aggregate items to order grain before joining.

Another trap is counting events instead of users. If the question asks for conversion rate, decide whether the denominator is users, sessions, accounts, or opportunities. Counting purchase events divided by page-view events may not answer the business question.

A third trap is using inner joins accidentally. If you need users with zero purchases, use a left join from the user population to orders. An inner join silently removes non-converters and makes conversion look like 100% in some queries.

A fourth trap is mishandling time. Use half-open intervals, such as >= start and < end, to avoid double-counting midnight boundaries. Ask whether timestamps are UTC or local. For subscription billing, billing date and event date may not be the same.

A fifth trap is ignoring late-arriving or duplicate data. Event pipelines can reprocess records. Payment systems can update status after authorization. Logs can contain retries. State your deduplication rule.

Seven-day SQL prep plan

Day 1: Drill joins and grain. Take five schemas and state the grain of every table. Identify dangerous one-to-many joins.

Day 2: Practice aggregations. Write queries for revenue, conversion, active users, average order value, and cohort size. Explain denominators out loud.

Day 3: Practice window functions: ROW_NUMBER, RANK, LAG, LEAD, SUM over partitions, and rolling counts. Focus on when a window is better than a group by.

Day 4: Practice funnels and retention. Write user-level, session-level, and account-level versions so you can explain the difference.

Day 5: Debug flawed queries. Create row multiplication, inner-join, null, and time-window bugs; then fix them.

Day 6: Run two live mocks. Narrate before coding. The interviewer should hear your assumptions, not just watch typing.

Day 7: Build a personal checklist: clarify metric, define grain, choose base table, join safely, filter explicitly, aggregate, validate, explain caveats.

SQL interviews reward candidates who are careful before they are clever. If you clarify the metric, protect grain, write readable CTEs, handle edge cases, and validate the result, you will outperform candidates who know more syntax but trust the wrong query.

Related guides

• API Design Mock Interview Questions in 2026 — Practice Prompts, Answer Structure, and Scoring Rubric — Prepare for API design interviews with realistic prompts, REST and event-driven tradeoffs, pagination, idempotency, auth, versioning, rate limits, and a practical scoring rubric.
• AWS Mock Interview Questions in 2026 — Practice Prompts, Answer Structure, and Scoring Rubric — Use these AWS mock interview prompts, answer frameworks, scoring criteria, architecture examples, and drills to prepare for cloud engineering and senior backend interviews.
• Backend System Design Mock Interview Questions in 2026 — Practice Prompts, Answer Structure, and Scoring Rubric — Backend system design practice for 2026 with API, data, consistency, queueing, reliability, and operations prompts plus a senior-level scoring rubric.
• Behavioral Interviewing Mock Interview Questions in 2026 — Practice Prompts, Answer Structure, and Scoring Rubric — Prepare for behavioral interviews with a practical story bank, STAR-plus answer structure, scoring rubric, realistic prompts, and a 7-day mock plan.
• Data Modeling Mock Interview Questions in 2026 — Practice Prompts, Answer Structure, and Scoring Rubric — A 2026 data modeling mock interview guide with schema prompts, relationship modeling, tradeoff examples, scoring rubric, drills, and a 7-day prep plan.