Search⌘ K
AI Features
Home
Courses
Grokking the Data Science Interview

How SQL Rounds Are Scored

Understand how SQL interviews in data science evaluate metric queries using criteria like correctness, handling duplicates, and clear reasoning. Learn to clarify assumptions, identify data grain, and validate multi-step queries before presenting results to improve accuracy and communication.

We'll cover the following...

In a SQL interview round, candidates typically write metric queries in real time and explain their approach and reasoning. This chapter covers interview questions such as:

  • Aggregation: “Compute average order value per user segment.”

  • Joins: “What is monthly revenue per active user?”

  • Multi-step metrics: “For each signup cohort month, what are day-7 retention and average revenue per user by day 30?”

  • Ranking: “For each product category, return the top 2 products by revenue last week.”

  • Time series: “Compute daily revenue and a 7-day rolling average.”

A SQL query can execute successfully but still return incorrect results. This lesson explains how SQL solutions are evaluated and introduces a process for identifying and correcting errors before presenting the results.

What makes a data science SQL round different

Data science SQL interviews typically focus on query logic and correctness rather than advanced data definition language (DDL) commands or database performance tuning. Candidates must ensure their metric queries use the correct population, time window, and unit of analysis.

A backend engineering interview may focus on application behavior, database schemas, transactions, and service interfaces. A database administrator (DBA) interview may focus on database operations, indexing, access permissions, and backup and recovery procedures. In contrast, data science SQL interviews often focus on business metrics such as retention, revenue per active user, and funnel conversion. These queries can contain logical errors that are difficult to detect. A query can execute successfully while unintentionally excluding users with no recorded events, double-counting duplicate events, or joining tables without accounting for differences in granularity, potentially duplicating records and inflating metrics.

A key concept in this chapter is data grain, which defines what each row represents at a specific stage of a query. Many common SQL errors occur when joins or aggregations change the data granularity unexpectedly, leading to duplicated records or incorrect metrics.

What interviewers score

Throughout this chapter, we'll evaluate SQL interview answers using three criteria. The following examples compare effective and ineffective responses to a retention question for each criterion:

  • Correctness means the query returns the metric the prompt defines. An effective response identifies users who signed up in January as the retention cohort, rather than users who generated events in January. An ineffective response reports a retention percentage without defining which users are included in the denominator.

  • Edge-case handling means the query has a stated policy for duplicates, NULL values, and incomplete records. A strong signal is saying that three qualifying events from user 101 on day 7 still contribute one retained user. A weak signal is assuming every event and key is unique without checking.

  • Reasoning out loud means the explanation follows the query’s stages. A strong signal is naming the cohort table, the user-level retention flag, and the final cohort aggregation. A weak signal is narrating syntax while never explaining why a join or count protects the denominator.

These criteria provide a framework for practice and do not represent an employer's official evaluation rubric. Use these criteria to identify unsupported assumptions, validate your reasoning, and correct errors before presenting your answer.

One prompt, two answers

The interviewer asks, "What is the 7-day retention rate for users who signed up in January?"

Compare how two candidates approach this question using the same sample tables:

Day-7 retention counts users, not event rows
Day-7 retention counts users, not event rows

Weak think-aloud: “I’ll join users to events, keep January signups with an event seven days after signup, and divide by the number of January users.”

The weak answer sounds reasonable, and the query it leads to runs without an error:

SELECT
  COUNT(*) * 1.0 / (
    SELECT COUNT(*) FROM users WHERE signup_date LIKE '2025-01%'
  ) AS retention
FROM users AS u
JOIN events AS e
  ON e.user_id = u.user_id
WHERE u.signup_date LIKE '2025-01%'
  AND e.event_date = date(u.signup_date, '+7 days');
Weak answer: The join counts event rows instead of users

On the tables above it returns 1.67, a retention rate of 167%. User 101 has three events on day 7, so the join produces three rows for one person, and COUNT(*) counts event rows as if they were people. The weak answer never stated the data grain, so nothing in it catches the problem. On real data the error is usually smaller, a percentage that looks plausible and is simply too high, which makes it harder to spot.

Strong think-aloud: “January signups are the cohort, so unless you want to add an eligibility rule, the denominator is every user who signed up in January, whether or not they came back. A user is retained if they have at least one event on day 7. Before I join anything, I’ll check that users has one row per user_id, and I’ll collapse events to one row per retained user, because a user can have several events on the same day.”

The effective response organizes the candidate's reasoning into the following steps:

WITH january_cohort AS (
  -- one row per January signup
  SELECT user_id, signup_date
  FROM users
  WHERE signup_date >= '2025-01-01'
    AND signup_date < '2025-02-01'
),
day7_active AS (
  -- one row per cohort user with at least one event on day 7
  SELECT DISTINCT e.user_id
  FROM january_cohort AS c
  JOIN events AS e
    ON e.user_id = c.user_id
   AND e.event_date = date(c.signup_date, '+7 days')
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(d.user_id) AS retained_users,
  1.0 * COUNT(d.user_id) / COUNT(*) AS day7_retention
FROM january_cohort AS c
LEFT JOIN day7_active AS d
  ON d.user_id = c.user_id;
Strong answer: Staged CTEs keep one row per cohort user

It returns a cohort of 3, 3 retained users, and a retention of 1.0. Every January user in the sample was active on day 7. The LEFT JOIN keeps users who never came back in the denominator, and DISTINCT makes user 101 count once. Clearly defining the metric assumptions and data grain helps interviewers evaluate whether the query includes the correct users in the denominator, applies the appropriate event date filters, and handles duplicate activity correctly.

The interviewer then asks follow-up questions, which the candidate can address using the same structured approach:

  • “Does day 7 mean exactly the seventh day, or any day in the first week?” That changes the ON condition in day7_active, not the denominator.

  • “What if signup_date is a timestamp?” Wrap both sides in date(...) so a signup at 23:00 still lands on the right day.

  • “What if a user has no events at all?” They stay in january_cohort, and the LEFT JOIN counts them as not retained.

A routine for defended metric queries

The strong answer followed a routine, and the same five steps prevent most early errors on any metric prompt:

  1. Clarify the schema and keys: What do signup_date, event_date, and user_id mean, and does users.user_id uniquely identify a person?

  2. State the data grain: Say what one row represents at each stage, such as one row per cohort user for the retention flag.

  3. Build in CTEs: Give each stage a name so it can be inspected on its own.

  4. Validate row counts: Count rows and distinct keys after every join or aggregation.

  5. Explain the result and its edge policies: Say what the number means and how duplicates, NULL values, and late events were handled.

This process builds on existing SQL knowledge by encouraging candidates to define their assumptions before writing queries. The following table compares each step with common mistakes in the earlier response and identifies the output produced at each stage.

How SQL Rounds Are Scored: Routine Step Comparison

Clarify schema and keys

Starts querying without confirming relationships or identifiers

Explicit key and schema notes

State the grain

Mixes users, events, or cohorts without naming the unit

One-sentence grain statement

Build in CTE stages

Writes one opaque query with no inspectable phases

Layered CTE plan

Sanity-check row counts and join cardinality

Trusts joins without checking duplication or row loss

Row-count and join-cardinality checks

Explain result and edge policies

Leaves NULLs, duplicates, and late events implicit

Result explanation and documented edge policies

Do you find this helpful?

This table turns each step into an artifact that can be inspected. A query written in stages gives us places to count rows, compare distinct keys, and catch a changed denominator before the final percentage looks convincing.

Five-step rule: State the cohort and grain before writing the final aggregation. Check every stage that can add, remove, or duplicate users.

The last step matters because a correct result still needs a defended policy. If an event arrives after the reporting cutoff, do we include it? If event_date is NULL, can the user qualify as retained? If the events table records the same activity twice, do we deduplicate or count activity as observed? The prompt may answer some of these questions. When it does not, we should state a reasonable policy and explain how it changes the metric.

What the rest of this chapter covers

Each subsequent lesson focuses on a common error that can cause an otherwise valid SQL query to produce incorrect results:

  • Aggregations and GROUP BY: COUNT(column_name) does not count rows where column_name is NULL, so it can change a denominator without visibly filtering records.

  • Joins Deep Dive: A fan-out join can turn one order and several event rows into several copies of the same revenue.

  • Subqueries and CTEs: A correlated subquery can obscure a metric’s intermediate grain and make a multi-step answer difficult to audit.

  • Ranking Functions: A window-function result cannot be filtered in WHERE, and the choice between ROW_NUMBER, RANK, and DENSE_RANK is a tie policy.

  • Running Totals and Moving Averages: Default window frames can include rows with equal ordering values, called peers, and missing calendar dates can change what a “7-day” window means.

  • Practice Set, Metrics and Cohorts and Practice Set, Window Functions and Joins: Combine these traps on new schemas, the way an interviewer escalates a prompt.

Find your starting point

Use the following diagnostic quiz to identify areas that need additional practice rather than to assess your overall performance. Each question corresponds to a preceding lesson, allowing you to revisit the relevant concepts when you answer incorrectly.

SQL Round Scoring Check

Q1. What is wrong with COUNT(column) when the column contains NULLs?

Do you find this helpful?

The result should tell us where the first hidden assumption sits: counting, joining, staging, ranking, or framing.