Sample report

Sample data for a fictional student, from a scripted interview. The report was written by the same pipeline that writes yours.

SQL and pandas interview

Data Science, regular level, two SQL questions and one pandas question, run on their data, Oct 22, 2026

Hire

If you read nothing else

At the Regular bar in this interview.

Strength: You hand-computed expected results before writing code and used failing comparisons to isolate bugs yourself in all three questions.
by hand, the January first cohort is users 1 and 2, and only 1 comes back on the second, so that's a halfYour words, 2:05
Strength: You asked precise clarifying questions (calendar day vs 24 hours, unknown segments, attribution window, duplicate-variant users) that changed the implementation.
day 1 means exactly the next calendar day, not within twenty-four hours?Your words, 0:42
Gap: Commit to a default, then caveat.
I'm honestly not sure which one a team would pick by default.Your words, 36:20

One moment from your delivery

You hesitated

In Q2 you hesitated: you took 11.5 s to start (your usual 3.0 s); a 4.4 s pause mid-answer; 8 fillers per minute (your usual 2): 'um' (2), 'hmm' (1), 'uh' (1); you slowed to 102 words per minute (your usual 148).

Time to start 11.5 s vs your usual 3.0 s
Longest pause 4.4 s
Fillers per minute 7.9 vs your usual 1.7 (“um”, “hmm”, “uh”)
Words per minute 102 vs your usual 148

Um, maybe the merge on customer id fails because, uh, one side is float?Your words, 19:32

Try: Start with a one-line headline ('My approach is X because Y') and think aloud from there instead of in silence.

Your next task, 20 min

Commit to a default, then caveat

Answer three open-ended prompts out loud in 60 seconds each — heavy-tailed revenue, scaling to 2 billion rows, rolling vs classic retention — always starting with 'My default would be...' before any caveat.

What happened

You worked through all three questions: day-1 retention in SQL, a pandas segment average, and an A/B test analysis. You checked results against hand-computed numbers, caught your own inner-join and merge bugs, and reasoned carefully about censored cohorts and NaN keys. The main takeaway: your debugging and validation instincts are solid; the weaker spots were scaling design and choosing a method for heavy-tailed revenue.

At the bar

You are at the Regular bar on correctness, debugging and communication; the remaining gaps are depth on production scaling and on picking a defensible method for skewed metrics.

You answered all 3 planned questions.

After that

More ways to practice, in order of impact.

1
Predict the result before you run: Before each query, write the expected rows on paper: cohort count, converter count, one tricky user. Then run and diff. Do it for three queries, 15 minutes.
2
Experiment analysis: You gave the absolute difference; also state the lift relative to control as a percentage, since that is what teams compare across tests.

Scorecard

Each skill from the rubric, scored 1 to 5, with the moments it was based on.

Your approach was sound and you debugged well, but on two analysis questions you listed options and stopped instead of committing to a default with a reason. Say what you would ship and why, even if you flag the alternative.
The inner join only keeps users who came back on day 1, so the cohort becomes just the retained users.Your words, 5:47
the newest cohorts can't have day-30 data yet, so those should be null, not zeroYour words, 9:06
What good looks like: You would state the approach and its edge cases before coding, then extend it to d1/d7/d30 without being prompted, naming the censoring rule up front.
To reach 5: finds an efficient approach independently and justifies it.
Before writing code next time, say your plan and the two edge cases you expect in one sentence each.
The final code is correct and idiomatic, and you deliberately chose NULL over zero for censored cohorts, which is the right call. Add a quick check for NaN keys after every left join so you catch the drop-key trap without being prompted.
I'd add validate many to one on the merge, pandas raises if the right side has duplicate keysYour words, 22:58
three orders, one missing, and the mean is over the two that have an amountYour words, 21:39
What good looks like: Clean, idiomatic code with the guard rails you added plus explicit dtype handling (nullable Int64 for ids) and a named function instead of a script.
To reach 5: clean, correct, idiomatic and handles edge cases.
Rewrite the pandas answer as a small function with the cutoff as a parameter and Int64 casts on both id columns.
You reason about scale correctly once asked, but your answers stay at the level of options. Name the partition column (order_date, by day) and say what it buys you, and quantify the cost of a pandas groupby versus a distributed one.
I'm less sure how I'd pick the partitioning if I had to set it up myself.Your words, 24:25
the normal approximation breaking with, you know, three and four usersYour words, 34:20
What good looks like: You would name the partition key and pruning explicitly, and say which method you would default to for skewed revenue and why, with its trade-off.
To reach 5: precise analysis and identifies better alternatives and their trade-offs.
Practise one scaling answer: partition by date, filter before join, broadcast the small table, and say what breaks at 2 billion rows.
You proactively built expected values and checked edge cases like the empty window and zero conversions. Write your join-integrity checks (unique keys, row counts) into the first draft rather than after the reviewer asks.
by hand, the January first cohort is users 1 and 2, and only 1 comes back on the second, so that's a halfYour words, 2:05
If I were dividing by something from a different table, I'd wrap it in a NULLIF.Your words, 8:35
What good looks like: You would write the expected output down before running, then add cases for empty tables, duplicate keys and boundary dates without the interviewer asking.
To reach 5: proactively builds test cases, finds own bugs.
For each question, list three test cases (empty, boundary date, duplicate key) before you start coding.
Your narration and clarifying questions are strong and make your reasoning easy to follow. When you get stuck, state the hypothesis and the test you will run instead of talking through candidates.
day 1 means exactly the next calendar day, not within twenty-four hours?Your words, 0:42
The user is the unit, not the purchase.Your words, 27:03
What good looks like: You would keep narrating while debugging, state the plan before each block of code, and summarise the result in one sentence at the end of each question.
To reach 5: narrates reasoning, asks good clarifying questions, structured throughout.
After each query passes, say one sentence on what the numbers mean before moving on.

Also went well

Your final code carries real defensive habits: validate="many_to_one" with a row-count assert, NULLIF reasoning, NULL for censored cohorts, user as the unit of randomization.

Also to improve

Check for NaN group keys after a left join: groupby drops them by default, which is why your unknown row vanished and cost a hint (turn 48-50).
Commit to a recommendation on open analysis choices instead of listing options: what you would use by default for heavy-tailed revenue and how you would pick the partition key.
Make join-integrity checks (unique keys, post-join row count) part of your first draft, not a response to a prompt.

Red flags

Chased a dtype theory for the missing unknown row (turn 48) instead of testing whether NaN keys were being dropped; needed the interviewer's hint to unblock.
Hard-coded the counts (4,1) and (3,2) in the Python statistics instead of reading the query output, which would not survive a rerun on full data.

Question by question

Open a question to see which key points you covered, in your own words, and what a stronger answer sounds like.

Your answer: You clarified the cohort definition, worked out the expected answer by hand from the eight rows, then wrote a first-login CTE left-joined to logins on cohort date plus one. You caught that your inner join shrank the cohort, fixed it, and extended the query to day 1, 7 and 30 with nulls for censored cohorts.
Key points
First-login CTE, left join on cohort date plus one (Covered)
a CTE with the minimum login date per user, that's the cohort. Then I join back to logins on the same user and the cohort date plus one.Your words, 2:53
Rate is retained over cohort size, guard the division (Covered)
every cohort row exists because at least one user has that first login, so the cohort size is at least oneYour words, 8:35
Censored recent cohorts get null, not zero (Covered)
the newest cohorts can't have day-30 data yet, so those should be null, not zero, otherwise it looks like everybody churnedYour words, 9:06
Follow-up questions
Walk through what the left join produces for user 4, who never returned Handled wellYou explained the null row: counted in the denominator, skipped in the numerator.
Can the denominator be zero or null, and what would you do Handled wellYou said cohort size is at least one and you would use NULLIF when dividing by another table.
Extend to day 1, 7 and 30 retention in one query Handled wellYou moved the day condition into conditional counts and nulled the censored cohorts.
Difference between classic and rolling retention Partly handledYour definition was roughly right but hedged; say it plainly: rolling counts a user active on day N or any later day.
A stronger answer
I'd define the cohort as each user's first login date, so a CTE with user_id and MIN(login_date) grouped by user. Then I'd left join back to logins on the same user_id and login_date equal to cohort_date plus one, and count distinct users on both sides so the rate is retained users over cohort size. The left join matters: with an inner join the cohort shrinks to only the users who came back, and a cohort where nobody returned disappears entirely. I'd check it against a hand calculation first, which here gives half, half and zero for the three cohorts. For day 1, 7 and 30 in one query I'd join on user_id only and use conditional COUNT DISTINCT with CASE for each day. Recent cohorts can't have day-30 data yet, so I'd compare cohort date plus 30 against the max login date and return null rather than zero, otherwise it reads as everyone churning. And I'd keep COUNT DISTINCT on the logins side as insurance in case the table ever holds raw events rather than one row per user per day.
Common mistakes on this question
Joining to day-1 logins with an inner join, which shrinks the cohort to the users who returned and drops cohorts with no returners.
Reporting zero for day-7 or day-30 on recent cohorts that have not had time to mature, instead of null.
Your answer: You filtered to the last 90 days from the max order date, merged customers with a left join, filled missing segments with unknown and aggregated mean amount, order count and missing-amount count. You debugged the missing unknown row down to groupby dropping NaN keys, then added validate many_to_one and a row-count assert.
Key points
Date filter, left merge, fill unknown, mean plus counts (Covered)
So the float thing was a red herring. I'll fill the segment with unknown before groupingYour words, 20:29
Merge cardinality check and id/date dtypes (Covered)
If a customer id appeared twice in customers, every one of their orders would be doubled and the counts would be wrong without any error.Your words, 22:58
Report missing data instead of dropping it silently (Covered)
So three orders, one missing, and the mean is over the two that have an amountYour words, 21:39
Follow-up questions
What do you expect the unknown row to be before you change the merge Handled wellYou predicted 50 from two orders, which made the missing row easy to spot.
What groupby does with NaN keys by default Handled wellYou took the hint, named dropna=False, then fixed it by filling the segment first.
How to check the merge did not duplicate orders Handled wellYou added validate='many_to_one' and an assert on the row count.
How to scale this to a 2 billion row orders table Partly handledPush-down and a broadcast join were right; you left partitioning open, so name date partitioning on the filter column.
A stronger answer
I'd read both files with order_date parsed as a date, take the cutoff as the max order_date minus 90 days and keep orders strictly after it. Then a left merge to customers so orders with no matching customer survive, and fill the segment with unknown before grouping, which covers both the unmatched orders and customers with an empty segment. The aggregation is mean of amount, which skips NaN, count of order_id for n_orders including the missing amounts, and a count of the nulls in amount so the excluded rows stay visible. I'd add validate many_to_one on the merge and assert the row count is unchanged, because a duplicate customer row would silently double that customer's orders. The trap I hit was that groupby drops NaN keys by default, so the unknown row vanished until I filled the segment first. For a two-billion-row fact table I'd push the filter and aggregation into the warehouse SQL, filter on date first so partitions prune, and join the small customers table afterwards.
Common mistakes on this question
Using the default inner merge, which silently drops orders whose customer is missing instead of reporting them under unknown.
Filling missing amounts with zero before averaging, which pulls the average down instead of excluding them.
Hints used: 1
Your answer: You deduped exposures to one variant per user, joined purchases inside a 7-day window after first exposure, and counted users and converters per variant. In Python you built a two-proportion interval and a chi-square split check, and you flagged that the interval is meaningless at seven users.
Key points
Dedupe users, one variant each, purchases inside the window (Covered)
group exposures by user, keep only users with exactly one distinct variant, and take their first exposure timeYour words, 27:03
SRM chi-square, two-proportion interval, absolute and relative lift (Partly)
two-proportion, normal approximation, so the standard error is, uh, the square root of p times one minus p over n for each groupYour words, 32:16
You gave the absolute difference; also state the lift relative to control as a percentage, since that is what teams compare across tests.
Novelty, multiple metrics, power and guardrails (Covered)
Whether the lift holds over the weeks or it's a novelty effect that's fading, so plot it by dayYour words, 37:11
Follow-up questions
Is the DISTINCT on the purchases side doing anything in this data Handled wellYou traced user 1's two purchases and showed the converter count would double.
Treatment is 52% of users on a large test, is that a problem Handled wellYou scaled the chi-square to the sample size and called it SRM at a hundred thousand users.
How to handle heavy-tailed revenue per user Partly handledYou listed bootstrap, capping and Mann-Whitney but didn't pick one; say which you'd default to and why.
What else you look at before recommending a launch Handled wellGuardrails, novelty by week, multiple metrics and the dropped users all came up.
A stronger answer
I'd clean exposures first: group by user_id, keep only users with exactly one distinct variant, and take MIN(exposed_at) as the first exposure, so users who saw both variants are excluded. Then left join purchases on user_id with purchased_at greater than or equal to the first exposure and less than exposure plus seven days, both bounds, otherwise a purchase before exposure counts as a conversion. I'd count distinct users and distinct converters per variant, because the user is the randomised unit, not the exposure or the purchase. In Python I'd take those counts, compute the difference in proportions with a normal-approximation interval, and run a chi-square on the split against 50/50 to check for sample ratio mismatch. With four and three users the interval is not usable, so I'd say there's no conclusion and suggest Fisher's exact test. For a real test at scale, a 52/48 split on a hundred thousand users would be a clear SRM and I wouldn't trust the result until I found the cause. Before recommending a launch I'd look at guardrails like refunds and revenue per user, check the lift by week for novelty effects, and account for how many users the both-variants bug removed.
Common mistakes on this question
Putting only the upper bound on the attribution window, so a purchase made before exposure counts as a conversion.
Counting exposures or purchases instead of users, which breaks independence and inflates the sample size.

Your code, each issue with the line it is about, and the complexity you said against the actual one.

1Given the logins table in your workspace, calculate day-1 retention: the share of users who logged in on the day after their first login, for each signup date.

Your code, sent at 12:12

1WITH first_login AS (2    SELECT user_id, MIN(login_date) AS cohort_date3    FROM logins4    GROUP BY user_id5),6last_day AS (7    SELECT MAX(login_date) AS max_date FROM logins8)9SELECT f.cohort_date,10       COUNT(DISTINCT f.user_id) AS cohort_size,11       COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1 THEN l.user_id END) * 1.012           / COUNT(DISTINCT f.user_id) AS d1,13       CASE WHEN MAX(d.max_date) >= f.cohort_date + 714            THEN COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 7 THEN l.user_id END) * 1.015                 / COUNT(DISTINCT f.user_id) END AS d7,16       CASE WHEN MAX(d.max_date) >= f.cohort_date + 3017            THEN COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 30 THEN l.user_id END) * 1.018                 / COUNT(DISTINCT f.user_id) END AS d3019FROM first_login f20CROSS JOIN last_day d21LEFT JOIN logins l ON l.user_id = f.user_id22GROUP BY f.cohort_date23ORDER BY f.cohort_date;24

Bugline 11Day 1 is not censored the way day 7 and day 30 are. For a cohort whose first login is the last day in the table, no day-1 data exists yet, so this expression returns 0 and the cohort looks like it churned completely, while d7 and d30 correctly return null.
```
11COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1 THEN l.user_id END) * 1.0
```
Fix: Wrap d1 in the same CASE you used for d7 and d30: only compute it when MAX(d.max_date) >= f.cohort_date + 1, otherwise return null.
Styleline 20The max date is carried through a one-row CROSS JOIN and then read with MAX(d.max_date) inside each CASE. It works, but it makes the query harder to read than it needs to be and hides that the value is a single scalar.
```
20CROSS JOIN last_day d
```
Fix: Use a scalar subquery, e.g. (SELECT MAX(login_date) FROM logins), directly in the CASE conditions, or keep the CTE but reference it once in a WHERE-free scalar expression.

Complexity: time O(n) over the logins rows: one group by , space O(u) for the distinct users per cohort

You never stated a complexity for this query, and the shape is a single pass plus a join, which is fine for this size.

Edge cases to check

A cohort whose first login is the maximum date in the table: Day 1 cannot be observed yet, but your d1 column would show 0 instead of null, which reads as total churn.
A user whose only later login is on day 2 or day 3, not day 1: Worth confirming explicitly that they land in the denominator only; your COUNT DISTINCT on the logins side does this, but the d1 column has no null guard for the newest cohort.

A cleaner outline

CTE 1: first login per user (MIN of login_date, grouped by user).
CTE 2: the maximum login_date in the table, as a single scalar.
Left join first_login to logins on user_id only, so every login of the user is available for the conditional counts.
cohort_size = COUNT(DISTINCT user_id) from the cohort side.
For each of day 1, 7 and 30: a CASE that returns null when the max date is before cohort_date + N, otherwise COUNT(DISTINCT user_id) filtered to that exact day, divided by cohort_size.
Group by cohort_date, order by cohort_date.

2You have an orders DataFrame and a customers DataFrame. Compute the average order value per customer segment for the last 90 days, handling missing values sensibly.

Your code, sent at 23:57

1import pandas as pd2 3orders = pd.read_csv("data/orders.csv", parse_dates=["order_date"])4customers = pd.read_csv("data/customers.csv")5 6cutoff = orders["order_date"].max() - pd.Timedelta(days=90)7recent = orders[orders["order_date"] > cutoff]8 9df = recent.merge(customers, on="customer_id", how="left", validate="many_to_one")10assert len(df) == len(recent)  # the merge must not duplicate orders11df["segment"] = df["segment"].fillna("unknown")12out = (13    df.groupby("segment")14    .agg(avg_amount=("amount", "mean"),15         n_orders=("order_id", "count"),16         n_missing_amount=("amount", lambda s: s.isna().sum()))17    .reset_index()18    .sort_values("segment")19)20print(out.to_csv(index=False), end="")21

Riskline 15n_orders counts non-null order_id values, not rows. If any order has a null order_id it silently disappears from the count, even though the requirement is all orders of the segment.
```
15n_orders=("order_id", "count"),
```
Fix: Count rows instead of a column: use .size() per group, or add a helper column of ones and sum it, so every order is counted regardless of nulls.
Styleline 16The missing-amount count is a Python lambda inside agg, which is slower and less readable than the built-ins and will not translate if this is later pushed into SQL or a bigger pipeline.
```
16n_missing_amount=("amount", lambda s: s.isna().sum()))
```
Fix: Create a boolean column (df["amount_missing"] = df["amount"].isna()) and sum it in agg, or use a named function so the intent is visible.
Styleline 10The row-count check is a bare assert, which is stripped when Python runs with -O, so the protection you added can vanish in some environments.
```
10assert len(df) == len(recent)  # the merge must not duplicate orders
```
Fix: Keep validate="many_to_one" as the primary guard and raise an explicit exception (if len(df) != len(recent): raise ValueError(...)) instead of relying on assert.

Complexity: time O(n) for the date filter, the merge and , space O(n) for the merged frame

Edge cases to check

A real segment literally named "unknown": fillna("unknown") merges genuine unknown-segment customers with orders that had no matching customer, so the two different data-quality problems become one row you cannot separate.
An order with a null order_id: It is counted in the average but not in n_orders, so n_orders and n_missing_amount stop adding up to the segment total.

A cleaner outline

Read both files, parse order_date, and compute the cutoff as max(order_date) minus 90 days.
Filter orders to order_date > cutoff before merging, so the merge stays small.
Left merge customers with validate="many_to_one" and an explicit row-count check.
Fill a missing segment with a distinct label (e.g. "unknown") and keep a flag column for orders that had no customer match.
Group by segment and aggregate: mean of amount, row count via size, and sum of the amount-missing flag.
Reset index, sort by segment, print to CSV without the index.

3You have exposure and purchase data from an A/B test. Compute conversion rate per variant, the difference with a confidence interval, and check whether the assignment looks healthy.

Your code, sent at 34:10

1from math import sqrt2 3n_c, x_c = 4, 1   # control: users, converters (from the query)4n_t, x_t = 3, 2   # treatment5p_c, p_t = x_c / n_c, x_t / n_t6diff = p_t - p_c7se = sqrt(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t)8print(f"control {p_c:.3f} treatment {p_t:.3f} diff {diff:.3f}")9print(f"95% CI {diff - 1.96 * se:.3f} to {diff + 1.96 * se:.3f}")10 11# sample ratio check against the planned 50/50 split, chi-square with 1 df12n = n_c + n_t13chi2 = (n_c - n / 2) ** 2 / (n / 2) + (n_t - n / 2) ** 2 / (n / 2)14print(f"SRM chi-square {chi2:.3f} (cutoff 3.84 at 5%)")15

Riskline 3The Python analysis hard-codes the counts instead of reading them from the query, so the interval and the SRM check cannot be rerun on new data and can drift from the SQL result.
```
3n_c, x_c = 4, 1   # control: users, converters (from the query)
```
Fix: Fetch the per-variant users and converters into a small DataFrame (or read the query output) and compute p_c, p_t and the counts from it.
Riskline 7The normal-approximation standard error is applied at 3 and 4 users, which produces an interval that runs past 1.0. You spotted this out loud, but the code prints the interval with no guard, so anyone reading the output would take it at face value.
```
7se = sqrt(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t)
```
Fix: Add a minimum-count check before using the normal approximation, and fall back to an exact or Wilson-style interval (or Fisher's exact test) when the counts are this small.
Riskline 12The sample-ratio check is run on the post-exclusion counts, i.e. after the users who appeared in both variants were removed. That is exactly the group the bug affects, so the check can look healthy while the assignment is broken.
```
12n = n_c + n_t
```
Fix: Run the chi-square on the raw exposure counts per variant, before excluding anyone, and report separately how many users were dropped for appearing in both variants.

Complexity: time O(n) for the exposures group by and the , space O(u) for the distinct users

Edge cases to check

A purchase landing exactly on exposed_at + 7 days: Your join uses a strict upper bound, so that purchase is excluded; worth stating the boundary convention out loud so the reader knows which side of the line it falls on.
A variant with zero converters: You handled it with the left join, but the Python side would divide by a zero proportion and produce a degenerate interval; worth a guard there too.
Exposures rows with a null variant: HAVING COUNT(DISTINCT variant) = 1 would keep a user whose only variant is null, and they would form their own group in the output.

A cleaner outline

CTE: group exposures by user, keep users with exactly one distinct variant, and take MIN(exposed_at) as the first exposure.
Left join purchases on user_id with both bounds: purchased_at >= exposed_at and purchased_at < exposed_at + 7 days.
Aggregate per variant: COUNT(DISTINCT user_id) for users, COUNT(DISTINCT purchase user_id) for converters, and the ratio as a float.
In Python, read those counts into a DataFrame rather than typing them in.
Compute the difference and its interval, with a small-sample guard that switches to an exact method.
Run the SRM chi-square on the raw per-variant exposure counts, and report how many users were excluded for appearing in both variants.

Your code runs

17 runs, 9 of 13 example checks passed. Last run: ran.

Measured by the code runner, not judged.

17 runs, 3 of 4 example checks passed
26 runs, 4 of 6 example checks passed
34 runs, 2 of 3 example checks passed

Practice next: Complexity and optimization · 3 of 5

For one large table you know, write the partition key, the filter that prunes it, and the join order in five lines. Ten minutes; say it out loud as if asked in an interview.

Coaching

The few changes that matter most, with a way to practice each one.

1Communication
Commit to a default, then caveat
On revenue per user you listed bootstrap, capping and Mann-Whitney but ended without picking one, so the interviewer never heard what you would actually do. Naming a default first and the caveat second reads as judgement rather than uncertainty.
I'm honestly not sure which one a team would pick by default.Your words, 36:20
How to practice: Take three open questions (heavy-tailed revenue, 2B-row scaling, rolling vs classic retention) and answer each in 60 seconds out loud: default, one reason, one caveat. Record and check you named a default every time.
2Testing and edge cases
Predict the result before you run
Your two join bugs — the default inner merge and the missing lower bound on the purchase window — were both caught by the example comparison after the run, not by you beforehand. Predicting the numbers first turns those into catches you make yourself.
I only put the upper bound in the join. I need purchased at greater or equal to exposed at as well.Your words, 29:41
How to practice: Before each query, write the expected rows on paper: cohort count, converter count, one tricky user. Then run and diff. Do it for three queries, 15 minutes.
3Complexity and optimization
Say how you would partition the table
You correctly pushed the 2-billion-row job to the warehouse and named a broadcast join, then stopped at partitioning. That last step is what shows you can size a real pipeline, not just describe one.
I'm less sure how I'd pick the partitioning if I had to set it up myself.Your words, 24:25
How to practice: For one large table you know, write the partition key, the filter that prunes it, and the join order in five lines. Ten minutes; say it out loud as if asked in an interview.

I'm honestly not sure which one a team would pick by default.Your words, 36:20
My default would be a bootstrap of the difference in mean revenue per user, because the business cares about total revenue and the mean is what big spenders dominate. I'd report a capped mean alongside as a check.
A default plus a reason is something a teammate can act on; the caveat can come after.
I'm less sure how I'd pick the partitioning if I had to set it up myself.Your words, 24:25
I'd partition orders by order_date so the 90-day filter prunes to a few partitions, aggregate there, then join the small customers table.
You already had the right plan; naming the partition key finishes the answer.

Day 1Write down three open questions and answer each with a default in 60 seconds.10 min
Day 2Rest day, or skim your notes on groupby dropping NaN keys.5 min
Day 3Predict row counts before running three SQL queries, then diff.15 min
Day 4Rest day, no practice.
Day 5Practise the 60-second default-first answer on scaling to 2 billion rows.10 min
Day 6Re-run the retention query from memory, including the d7/d30 censoring.20 min
Day 7Record a two-minute answer on experiment results and review it.15 min

Pace, pauses, fillers and pitch, measured from your voice. Signals, not judgments.

Moments worth a look

You hesitated
In Q2 you hesitated: you took 11.5 s to start (your usual 3.0 s); a 4.4 s pause mid-answer; 8 fillers per minute (your usual 2): 'um' (2), 'hmm' (1), 'uh' (1); you slowed to 102 words per minute (your usual 148).
- Time to start 11.5 s vs your usual 3.0 s
- Longest pause 4.4 s
- Fillers per minute 7.9 vs your usual 1.7 (“um”, “hmm”, “uh”)
- Words per minute 102 vs your usual 148
Um, maybe the merge on customer id fails because, uh, one side is float?Your words, 19:32
Try: Start with a one-line headline ('My approach is X because Y') and think aloud from there instead of in silence.
Your delivery got bumpy and you sped up
In Q3 your delivery got bumpy and you sped up: 11 fillers per minute (your usual 2): 'um' (2), 'I mean' (1), 'uh' (1); you sped up to 188 words per minute (your usual 148); 2 restarted sentences.
- Fillers per minute 10.6 vs your usual 1.7 (“um”, “I mean”, “uh”)
- Words per minute 188 vs your usual 148
- Restarted sentences 2
Okay, so the difference is about point four two, and the interval goes from, uh, minus point twoYour words, 34:20
Try: Swap fillers for a short silent pause; say this answer once more out loud and listen for them.
You hesitated and used hedging phrases
When asked “Mm-hm. Conversion is a yes or no per user. Product also wants...” (Q3), you hesitated and used hedging phrases: you took 6.2 s to start (your usual 3.0 s); a 3.2 s pause mid-answer; 2 hedges such as 'probably'.
- Time to start 6.2 s vs your usual 3.0 s
- Longest pause 3.2 s
- Hedging 2 (“probably”, “not sure”)
I'd probably bootstrap the difference in means, or cap the top like the ninety-ninth percentile.Your words, 36:20
Try: Start with a one-line headline ('My approach is X because Y') and think aloud from there instead of in silence.

Voice signals

Measured in 46 of 51 answers with audio.

Your pitch went up at 16 of 264 measured phrase endings.

5 of 51 answers did not have enough clear audio to measure (too little speech, background noise).

Put the claim first and the hedge second — 'Rolling retention is active on day N or later; I've seen it once in a course' — so the listener hears your answer before the doubt.

In the answer where you computed the interval your fillers rose to about 11 a minute; writing the number down before saying it gives you a beat to land on it cleanly.

Pace, pauses and fillers

Measured across 51 answers: average speaking rate 143 wpm, 1.9 fillers per speaking minute, average time to start answering 3.7 s, longest mid-answer pause 4.4 s.

Pace: 143words/min
Fillers: 1.9per min
Longest pause: 4.4s
Time to first word: 3.7s; Average before you started answering
Speaking time: 79%; Share of answer time spent speaking
Hedges: 8; Phrases like "I think" or "maybe"

Answer by answer

Pause Strong Hesitation Rushing Trailing off Rambling

The answers with a marked moment. Notes on single answers use typical speaking ranges.

Question 1

0s35s
Length
35s
Pace
126
To first word
5.6s
Fillers
1
Hedges
2
Pauses
1
Hesitation (0s to 16s): Start came after 5.6 s, with 1 filler and 2 hedge phrases in the answer.

Question 2

0s51s
Length
51s
Pace
103
To first word
11.5s
Fillers
4
Hedges
2
Pauses
2
Hesitation (0s to 22s): Start came after 11.5 s, with 4 fillers and 2 hedge phrases in the answer.
Trailing off (39s to 51s): Voice level over the last 12 s was about 7 dB lower than earlier in the answer.

Question 3

0s40s
Length
40s
Pace
131
To first word
6.2s
Fillers
1
Hedges
2
Pauses
1
Hesitation (0s to 16s): Start came after 6.2 s, with 1 filler and 2 hedge phrases in the answer.
Trailing off (31s to 40s): Voice level over the last 9 s was about 7 dB lower than earlier in the answer.
0s37s
Length
37s
Pace
173
To first word
4.0s
Fillers
3
Hedges
0
Pauses
0
Hesitation (0s to 14s): Start came after 4.0 s, with 3 fillers and 0 hedge phrases in the answer.

18 steady stretches noted.
4 hesitation episodes noted.
2 trailing-off endings noted.

What Alex wrote down while listening.

ClaimQuestion 10:42
Clarified cohort definition and calendar-day semantics.
ClaimQuestion 12:05
Inspected the data and computed the expected retention by hand before coding.
GapQuestion 15:25
Inner join to day-1 logins: cohort shrinks to retained users only.
ClaimQuestion 15:47
Read the failing comparison and found the inner join without a hint.
ClaimQuestion 19:06
Conditional counts for d1/d7/d30 and NULL for censored recent cohorts.
ClaimQuestion 112:50
Rolling retention described as active on day N or later; unsure.
GapQuestion 217:47
Default inner merge drops orders without a customer.
GapQuestion 219:32
After the left merge, chased a dtype theory; missed groupby dropping NaN keys.
GapQuestion 224:25
Scaling answer general; partitioning not specified.
GapQuestion 329:33
Window had only an upper bound; a pre-exposure purchase counted as a conversion.
ClaimQuestion 329:41
Traced the failing control count to user 8's purchase before exposure.
ClaimQuestion 334:20
Recognised the normal-approximation CI is invalid at n=7; suggested Fisher's exact test.
GapQuestion 336:20
Youavy-tailed revenue: listed options without a clear choice.

One more note.

More next steps

Put the claim first and the hedge second — 'Rolling retention is active on day N or later; I've seen it once in a course' — so the listener hears your answer before the doubt.

101 turns, word for word.

Alex0:02
Hi, I'm Alex. This is the SQL and pandas round. The editor runs SQL on DuckDB and Python with pandas, against the data that comes with each question. You can run a query on its own to look at the result, or run it against the examples, which compares your output with the expected one. There are three questions; I'll keep an eye on time. Talk me through what you're doing as you go. First question. Given the logins table in your workspace, calculate day-1 retention: the share of users who logged in on the day after their first login, for each signup date.
You0:42
Okay. Um, so the signup date is just the first login, there's no separate signups table? And day 1 means exactly the next calendar day, not within twenty-four hours?
Alex0:56
Right. The cohort is each user's first login date, and day 1 is exactly one calendar day later. Return cohort_date, cohort_size and day1_retention as a fraction, one row per cohort, ordered by date. The newest cohort is included even though it has no day-1 data yet.
You1:14
And the table is one row per user per day, so no duplicates within a day?
Alex1:24
That's what the schema comment says: one row per user per login day. Comments in schemas are not always true, though. How would you check?
You1:33
I'd just look at it first. It's small.

You1:58

Sent to the workspace, sql

1SELECT * FROM logins ORDER BY user_id, login_date;2

You2:05
Okay, so eight rows, five users. User 1 logs in on the first, second and fifth. User 2 only the first. User 3 the second and third. 4 and 5 once each. So by hand, the January first cohort is users 1 and 2, and only 1 comes back on the second, so that's a half. January second is 3 and 4, 3 comes back, also a half. And January third is just user 5, zero. Good, that gives me something to check against.
You2:53
Plan: a CTE with the minimum login date per user, that's the cohort. Then I join back to logins on the same user and the cohort date plus one. Retention is the distinct users I find on that day divided by the cohort size.
Alex3:15
Go ahead.
You3:53
Okay, so, the CTE first. Select user id, min of login date as cohort date, from logins, group by user. Um, then the main query from that CTE, joined to logins on the user id and the login date equals cohort date plus one. In DuckDB I think adding an integer to a date works, it gives the next day. Group by cohort date and order by it.

You5:25

Sent to the workspace, sql

1WITH first_login AS (2    SELECT user_id, MIN(login_date) AS cohort_date3    FROM logins4    GROUP BY user_id5)6SELECT f.cohort_date,7       COUNT(DISTINCT f.user_id) AS cohort_size,8       COUNT(DISTINCT l.user_id) * 1.0 / COUNT(DISTINCT f.user_id) AS day1_retention9FROM first_login f10JOIN logins l11  ON l.user_id = f.user_id12 AND l.login_date = f.cohort_date + 113GROUP BY f.cohort_date14ORDER BY f.cohort_date;15

You5:33
That runs. Let me check it against the example.
You5:47
Hmm, it fails. It expects three rows and I have two, and my retention is one everywhere. Which doesn't match what I did by hand. Oh. The inner join only keeps users who came back on day 1, so the cohort becomes just the retained users. And the third of January disappears completely because nobody from it came back. I need to keep everyone in the cohort, so a left join.

You6:54

Sent to the workspace, sql

1WITH first_login AS (2    SELECT user_id, MIN(login_date) AS cohort_date3    FROM logins4    GROUP BY user_id5)6SELECT f.cohort_date,7       COUNT(DISTINCT f.user_id) AS cohort_size,8       COUNT(DISTINCT l.user_id) * 1.0 / COUNT(DISTINCT f.user_id) AS day1_retention9FROM first_login f10LEFT JOIN logins l11  ON l.user_id = f.user_id12 AND l.login_date = f.cohort_date + 113GROUP BY f.cohort_date14ORDER BY f.cohort_date;15

You7:01
With the left join all three pass. Half, half and zero, like by hand. And the empty table gives no rows, and the user who only came back on day three counts as not retained.
Alex7:21
Walk me through what the left join produces for user 4, the one who never came back.
You7:27
User 4's first login is January second, so the join looks for a login on the third. There isn't one, so with the left join I get one row for user 4 with the logins columns all null. COUNT DISTINCT on f.user_id counts them in the cohort, and COUNT DISTINCT on l.user_id skips the null, so they're in the denominator but not the numerator. That's exactly what I want.
Alex8:01
Why COUNT DISTINCT on the logins side?
You8:04
The table is one row per user per day, so today it can't duplicate, but if it were raw login events a user could log in three times on day 1 and be counted three times. Distinct keeps it a count of users. It's cheap insurance.
Alex8:26
Okay. Other edge cases. What about the denominator, can it ever be zero or null here, and what would happen if it were?
You8:35
Um, no, every cohort row exists because at least one user has that first login, so the cohort size is at least one. If I were dividing by something from a different table, I'd wrap it in a NULLIF.
Alex8:57
Next step. Product wants the standard retention table, not just day one. Extend it to day 1, 7 and 30 retention in one query.
You9:06
Okay. Same first-login CTE. Then instead of putting the day in the join, I join all of the user's logins and use conditional counts, count distinct user when the login date is the cohort date plus one, plus seven, plus thirty. And, um, the newest cohorts can't have day-30 data yet, so those should be null, not zero, otherwise it looks like everybody churned. I'll use the max date in the table as today.
Alex9:41
Go ahead and write it.
You10:15
So the join is now only on user id, every login of the user. Then three counts with case when inside the count distinct. And for the censoring, um, I'll cross join a one-row CTE with the max date, and wrap d7 and d30 in a case: only compute it if the max date is at least the cohort date plus seven, or plus thirty. Otherwise it falls through to null.

You12:12

Sent to the workspace, sql

1WITH first_login AS (2    SELECT user_id, MIN(login_date) AS cohort_date3    FROM logins4    GROUP BY user_id5),6last_day AS (7    SELECT MAX(login_date) AS max_date FROM logins8)9SELECT f.cohort_date,10       COUNT(DISTINCT f.user_id) AS cohort_size,11       COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 1 THEN l.user_id END) * 1.012           / COUNT(DISTINCT f.user_id) AS d1,13       CASE WHEN MAX(d.max_date) >= f.cohort_date + 714            THEN COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 7 THEN l.user_id END) * 1.015                 / COUNT(DISTINCT f.user_id) END AS d7,16       CASE WHEN MAX(d.max_date) >= f.cohort_date + 3017            THEN COUNT(DISTINCT CASE WHEN l.login_date = f.cohort_date + 30 THEN l.user_id END) * 1.018                 / COUNT(DISTINCT f.user_id) END AS d3019FROM first_login f20CROSS JOIN last_day d21LEFT JOIN logins l ON l.user_id = f.user_id22GROUP BY f.cohort_date23ORDER BY f.cohort_date;24

You12:19
So d1 is the same as before, a half, a half, zero. And d7 and d30 are null for every cohort, which is right here, the data only covers five days. If I'd written zero there it would be misleading.
Alex12:43
Mm-hm. You'll hear two definitions of retention on most teams. What is the difference between classic and rolling retention?
You12:50
Um. Classic is what I did, active exactly on day N. Rolling is, I think, active on day N or any day after it? So it's, kind of, always higher than classic, and it doesn't go down when someone skips a day. I'm fairly sure that's it, but I've only seen it in one course.
Alex13:26
When would you report rolling instead of classic?
You13:29
Um, maybe for a product people don't use every day. Like a tax app or a travel app, being active exactly on day seven means nothing, so day seven or later is closer to did we keep them. For a daily app like messaging I'd stay with classic. I'd at least say which one in the chart title, because they're easy to mix up.
Alex14:04
Okay. Next question. You have an orders DataFrame and a customers DataFrame. Compute the average order value per customer segment for the last 90 days, handling missing values sensibly.
You14:15
What counts as today, the real date or the latest order? And orders whose customer isn't in the customers table, do I drop them?
Alex14:29
Today is the latest order_date in the data, and the last 90 days means after that date minus 90. Orders without a matching customer go under unknown, not
You14:39
Sorry, and a customer who exists but has an empty segment, is that unknown too?
Alex14:46
Treat it the same way. Missing amounts are left out of the average, but count them. Print CSV: segment, avg_amount, n_orders and n_missing_amount, sorted by segment.
You14:56
And n_orders includes the ones with a missing amount?
Alex15:04
Yes. All orders of the segment.
You15:07
Okay. So filter to the last ninety days first, then merge in the segment, then group by segment with three aggregations: the mean of amount, which skips NaN by default, the count of orders, and a count of the nulls in amount.
Alex15:28
Okay.
You15:56
Reading both files, order date is already parsed as a date. Cutoff is the max minus a Timedelta of ninety days, keep strictly greater. Then merge on customer id. Um, group by segment, named aggregations, mean of amount, count of order id, and for the missing ones a lambda with isna sum. Reset index, sort, and print to_csv without the index.

You17:47

Sent to the workspace, python

1import pandas as pd2 3orders = pd.read_csv("data/orders.csv", parse_dates=["order_date"])4customers = pd.read_csv("data/customers.csv")5 6cutoff = orders["order_date"].max() - pd.Timedelta(days=90)7recent = orders[orders["order_date"] > cutoff]8 9df = recent.merge(customers, on="customer_id")10out = (11    df.groupby("segment")12    .agg(avg_amount=("amount", "mean"),13         n_orders=("order_id", "count"),14         n_missing_amount=("amount", lambda s: s.isna().sum()))15    .reset_index()16    .sort_values("segment")17)18print(out.to_csv(index=False), end="")19

You17:54
Um, the unknown row is missing. Right, merge is an inner join by default, so the order with no customer id and the one from customer 4, who isn't in the customers table, both disappear. Left merge.
Alex18:21
Before you change it, what do you expect the unknown row to be?
You18:26
Um, the order with no customer id is forty dollars, and customer 4's order is sixty. Both recent. So unknown should be an average of fifty, two orders, nothing missing. Order 6 is from January, so it's out of the window, it shouldn't show up anywhere. That's the nine ninety-nine one, which would really move enterprise if the filter were wrong.

You19:23

Sent to the workspace, python

1import pandas as pd2 3orders = pd.read_csv("data/orders.csv", parse_dates=["order_date"])4customers = pd.read_csv("data/customers.csv")5 6cutoff = orders["order_date"].max() - pd.Timedelta(days=90)7recent = orders[orders["order_date"] > cutoff]8 9df = recent.merge(customers, on="customer_id", how="left")10out = (11    df.groupby("segment")12    .agg(avg_amount=("amount", "mean"),13         n_orders=("order_id", "count"),14         n_missing_amount=("amount", lambda s: s.isna().sum()))15    .reset_index()16    .sort_values("segment")17)18print(out.to_csv(index=False), end="")19

You19:32
Hmm. Still no unknown row. Enterprise and smb are right. So the left merge kept the rows, I think, but... they don't show up. Um, maybe the merge on customer id fails because, uh, one side is float? The orders column has a NaN so it's float, and customers is int. Um.
Alex20:25
What does groupby do with NaN keys by default?
You20:29
Oh. It drops them. The segment is NaN for those two orders after the left merge, and groupby throws away NaN keys unless you pass dropna false. So the float thing was a red herring. I'll fill the segment with unknown before grouping, that also covers customer 5, whose segment is empty.

You21:17

Sent to the workspace, python

1import pandas as pd2 3orders = pd.read_csv("data/orders.csv", parse_dates=["order_date"])4customers = pd.read_csv("data/customers.csv")5 6cutoff = orders["order_date"].max() - pd.Timedelta(days=90)7recent = orders[orders["order_date"] > cutoff]8 9df = recent.merge(customers, on="customer_id", how="left")10df["segment"] = df["segment"].fillna("unknown")11out = (12    df.groupby("segment")13    .agg(avg_amount=("amount", "mean"),14         n_orders=("order_id", "count"),15         n_missing_amount=("amount", lambda s: s.isna().sum()))16    .reset_index()17    .sort_values("segment")18)19print(out.to_csv(index=False), end="")20

You21:24
Both examples pass now. Enterprise 200, smb 75 with one missing amount, unknown 50.
Alex21:34
Smb has three orders and an average of 75. Walk me through that row.
You21:39
Customers 1 and 3 are smb. Customer 1 has two recent orders, a hundred and fifty, and customer 3 has one order with no amount. So three orders, one missing, and the mean is over the two that have an amount, a hundred and fifty over two, seventy-five. If I'd filled the missing amount with zero, it would be fifty, which would be wrong.
Alex22:10
Earlier you suspected the customer_id types. After reading the CSV, what is the dtype on each side, and does it matter here?
You22:19
In orders it's float, because one id is empty and NaN forces float. In customers it's int. Pandas still matches one point oh with one, so the merge works, it just might warn. In a bigger pipeline I'd cast both to the nullable Int64 type so ids stay integers and the missing one stays missing.
Alex22:48
Okay. One thing about the merge itself. Nothing in the data guarantees customer_id is unique in customers. How would you check the merge did not duplicate orders?
You22:58
If a customer id appeared twice in customers, every one of their orders would be doubled and the counts would be wrong without any error. So I'd add validate many to one on the merge, pandas raises if the right side has duplicate keys. And an assert that the row count after the merge equals the row count before. Let me put both in.

You23:57

Sent to the workspace, python

1import pandas as pd2 3orders = pd.read_csv("data/orders.csv", parse_dates=["order_date"])4customers = pd.read_csv("data/customers.csv")5 6cutoff = orders["order_date"].max() - pd.Timedelta(days=90)7recent = orders[orders["order_date"] > cutoff]8 9df = recent.merge(customers, on="customer_id", how="left", validate="many_to_one")10assert len(df) == len(recent)  # the merge must not duplicate orders11df["segment"] = df["segment"].fillna("unknown")12out = (13    df.groupby("segment")14    .agg(avg_amount=("amount", "mean"),15         n_orders=("order_id", "count"),16         n_missing_amount=("amount", lambda s: s.isna().sum()))17    .reset_index()18    .sort_values("segment")19)20print(out.to_csv(index=False), end="")21

You24:05
Still passes, so no duplicates in this data, but now it would fail loudly.
Alex24:15
Now the same thing in production, where orders is a fact table that keeps growing. How would you scale this if orders has 2 billion rows?
You24:25
I wouldn't load that into pandas. I'd push it down to the warehouse as SQL: filter the date first, which should prune partitions if the table is partitioned by date, aggregate, then join the small customers table. Um, or Spark if it has to be Python, with a broadcast join for customers since it's small. I'm less sure how I'd pick the partitioning if I had to set it up myself.
Alex25:03
Say you write it in SQL in the warehouse. Where could the SQL version quietly differ from your pandas one?
You25:11
Hmm. NULLs, mostly. AVG in SQL also skips nulls, so that's the same. But COUNT of amount skips nulls and COUNT star doesn't, so n_orders has to be COUNT star or COUNT of order id. A NULL segment groups into its own NULL group in SQL instead of disappearing, which is actually the opposite of pandas, so I'd still COALESCE it to unknown. And the date cutoff, whether it's strictly greater, and timestamps versus dates, that's where off-by-one-day bugs come from.
Alex25:55
Okay. Last question. You have exposure and purchase data from an A/B test. Compute conversion rate per variant, the difference with a confidence interval, and check whether the assignment looks healthy.
You26:07
Okay, a few questions. Which purchases count, any purchase after exposure, or within some window? And was the split meant to be fifty-fifty?
Alex26:21
Only purchases after the user's first exposure, within 7 days. The design intended a 50/50 split. Also, some users appear in both variants because of a bug. The exposures table should be one row per user, but it isn't always.
You26:36
Then those users have to go, they saw both, so I can't attribute anything. Do you want the SQL part here and the interval separately?
Alex26:51
The SQL part: per variant, users, converters and conversion rate, ordered by variant, after excluding users who appear in both variants. The interval and the assignment check can be in Python.
You27:03
Okay. First a clean CTE: group exposures by user, keep only users with exactly one distinct variant, and take their first exposure time. Then left join purchases inside the seven-day window, and count distinct users and distinct converters per variant. The user is the unit, not the purchase.
Alex27:26
Go ahead.
You27:55
Clean CTE: group by user id, having count distinct variant equals one, and then min of variant is just the variant, and min of exposed at is the first exposure. Then left join purchases on user id and purchased at less than exposed at plus an interval of seven days. Count distinct both sides, and the rate is converters times one point oh over users.

You29:33

Sent to the workspace, sql

1WITH clean AS (2    SELECT user_id, MIN(variant) AS variant, MIN(exposed_at) AS exposed_at3    FROM exposures4    GROUP BY user_id5    HAVING COUNT(DISTINCT variant) = 16)7SELECT c.variant,8       COUNT(DISTINCT c.user_id) AS users,9       COUNT(DISTINCT p.user_id) AS converters,10       COUNT(DISTINCT p.user_id) * 1.0 / COUNT(DISTINCT c.user_id) AS conversion_rate11FROM clean c12LEFT JOIN purchases p13  ON p.user_id = c.user_id14 AND p.purchased_at < c.exposed_at + INTERVAL 7 DAY15GROUP BY c.variant16ORDER BY c.variant;17

You29:41
Control is wrong. It expects one converter out of four, I have two. Treatment matches. So one control user converts in my query and shouldn't. Let me think. User 2 buys on the twelfth, that's outside the seven days, so not them. User 8... buys on December thirty-first, before the exposure. I only put the upper bound in the join. I need purchased at greater or equal to exposed at as well.

You30:51

Sent to the workspace, sql

1WITH clean AS (2    SELECT user_id, MIN(variant) AS variant, MIN(exposed_at) AS exposed_at3    FROM exposures4    GROUP BY user_id5    HAVING COUNT(DISTINCT variant) = 16)7SELECT c.variant,8       COUNT(DISTINCT c.user_id) AS users,9       COUNT(DISTINCT p.user_id) AS converters,10       COUNT(DISTINCT p.user_id) * 1.0 / COUNT(DISTINCT c.user_id) AS conversion_rate11FROM clean c12LEFT JOIN purchases p13  ON p.user_id = c.user_id14 AND p.purchased_at >= c.exposed_at15 AND p.purchased_at < c.exposed_at + INTERVAL 7 DAY16GROUP BY c.variant17ORDER BY c.variant;18

You30:58
Both pass. Control a quarter, treatment two thirds, and the case where nobody converts gives zeros instead of dropping the rows, because of the left join.
Alex31:12
Is the DISTINCT on the purchases side doing anything in this data?
You31:17
Yes, user 1 buys twice, on the third and the fourth, both inside the window. Without distinct, the join gives two rows for user 1 and control would have two converters out of four, which is the same wrong number I had before, but for a different reason. Funny. So yes, it matters.
Alex31:42
Why count users and not exposures as the unit?
You31:46
Because the randomization is by user. If one user is exposed five times and I count exposures, their behavior counts five times, and the observations aren't independent anymore, so the variance would be wrong. The user is what was assigned, so that's what I count.
Alex32:06
Okay. The question also asks for the difference with a confidence interval, and whether the assignment looks healthy. How do you want to do those?
You32:16
I'll do it in Python with the counts from the query. Um, two-proportion, normal approximation, so the standard error is, uh, the square root of p times one minus p over n for each group, added. And, you know, a chi-square on the group sizes against fifty-fifty for the assignment check.
Alex32:38
Go ahead.
You33:02
I'll just hard-code the counts from the query, four and one, three and two. Proportions, the difference, the standard error, plus and minus one point nine six. Then the chi-square for the split by hand, it's two cells.

You34:10

Sent to the workspace, python

1from math import sqrt2 3n_c, x_c = 4, 1   # control: users, converters (from the query)4n_t, x_t = 3, 2   # treatment5p_c, p_t = x_c / n_c, x_t / n_t6diff = p_t - p_c7se = sqrt(p_c * (1 - p_c) / n_c + p_t * (1 - p_t) / n_t)8print(f"control {p_c:.3f} treatment {p_t:.3f} diff {diff:.3f}")9print(f"95% CI {diff - 1.96 * se:.3f} to {diff + 1.96 * se:.3f}")10 11# sample ratio check against the planned 50/50 split, chi-square with 1 df12n = n_c + n_t13chi2 = (n_c - n / 2) ** 2 / (n / 2) + (n_t - n / 2) ** 2 / (n / 2)14print(f"SRM chi-square {chi2:.3f} (cutoff 3.84 at 5%)")15

You34:20
Okay, so the difference is about point four two, and the interval goes from, uh, minus point two seven to one point one. Which, um, is above one, so it's nonsense. That's the normal approx- the normal approximation breaking with, you know, three and four users. With this sample I'd say: no conclusion, and, I mean, if I had to test something this small I'd use, um, Fisher's exact test. The chi-square for the split is point one four, so nothing wrong with the assignment, but again, seven users.
Alex34:53
If a product manager asked you for one sentence about this test, based on this data, what would you say?
You35:01
Um, treatment looks higher, but, uh, with seven users we can't tell it apart from chance, and one user had to be dropped because they saw both versions. We need the full test before deciding anything.
Alex35:19
Different scenario. The real test ran for two weeks on a lot more users. The treatment group is 52% of users. Is that a problem and how do you decide?
You35:33
It depends on the total. If it's a hundred users, fifty-two to forty-eight is noise. If it's a hundred thousand... uh, the expected is fifty thousand each, observed fifty-two thousand, so each cell is off by two thousand. Two thousand squared over fifty thousand is eighty, times two cells, a chi-square of a hundred and sixty. That's way past three point eight four. So at that size it's a sample ratio mismatch and I wouldn't trust the result until I found why, like a bug that drops control users or a redirect that loses some.
Alex36:11
Mm-hm. Conversion is a yes or no per user. Product also wants revenue. How would you handle revenue per user, which is heavy tailed?
You36:20
Um, the mean gets dominated by a few big spenders, so the variance is huge. I'd probably bootstrap the difference in means, or cap the top like the ninety-ninth percentile. Or test the median with Mann-Whitney, but that's a different question than the mean, and the business usually cares about total revenue. I'm honestly not sure which one a team would pick by default.
Alex37:01
Suppose the real test is large and conversion is up two points with a tight interval. What else do you look at before recommending a launch?
You37:11
Um, guardrails. Like refunds, or, uh, revenue per user, in case people convert more but spend less. Whether the lift holds over the weeks or it's a novelty effect that's fading, so plot it by day. And, you know, if we looked at a lot of metrics, some will be significant by chance, so I'd only trust the one we picked as primary before the test. And I'd check how many users we lost by dropping the ones in both variants. If that's a lot, the bug itself could bias things.
Alex37:50
We have a few minutes. Any questions for me?
You37:53
Yeah, one. How much of a data scientist's day is SQL like this versus modeling, usually?
Alex38:03
It depends a lot on the team and the company. Analytics-heavy roles are mostly SQL, metrics and experiments, with a lot of time spent checking data before anyone models anything. Others are closer to machine learning. The title alone doesn't tell you much, so it's worth asking each team directly. The hiring team can describe the specific role. Anything else?
You38:25
Yes, one more. Would something like the experiment question come up more in a later round, like a case interview?
Alex38:37
Often, yes. Many processes have a separate product or experimentation round where you design the test and interpret results without code. The exact rounds differ by company; the recruiter can tell you the format.
You38:49
Okay. That's it, thank you.
Alex38:54
That's time. Thanks. Your report will be ready in a few minutes.

Mockich is a practice tool. Scores and delivery signals are guidance for your preparation, not a prediction of a real hiring decision.

Start another interview

Code runs in this session17 runs, 9 of 13 example checks passed

What the code runner returned to the student, in order: real executions of their code in the sandbox, on each question’s own data.

2:02Question 1Ran the query on the question’s tablesRan
SQL (DuckDB), the code sent at 1:58
user_id login_date
1 2024-01-01
1 2024-01-02
1 2024-01-05
2 2024-01-01
3 2024-01-02
3 2024-01-03
4 2024-01-02
5 2024-01-03
8 rows
5:31Question 1Ran the query on the question’s tablesRan
SQL (DuckDB), the code sent at 5:25
cohort_date cohort_size day1_retention
2024-01-01 1 1
2024-01-02 1 1
2 rows
5:45Question 1Ran one example: Example 1: cohort_date, cohort_size, day-1 retention (a fraction), by dateFailed
SQL (DuckDB), the code sent at 5:25
cohort_date cohort_size day1_retention
2024-01-01 1 1
2024-01-02 1 1
2 rows
Expected rows
cohort_date cohort_size day1_retention
2024-01-01 2 0.5
2024-01-02 2 0.5
2024-01-03 1 0
3 rows
Example 1: cohort_date, cohort_size, day-1 retention (a fraction), by date: 3 rows missing, 2 unexpected rows (expected 3, got 2).
row 1, expected2024-01-01 | 2 | 0.5yours2024-01-01 | 1 | 1
6:59Question 1Ran every example3/3 passed
SQL (DuckDB), the code sent at 6:54
- cohort_date cohort_size day1_retention
  2024-01-01 2 0.5
  2024-01-02 2 0.5
  2024-01-03 1 0
  3 rows
  Example 1: cohort_date, cohort_size, day-1 retention (a fraction), by date: The result matches the expected rows.
- cohort_date cohort_size day1_retention
  0 rows
  Edge: no logins at all: The result matches the expected rows.
- cohort_date cohort_size day1_retention
  2024-02-01 1 0
  1 row
  Edge: a login on day 3 but not day 1 does not count: The result matches the expected rows.
12:17Question 1Ran the query on the question’s tablesRan
SQL (DuckDB), the code sent at 12:12
cohort_date cohort_size d1 d7 d30
2024-01-01 2 0.5 NULL NULL
2024-01-02 2 0.5 NULL NULL
2024-01-03 1 0 NULL NULL
3 rows
17:52Question 2Ran one example: Example 1: print CSV with columns segment, avg_amount, n_orders, n_missing_amount (sorted by segment)Failed
Python, the code sent at 17:47
```
segment,avg_amount,n_orders,n_missing_amount
enterprise,200.0,1,0
smb,75.0,3,1
```
Example 1: print CSV with columns segment, avg_amount, n_orders, n_missing_amount (sorted by segment): differs from the expected output.
line 4, expectedunknown,50.0,2,0yours
19:28Question 2Ran one example: Example 1: print CSV with columns segment, avg_amount, n_orders, n_missing_amount (sorted by segment)Failed
Python, the code sent at 19:23
```
segment,avg_amount,n_orders,n_missing_amount
enterprise,200.0,1,0
smb,75.0,3,1
```
Example 1: print CSV with columns segment, avg_amount, n_orders, n_missing_amount (sorted by segment): differs from the expected output.
line 4, expectedunknown,50.0,2,0yours
21:22Question 2Ran every example2/2 passed
Python, the code sent at 21:17
- ```
segment,avg_amount,n_orders,n_missing_amount
enterprise,200.0,1,0
smb,75.0,3,1
unknown,50.0,2,0
```
  Example 1: print CSV with columns segment, avg_amount, n_orders, n_missing_amount (sorted by segment): matches the expected output.
- ```
segment,avg_amount,n_orders,n_missing_amount
smb,15.0,2,0
```
  Edge: every order is recent and from one segment: matches the expected output.
24:02Question 2Ran every example2/2 passed
Python, the code sent at 23:57
- ```
segment,avg_amount,n_orders,n_missing_amount
enterprise,200.0,1,0
smb,75.0,3,1
unknown,50.0,2,0
```
  Example 1: print CSV with columns segment, avg_amount, n_orders, n_missing_amount (sorted by segment): matches the expected output.
- ```
segment,avg_amount,n_orders,n_missing_amount
smb,15.0,2,0
```
  Edge: every order is recent and from one segment: matches the expected output.
29:38Question 3Ran one example: Example 1 (the SQL part): variant, users, converters, conversion rateFailed
SQL (DuckDB), the code sent at 29:33
variant users converters conversion_rate
control 4 2 0.5
treatment 3 2 0.6666666666666666
2 rows
Expected rows
variant users converters conversion_rate
control 4 1 0.25
treatment 3 2 0.6666666666666666
2 rows
Example 1 (the SQL part): variant, users, converters, conversion rate: 1 row missing, 1 unexpected row (expected 2, got 2).
row 1, expectedcontrol | 4 | 1 | 0.25yourscontrol | 4 | 2 | 0.5
30:56Question 3Ran every example2/2 passed
SQL (DuckDB), the code sent at 30:51
- variant users converters conversion_rate
  control 4 1 0.25
  treatment 3 2 0.6666666666666666
  2 rows
  Example 1 (the SQL part): variant, users, converters, conversion rate: The result matches the expected rows.
- variant users converters conversion_rate
  control 1 0 0
  treatment 1 0 0
  2 rows
  Edge: nobody converts: The result matches the expected rows.

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-05
2	2024-01-01
3	2024-01-02
3	2024-01-03
4	2024-01-02
5	2024-01-03

cohort_date	cohort_size	day1_retention
2024-01-01	1	1
2024-01-02	1	1

cohort_date	cohort_size	day1_retention
2024-01-01	1	1
2024-01-02	1	1

cohort_date	cohort_size	day1_retention
2024-01-01	2	0.5
2024-01-02	2	0.5
2024-01-03	1	0

cohort_date	cohort_size	day1_retention
2024-01-01	2	0.5
2024-01-02	2	0.5
2024-01-03	1	0

cohort_date	cohort_size	day1_retention
2024-02-01	1	0

cohort_date	cohort_size	d1	d7	d30
2024-01-01	2	0.5	NULL	NULL
2024-01-02	2	0.5	NULL	NULL
2024-01-03	1	0	NULL	NULL

variant	users	converters	conversion_rate
control	4	2	0.5
treatment	3	2	0.6666666666666666

variant	users	converters	conversion_rate
control	4	1	0.25
treatment	3	2	0.6666666666666666

variant	users	converters	conversion_rate
control	4	1	0.25
treatment	3	2	0.6666666666666666

variant	users	converters	conversion_rate
control	1	0	0
treatment	1	0	0

34:15Question 3Ran the codeRan

Python, the code sent at 34:10

control 0.250 treatment 0.667 diff 0.417
95% CI -0.265 to 1.098
SRM chi-square 0.143 (cutoff 3.84 at 5%)

If you read nothing else

What happened

After that

Scorecard

Also went well

Also to improve

Red flags

Question by question

Key points

Follow-up questions

A stronger answer

Common mistakes on this question

Key points

Follow-up questions

A stronger answer

Common mistakes on this question

Key points

Follow-up questions

A stronger answer

Common mistakes on this question

Code review

1Given the logins table in your workspace, calculate day-1 retention: the share of users who logged in on the day after their first login, for each signup date.

Edge cases to check

A cleaner outline

2You have an orders DataFrame and a customers DataFrame. Compute the average order value per customer segment for the last 90 days, handling missing values sensibly.

Edge cases to check

A cleaner outline

3You have exposure and purchase data from an A/B test. Compute conversion rate per variant, the difference with a confidence interval, and check whether the assignment looks healthy.

Edge cases to check

A cleaner outline

Your code runs

Coaching

Commit to a default, then caveat

Predict the result before you run

Say how you would partition the table

Say this instead

Next 7 days

How you sounded

Moments worth a look

Voice signals

Pace, pauses and fillers

Answer by answer

Alex’s notes

More from this interview

More next steps

Transcript