Data Analysis with Python (pandas, polars, DuckDB) Module 17 – Analyst interviews and take-home assignments
Data analyst interviews: pandas and SQL patterns
The twelve tasks that recur in data analyst coding rounds, each solved in pandas and in SQL on the same data, plus a take-home write-up and two drills.
What you will learn
- Recognise the twelve task patterns that recur in analyst coding rounds and take-home assignments
- Solve each pattern in pandas and in SQL, and explain its edge case aloud
- State the grain, the keys, the meaning of missing values and the tie rule before writing code
- Structure a take-home assignment and its README write-up
- Practise two timed drills against sample tests
Before you start
What you’ll learn · Before you start
What you will learn
- Recognise the twelve task patterns that recur in analyst coding rounds and take-home assignments
- Solve each pattern in pandas and in SQL, and explain its edge case aloud
- State the grain, the keys, the meaning of missing values and the tie rule before writing code
- Structure a take-home assignment and its README write-up
- Practise two timed drills against sample tests
Before you start
On this page
Data analyst coding rounds and take-home assignments keep returning to the same twelve kinds of task: the top N rows of each group, the latest record per key, duplicates, running totals, growth from one period to the next, gaps in a sequence, sessions in an event log, retention by sign-up cohort, funnel conversion, rows with no match in another table, the median of each group, and pivoting and unpivoting. Each rests on one of four moves, and each has an edge case that interviewers like to ask about: a tie, a missing month, two rows with the same timestamp, a month that has not happened yet.
This lesson solves all twelve on small tables, once in pandas and once in SQL run by DuckDB, and each of those examples checks that the two answers hold the same rows. Then it shows how to write up a take-home assignment, with a sample README whose numbers come from a script, and ends with two timed drills.
Say four things before you write code
Interviewers listen to how you set a problem up as closely as they read your code. Before the first line, say four things in a sentence each:
- The grain. What one row of each table stands for, and what one row of your answer will be: “one row per order in, one row per city and month out”. The first lesson of this track explains why the grain decides what a count means.
- The keys. Which columns identify a row, and whether they are really unique.
df.duplicated(["customer"]).sum()answers that in one line, and a nonzero answer changes your plan. - Missing values. What an empty cell means here: not delivered yet, unknown, or zero. It decides whether the row is counted, skipped or filled.
- Ties and order. What decides between rows that tie, and what order the answer comes in.
“Two restaurants tie for second place in Pune, so I’ll break ties by name and every city gets exactly two rows” takes ten seconds to say and answers the follow-up question before it is asked. Then test the smallest input that would break your solution, a tie, a missing month or two events in the same minute, which is what every example below does.
Twelve patterns, four moves
Twelve task patterns rest on four moves
Text description of the diagram
The diagram starts from a box that reads "The task, in the interviewer's words: which move does it need?". An arrow leads down to four families, one under the other.
- Rank inside a group: top N per group, the latest record per key, and duplicates. In pandas: sort, then keep rows by their position. In SQL: ROW_NUMBER() OVER (…).
- Compare with the row before: running totals, growth, gaps and islands, and sessions. In pandas: shift, diff and cumsum per group. In SQL: LAG() and SUM() OVER (… ROWS …).
- Match across tables or time: retention cohorts, funnels, and rows with no match. In pandas: merge and isin. In SQL: JOIN, LEFT JOIN and NOT EXISTS.
- Summarise or reshape: the median by group, and pivot and unpivot. In pandas: agg, pivot_table and melt. In SQL: GROUP BY, a conditional SUM and UNPIVOT.
Read a task for the move it needs, not for its business story. “The two best restaurants in each city” and “each customer’s current plan” are the same move: order the rows inside a group and keep some of them. “Revenue so far”, “growth on last month” and “a new session after 30 minutes of quiet” all compare a row with the one before it.
The twelve at a glance, each with the words that usually signal it and the core of the answer in each tool:
- Top N per group (“the three best … in each …”). pandas:
sort_values, thengroupby().head(n). SQL:ROW_NUMBER() OVER (PARTITION BY …). - Latest record per key (“current”, “most recent”). pandas:
sort_values, thendrop_duplicates(keep="last"). SQL:ROW_NUMBER(), keeping row 1. - Duplicates (“more than once”, “repeated”). pandas:
duplicated(keep=False). SQL:GROUP BY … HAVING count(*) > 1. - Running totals (“so far”, “cumulative”, “to date”). pandas:
groupby().cumsum(). SQL:SUM() OVER (… ROWS BETWEEN …). - Growth between periods (“compared with last month”). pandas: complete the calendar, then
shift(). SQL: a calendarLEFT JOIN, thenLAG(). - Gaps and islands (“missing numbers”, “consecutive”). pandas:
diff(),shift(-1)andcumsum(). SQL:LEAD()andn - ROW_NUMBER(). - Sessions (“within 30 minutes”, “visits”). pandas:
diff(), a comparison andcumsum(). SQL:LAG(),CASEand a runningSUM(). - Retention cohorts (“came back”, “still active after”). pandas: months since sign-up, then
nunique(). SQL:date_diff()andCOUNT(DISTINCT …). - Funnels (“went on to”, “conversion from … to …”). pandas: the first time of each step after the step before.
SQL:
MIN(ts)per step, with a time condition. - Rows with no match (“never”, “did not”, “without”). pandas:
merge(how="left_anti")or~isin(). SQL:NOT EXISTSorLEFT JOIN … IS NULL. - Median by group (“typical”, “middle”). pandas:
groupby().median(). SQL:median()in DuckDB,percentile_cont(0.5)in PostgreSQL. - Pivot and unpivot (“one column per month”, “back to rows”). pandas:
pivot_table()andmelt(). SQL: a conditionalSUM()andUNPIVOT.
Every pattern example below builds a small DataFrame, answers the task in pandas, runs the SQL answer on the same DataFrame through DuckDB, which reads a pandas DataFrame by its variable name, and passes both to this helper. It prints the pandas rows and stops with an error unless SQL returned the same rows in the same order, so each recorded output below is also the proof that the two answers agree:
"""Used by every pattern example: print the pandas answer, then check that the SQL answer
holds the same rows."""
import pandas as pd
def same_rows(pandas_answer, sql_answer):
"""Print pandas' rows; raise an error unless SQL returned them in the same order."""
a = pandas_answer.reset_index(drop=True)
b = sql_answer.reset_index(drop=True)
print(a.to_string(index=False))
pd.testing.assert_frame_equal(a, b, check_dtype=False)
print(f"DuckDB's SQL returns the same {len(b)} rows.")Rank inside a group: top N, latest record, duplicates
Top N per group
The task: the two highest-earning restaurants in each city. Two Pune restaurants tie for second place, and Indore has only one restaurant.
# Top N per group: the two highest-earning restaurants in each city, ties broken by name.
import duckdb
import pandas as pd
from agree import same_rows
sales = pd.DataFrame({
"city": ["Pune", "Pune", "Pune", "Pune", "Kochi", "Kochi", "Kochi", "Indore"],
"restaurant": ["Masala Hub", "Dosa Den", "Chai Point", "Vada Corner",
"Fish Fry", "Appam House", "Biryani Bay", "Poha Stop"],
"revenue": [5200, 4100, 4100, 3000, 3900, 3900, 2800, 2500],
})
# pandas: sort so the wanted rows come first in each city, then keep the first two of each
top2 = (sales.sort_values(["city", "revenue", "restaurant"], ascending=[True, False, True])
.groupby("city").head(2))
sql = duckdb.sql("""
SELECT city, restaurant, revenue
FROM (SELECT *, row_number() OVER (PARTITION BY city
ORDER BY revenue DESC, restaurant) AS rn
FROM sales)
WHERE rn <= 2
ORDER BY city, revenue DESC, restaurant
""").df()
same_rows(top2, sql)
# Keeping ties instead: rank(method="min") numbers like SQL's RANK(), so a tie for second
# place keeps both rows
place = sales.groupby("city")["revenue"].rank(method="min", ascending=False)
kept = sales[place <= 2]
pune = ", ".join(kept.loc[kept["city"] == "Pune", "restaurant"])
print(f"With ties kept: {len(kept)} rows. Pune keeps", pune)Output
city restaurant revenue Indore Poha Stop 2500 Kochi Appam House 3900 Kochi Fish Fry 3900 Pune Masala Hub 5200 Pune Chai Point 4100 DuckDB's SQL returns the same 5 rows. With ties kept: 6 rows. Pune keeps Masala Hub, Dosa Den, Chai Point
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 top_n_per_group.py
Sort so that the rows you want come first in each group, then take the first two: groupby("city").head(2) returns
the first two rows of every city with their order and index kept. SQL numbers the rows inside each city with
ROW_NUMBER() and keeps numbers 1 and 2. Both put a tie-breaker in the sort, the restaurant’s name after the revenue.
Without it, which of Chai Point and Dosa Den comes second depends on the order the rows happen to be in, and pandas and
SQL, or two runs of the same query, can disagree. Indore gets one row: a group smaller than N is not an error.
“Exactly two per city” is one reading of the task. “Every restaurant with one of the two highest revenues” keeps ties:
rank(method="min") numbers like SQL’s RANK(), gives Chai Point and Dosa Den both place 2, and Pune keeps three
rows. Ask which reading is wanted. Filtering, sorting and ranking
compares all five rank methods on one tied column.
The latest record per key
The task: each customer’s current plan, from a log of plan changes. Customer C2 changed plan twice in the same minute.
# Latest record per key: each customer's current plan, from a log of plan changes.
import duckdb
import pandas as pd
from agree import same_rows
changes = pd.DataFrame({
"change_id": [1, 2, 3, 4, 5],
"customer": ["C1", "C2", "C2", "C1", "C3"],
"changed_at": pd.to_datetime(["2026-03-01 09:00", "2026-03-01 11:00",
"2026-03-01 11:00", "2026-03-02 10:30",
"2026-03-02 08:15"]),
"plan": ["basic", "basic", "silver", "gold", "gold"],
})
# pandas: order each customer's rows oldest to newest (change_id breaks the tie at 11:00),
# then keep the last
latest = (changes.sort_values(["customer", "changed_at", "change_id"])
.drop_duplicates("customer", keep="last")
[["customer", "changed_at", "plan"]])
sql = duckdb.sql("""
SELECT customer, changed_at, plan
FROM (SELECT *, row_number() OVER (PARTITION BY customer
ORDER BY changed_at DESC, change_id DESC) AS rn
FROM changes)
WHERE rn = 1
ORDER BY customer
""").df()
same_rows(latest, sql)
# The max-and-join-back version returns both of C2's rows: they share the latest time
joined = duckdb.sql("""
SELECT c.customer, c.plan
FROM changes AS c
JOIN (SELECT customer, max(changed_at) AS last_at FROM changes GROUP BY customer) AS m
ON c.customer = m.customer AND c.changed_at = m.last_at
""").df()
customers = joined["customer"].nunique()
print("max() and join back:", len(joined), "rows for", customers, "customers")Output
customer changed_at plan
C1 2026-03-02 10:30:00 gold
C2 2026-03-01 11:00:00 silver
C3 2026-03-02 08:15:00 gold
DuckDB's SQL returns the same 3 rows.
max() and join back: 4 rows for 3 customers
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 latest_per_key.py
Sort each customer’s rows from oldest to newest and keep the last one with drop_duplicates("customer", keep="last").
In SQL, number each customer’s rows from newest to oldest and keep row 1. The change ID decides between C2’s two
changes at 11:00; sorting by time alone would leave that choice to row order.
A common shortcut, a GROUP BY with max(changed_at) joined back to the table, returns every row at the latest
time, so C2 comes back twice and the answer has 4 rows for 3 customers. Say what breaks a tie, or say that the data
cannot have one and check that it does not.
Duplicates: find them before you drop them
The task: a payments export repeats some payment IDs. Report them before cleaning anything.
# Duplicates: find them, and tell harmless copies from conflicts, before dropping anything.
import duckdb
import pandas as pd
from agree import same_rows
payments = pd.DataFrame({
"payment_id": ["P1", "P1", "P2", "P3", "P3", "P4"],
"amount": [500, 500, 1200, 300, 350, 800],
"method": ["UPI", "UPI", "card", "UPI", "UPI", "cash"],
})
print("Rows that copy an earlier row exactly:", payments.duplicated().sum())
# pandas: every payment ID that occurs more than once, with its rows and distinct amounts
repeated = payments[payments.duplicated("payment_id", keep=False)]
report = (repeated.groupby("payment_id", as_index=False)
.agg(rows=("amount", "size"), amounts=("amount", "nunique")))
sql = duckdb.sql("""
SELECT payment_id, count(*) AS rows, count(DISTINCT amount) AS amounts
FROM payments
GROUP BY payment_id
HAVING count(*) > 1
ORDER BY payment_id
""").df()
same_rows(report, sql)
clean = payments.drop_duplicates()
p3_rows = (clean["payment_id"] == "P3").sum()
print("After dropping exact copies:", len(clean), "rows; P3 still has", p3_rows)Output
Rows that copy an earlier row exactly: 1
payment_id rows amounts
P1 2 1
P3 2 2
DuckDB's SQL returns the same 2 rows.
After dropping exact copies: 5 rows; P3 still has 2
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 duplicates.py
Two kinds of duplicate need different answers. An exact copy, every column the same, is safe to drop: duplicated()
counts one here, and drop_duplicates() removes it. A repeated key with different values is a conflict. P3 has two
amounts, 300 and 350, and dropping either one without a rule changes revenue. The report counts both kinds: rows and
distinct amounts per repeated ID, which is GROUP BY payment_id HAVING count(*) > 1 in SQL. Then choose the rule for
conflicts, such as the latest row or the amount the payment provider confirms, and write down how many rows it
touched.
Compare with the row before: totals, growth, gaps, sessions
Running totals
The task: each city’s revenue so far, order by order. Two Pune orders share the first day.
# Running totals: revenue so far in each city, order by order.
import duckdb
import pandas as pd
from agree import same_rows
orders = pd.DataFrame({
"order_id": [101, 102, 103, 104, 105, 106],
"city": ["Pune", "Pune", "Kochi", "Pune", "Kochi", "Pune"],
"day": pd.to_datetime(["2026-03-01", "2026-03-01", "2026-03-01",
"2026-03-02", "2026-03-03", "2026-03-03"]),
"amount": [400, 250, 300, 500, 200, 150],
})
# pandas: cumsum adds row by row, so sort first, with order_id ordering the rows of a day
o = orders.sort_values(["city", "day", "order_id"]).copy()
o["so_far"] = o.groupby("city")["amount"].cumsum()
o = o[["city", "day", "order_id", "amount", "so_far"]]
sql = duckdb.sql("""
SELECT city, day, order_id, amount,
sum(amount) OVER (PARTITION BY city ORDER BY day, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS so_far
FROM orders
ORDER BY city, day, order_id
""").df()
same_rows(o, sql)
# Without a frame clause, the frame ends with the current row's peers, so orders on the
# same day share a total
default = duckdb.sql("""
SELECT order_id, sum(amount) OVER (PARTITION BY city ORDER BY day) AS so_far_default
FROM orders WHERE city = 'Pune' ORDER BY day, order_id
""").df()
print("Pune with the default frame:", default["so_far_default"].astype("int64").tolist())Output
city day order_id amount so_far Kochi 2026-03-01 103 300 300 Kochi 2026-03-03 105 200 500 Pune 2026-03-01 101 400 400 Pune 2026-03-01 102 250 650 Pune 2026-03-02 104 500 1150 Pune 2026-03-03 106 150 1300 DuckDB's SQL returns the same 6 rows. Pune with the default frame: [650, 650, 1150, 1300]
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 running_totals.py
cumsum adds row by row in the order the rows are in, so sort first, with something that orders the rows inside a
day, here the order ID. In SQL the frame clause decides the answer. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
adds row by row, like pandas. Leave the frame out and DuckDB, like PostgreSQL, uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, in which the current row brings its peers, every row with the same ORDER BY value
(DuckDB window functions). Pune’s two first-day
orders then both show 650 instead of 400 and 650. The total at the end of each day is right either way; the running
total of each order is not.
Growth from one period to the next
The task: month-on-month revenue growth for each city. Indore had no orders in March.
# Growth between periods: month-on-month revenue growth per city, with a month missing.
import duckdb
import pandas as pd
from agree import same_rows
monthly = pd.DataFrame({
"city": ["Pune", "Pune", "Pune", "Pune", "Indore", "Indore", "Indore"],
"month": ["2026-01", "2026-02", "2026-03", "2026-04", "2026-01", "2026-02", "2026-04"],
# thousand rupees; Indore had no orders in March
"revenue": [100, 120, 150, 120, 80, 100, 140],
})
# The trap: pct_change compares each row with the row above it, so Indore's April is
# compared with February
naive = monthly.groupby("city")["revenue"].pct_change()
print("Row-to-row growth for Indore in April:", f"{naive.iloc[-1]:+.0%}")
# pandas: one row for every city and month first (revenue 0 in a month without orders),
# then compare
months = [str(p) for p in pd.period_range("2026-01", "2026-04", freq="M")]
grid = pd.MultiIndex.from_product([["Indore", "Pune"], months], names=["city", "month"])
full = monthly.set_index(["city", "month"]).reindex(grid, fill_value=0).reset_index()
full["prev"] = full.groupby("city")["revenue"].shift(1)
growth = full["revenue"] / full["prev"].where(full["prev"] > 0) - 1
full["growth_pct"] = (100 * growth).round(1)
sql = duckdb.sql("""
WITH months AS (SELECT strftime(m, '%Y-%m') AS month
FROM range(DATE '2026-01-01', DATE '2026-05-01', INTERVAL 1 MONTH)
AS t(m)),
grid AS (SELECT c.city, m.month
FROM (SELECT DISTINCT city FROM monthly) AS c CROSS JOIN months AS m),
filled AS (SELECT g.city, g.month, coalesce(r.revenue, 0) AS revenue
FROM grid AS g LEFT JOIN monthly AS r USING (city, month)),
with_prev AS (SELECT *,
lag(revenue) OVER (PARTITION BY city ORDER BY month) AS prev
FROM filled)
SELECT city, month, revenue, prev,
round(100 * (revenue / nullif(prev, 0) - 1), 1) AS growth_pct
FROM with_prev
ORDER BY city, month
""").df()
same_rows(full, sql)
# Without the guard, growth from zero comes out as infinity in both tools
unguarded_pandas = (full["revenue"] / full["prev"] - 1).iloc[3]
unguarded_sql = duckdb.sql("SELECT 140 / 0 - 1").fetchone()[0]
print("Indore's April without the guard:", unguarded_pandas, "in pandas,",
unguarded_sql, "in DuckDB")Output
Row-to-row growth for Indore in April: +40% city month revenue prev growth_pct Indore 2026-01 80 NaN NaN Indore 2026-02 100 80.0 25.0 Indore 2026-03 0 100.0 -100.0 Indore 2026-04 140 0.0 NaN Pune 2026-01 100 NaN NaN Pune 2026-02 120 100.0 20.0 Pune 2026-03 150 120.0 25.0 Pune 2026-04 120 150.0 -20.0 DuckDB's SQL returns the same 8 rows. Indore's April without the guard: inf in pandas, inf in DuckDB
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 period_growth.py
pct_change computes the change from the previous row, not from the previous month
(pandas pct_change). Indore has no March
row, so its April row sits right after February, and the +40 % it prints compares April with February. Build the
complete calendar first: one row for every city and month, with revenue 0 where there were no orders. pandas does it
with reindex on a MultiIndex.from_product, SQL with a range of months cross-joined with the cities and
left-joined to the data. Then March shows −100 %, and April has no growth at all, because growth from zero is
undefined: divide by prev.where(prev > 0) in pandas and by nullif(prev, 0) in SQL. Without that guard both tools
return infinity, as the last line shows.
Two more things to say aloud. A month that has not finished always looks like a fall, so leave it out or label it as
partial. And division differs between SQL dialects: DuckDB’s / turns two integers into a decimal result, while
PostgreSQL truncates integer division, so 120 / 100 is 1 there
(DuckDB,
PostgreSQL). Cast one side to a decimal type when the
query may run on another database.
Gaps and islands
The task: an invoice series should have no holes. Which numbers are missing, and which unbroken runs were issued? One invoice number was sent twice.
# Gaps and islands: which invoice numbers are missing, and which unbroken runs were issued.
import duckdb
import pandas as pd
from agree import same_rows
# Invoice 1010 was sent twice
invoices = pd.DataFrame({"n": [1001, 1002, 1003, 1006, 1007, 1009, 1010, 1010, 1011, 1012]})
# pandas, gaps: sort the distinct numbers; wherever the next number is more than 1 away,
# the numbers in between are missing
s = invoices["n"].drop_duplicates().sort_values().reset_index(drop=True)
nxt = s.shift(-1)
gap = nxt - s > 1
gaps = pd.DataFrame({"missing_from": s[gap] + 1,
"missing_to": (nxt[gap] - 1).astype("int64")})
sql_gaps = duckdb.sql("""
SELECT n + 1 AS missing_from, next_n - 1 AS missing_to
FROM (SELECT n, lead(n) OVER (ORDER BY n) AS next_n
FROM (SELECT DISTINCT n FROM invoices))
WHERE next_n - n > 1
ORDER BY n
""").df()
same_rows(gaps, sql_gaps)
# pandas, islands: a new run starts wherever the step from the previous number is not 1
run = (s.diff() != 1).cumsum()
islands = s.groupby(run).agg(first="min", last="max", issued="size").reset_index(drop=True)
# SQL, islands: inside a run, n minus its row number stays the same
sql_islands = duckdb.sql("""
SELECT min(n) AS first, max(n) AS last, count(*) AS issued
FROM (SELECT n, n - row_number() OVER (ORDER BY n) AS run
FROM (SELECT DISTINCT n FROM invoices))
GROUP BY run
ORDER BY first
""").df()
same_rows(islands, sql_islands)
# Without DISTINCT, the second 1010 shifts the row numbers after it: the runs overlap
broken = duckdb.sql("""
SELECT min(n) AS first, max(n) AS last, count(*) AS issued
FROM (SELECT n, n - row_number() OVER (ORDER BY n) AS run FROM invoices)
GROUP BY run
ORDER BY first
""").df()
runs = "; ".join(f"{r.first}-{r.last} ({r.issued})" for r in broken.itertuples())
print("Without DISTINCT:", runs)Output
missing_from missing_to
1004 1005
1008 1008
DuckDB's SQL returns the same 2 rows.
first last issued
1001 1003 3
1006 1007 2
1009 1012 4
DuckDB's SQL returns the same 3 rows.
Without DISTINCT: 1001-1003 (3); 1006-1012 (5); 1009-1010 (2)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 gaps_islands.py
These are two questions about one sequence. The gaps are the missing numbers: wherever the next number is more than 1
away, the numbers in between are missing, which shift(-1) finds in pandas and LEAD() in SQL. The islands are the
unbroken runs. In pandas a new run starts wherever the step from the previous number is not 1, and a cumulative sum of
those starts labels each run. In SQL, n - ROW_NUMBER() stays the same along a run, so you can group by it. Both
methods need the distinct numbers first. The repeated 1010 shifts every row number after it, and the last line shows
what comes out without DISTINCT: runs that overlap, which no real sequence has. The same idea finds streaks, such as
a customer’s run of consecutive active days.
Sessions
The task: split each user’s events into sessions, where more than 30 minutes without an event starts a new session.
# Sessions: split each user's events; over 30 minutes without an event starts a new one.
import duckdb
import pandas as pd
from agree import same_rows
events = pd.DataFrame({
"user_id": ["u1", "u2", "u1", "u1", "u1", "u2", "u1"],
"ts": pd.to_datetime(["2026-03-01 09:00", "2026-03-01 09:05", "2026-03-01 09:10",
"2026-03-01 09:40", "2026-03-01 10:11", "2026-03-01 11:00",
"2026-03-01 10:20"]),
})
# pandas: sort, take the gap to the user's previous event, flag the starts, count the flags
e = events.sort_values(["user_id", "ts"]).copy()
gap = e.groupby("user_id")["ts"].diff()
starts = gap.isna() | (gap > pd.Timedelta(minutes=30))
e["session"] = starts.astype("int64").groupby(e["user_id"]).cumsum()
per_session = (e.groupby(["user_id", "session"], as_index=False)
.agg(start=("ts", "min"), end=("ts", "max"), events=("ts", "size")))
sql = duckdb.sql("""
WITH flagged AS (
SELECT user_id, ts,
CASE WHEN lag(ts) OVER w IS NULL OR ts - lag(ts) OVER w > INTERVAL 30 MINUTE
THEN 1 ELSE 0 END AS starts
FROM events
WINDOW w AS (PARTITION BY user_id ORDER BY ts)),
numbered AS (
SELECT *, sum(starts) OVER (PARTITION BY user_id ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS session
FROM flagged)
SELECT user_id, session, min(ts) AS start, max(ts) AS "end", count(*) AS events
FROM numbered
GROUP BY user_id, session
ORDER BY user_id, session
""").df()
same_rows(per_session, sql)Output
user_id session start end events
u1 1 2026-03-01 09:00:00 2026-03-01 09:40:00 3
u1 2 2026-03-01 10:11:00 2026-03-01 10:20:00 2
u2 1 2026-03-01 09:05:00 2026-03-01 09:05:00 1
u2 2 2026-03-01 11:00:00 2026-03-01 11:00:00 1
DuckDB's SQL returns the same 4 rows.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 sessions.py
Sort each user’s events by time, measure the gap to the same user’s previous event, flag the events that start a
session (the user’s first event, or one after a gap of more than 30 minutes), and number the sessions with a running
count of the flags. In pandas that is diff, a comparison and cumsum; in SQL, LAG, a CASE and a running SUM
with a ROWS frame. u1’s event at 09:40 comes exactly 30 minutes after the one before and stays in the first session,
while the next, 31 minutes later, starts the second. Say which side of the boundary exactly 30 minutes falls on before
you write > or >=.
Match across tables or time: cohorts, funnels, no match
Retention by cohort
The task: of the people who signed up in each month, what share ordered one, two and three months later? The data ends in April.
# Retention by cohort: of each month's sign-ups, what share ordered 1, 2 and 3 months later?
import duckdb
import pandas as pd
from agree import same_rows
users = pd.DataFrame({
"user_id": ["a1", "a2", "a3", "a4", "b1", "b2", "b3", "c1", "c2", "d1", "d2"],
"signup": pd.to_datetime(["2026-01-03", "2026-01-10", "2026-01-20", "2026-01-28",
"2026-02-02", "2026-02-14", "2026-02-25", "2026-03-05",
"2026-03-18", "2026-04-02", "2026-04-09"]),
})
orders = pd.DataFrame({
"user_id": ["a1", "a1", "a1", "a2", "a3", "b1", "c1", "d1"],
"day": pd.to_datetime(["2026-01-15", "2026-02-05", "2026-03-10", "2026-02-20",
"2026-04-02", "2026-04-11", "2026-04-01", "2026-04-20"]),
})
data_ends = pd.Period("2026-04", freq="M") # the last month in the data
# pandas: each order's month number since sign-up, then distinct users per cohort and month
u = users.assign(cohort=users["signup"].dt.to_period("M"))
o = orders.merge(u, on="user_id")
o["k"] = ((o["day"].dt.year - o["signup"].dt.year) * 12
+ (o["day"].dt.month - o["signup"].dt.month))
active = (o[o["k"].between(1, 3)].groupby(["cohort", "k"])["user_id"].nunique()
.rename("active").reset_index())
# Only the cells the data can see: month k of a cohort exists once cohort + k has happened
size = u.groupby("cohort").size()
seen = [(c, k) for c in size.index for k in (1, 2, 3) if c + k <= data_ends]
cells = pd.DataFrame(seen, columns=["cohort", "k"])
cells = cells.merge(active, on=["cohort", "k"], how="left")
cells["active"] = cells["active"].fillna(0).astype("int64")
cells["users"] = cells["cohort"].map(size)
cells["retained_pct"] = (100 * cells["active"] / cells["users"]).round(1)
cells["cohort"] = cells["cohort"].astype(str)
sql = duckdb.sql("""
WITH cohorts AS (SELECT user_id, signup, strftime(signup, '%Y-%m') AS cohort
FROM users),
sizes AS (SELECT cohort, count(*) AS users FROM cohorts GROUP BY cohort),
active AS (SELECT c.cohort, date_diff('month', c.signup, o.day) AS k,
count(DISTINCT o.user_id) AS active
FROM orders AS o JOIN cohorts AS c USING (user_id)
GROUP BY c.cohort, k),
cells AS (SELECT s.cohort, s.users, t.k
FROM sizes AS s CROSS JOIN range(1, 4) AS t(k)
WHERE date_diff('month', strptime(s.cohort, '%Y-%m'), DATE '2026-04-01')
>= t.k)
SELECT cells.cohort, cells.k, coalesce(a.active, 0) AS active, cells.users,
round(100 * coalesce(a.active, 0) / cells.users, 1) AS retained_pct
FROM cells LEFT JOIN active AS a ON a.cohort = cells.cohort AND a.k = cells.k
ORDER BY cells.cohort, cells.k
""").df()
same_rows(cells[["cohort", "k", "active", "users", "retained_pct"]], sql)
triangle = (cells.pivot(index="cohort", columns="k", values="retained_pct")
.reindex(size.index.astype(str)))
print(triangle.to_string())Output
cohort k active users retained_pct 2026-01 1 2 4 50.0 2026-01 2 1 4 25.0 2026-01 3 1 4 25.0 2026-02 1 0 3 0.0 2026-02 2 1 3 33.3 2026-03 1 1 2 50.0 DuckDB's SQL returns the same 6 rows. k 1 2 3 cohort 2026-01 50.0 25.0 25.0 2026-02 0.0 33.3 NaN 2026-03 50.0 NaN NaN 2026-04 NaN NaN NaN
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 retention_cohorts.py
A cohort is everyone who signed up in the same month, and its month-k retention is the share of the cohort that ordered k calendar months after the sign-up month. Three details decide whether the table is right:
- The month number is the difference in calendar months: year × 12 + month of the order, minus the same for the
sign-up. DuckDB’s
date_diff('month', …)counts the same thing, the month boundaries between two dates (DuckDB date functions). - The denominator is everyone in the cohort, including the people who never ordered again: a4, b2 and b3 count.
- Zero or not yet. The February cohort’s month 1 is 0.0 %: March is in the data and nobody from February ordered then. Its month 3 is empty, because that month comes after the data ends. The code first lists the cells the data can see, then fills those without orders with 0. Filling every empty cell with 0 would say that the April cohort is already lost.
The result is the familiar triangle: each newer cohort has one month fewer to show. Expect the follow-up question: why is the bottom-right corner empty?
Cohort Retention Analyzer Build the same retention triangle from your own export of orders or logins.Funnel conversion
The task: of the users who visited, how many then added something to the cart, and how many of those then paid?
# Funnel conversion: of the users who visited, how many then added to cart, and then paid?
import duckdb
import pandas as pd
from agree import same_rows
events = pd.DataFrame({
"user_id": ["u1", "u1", "u1", "u2", "u2", "u3", "u4",
"u4", "u4", "u5", "u5", "u6", "u6"],
"event": ["visit", "cart", "pay", "visit", "cart", "visit", "cart",
"visit", "pay", "visit", "pay", "visit", "pay"],
"ts": pd.to_datetime(["10:00", "10:05", "10:09", "10:01", "10:07", "10:02", "09:58",
"10:03", "10:06", "10:04", "10:12", "10:05", "10:15"],
format="%H:%M"),
})
separately = events.groupby("event")["user_id"].nunique().to_dict()
print("Users with each event, counted separately:", separately)
# pandas: each step counts only if it happened after the user reached the step before
def first_after(step, earlier):
"""The first time each user did `step` at or after their time in `earlier`."""
e = events[events["event"] == step]
e = e.merge(earlier.rename("after"), left_on="user_id", right_index=True)
return e[e["ts"] >= e["after"]].groupby("user_id")["ts"].min()
visit = events[events["event"] == "visit"].groupby("user_id")["ts"].min()
cart = first_after("cart", visit)
pay = first_after("pay", cart)
steps = pd.DataFrame({"step": ["visit", "cart", "pay"],
"users": [len(visit), len(cart), len(pay)]})
steps["pct_of_previous"] = (100 * steps["users"] / steps["users"].shift(1)).round(1)
sql = duckdb.sql("""
WITH v AS (SELECT user_id, min(ts) AS t FROM events WHERE event = 'visit'
GROUP BY user_id),
c AS (SELECT e.user_id, min(e.ts) AS t FROM events AS e JOIN v USING (user_id)
WHERE e.event = 'cart' AND e.ts >= v.t GROUP BY e.user_id),
p AS (SELECT e.user_id, min(e.ts) AS t FROM events AS e JOIN c USING (user_id)
WHERE e.event = 'pay' AND e.ts >= c.t GROUP BY e.user_id),
counts AS (SELECT 1 AS n, 'visit' AS step, count(*) AS users FROM v
UNION ALL SELECT 2, 'cart', count(*) FROM c
UNION ALL SELECT 3, 'pay', count(*) FROM p)
SELECT step, users,
round(100 * users / lag(users) OVER (ORDER BY n), 1) AS pct_of_previous
FROM counts ORDER BY n
""").df()
same_rows(steps, sql)Output
Users with each event, counted separately: {'cart': 3, 'pay': 4, 'visit': 6}
step users pct_of_previous
visit 6 NaN
cart 2 33.3
pay 1 50.0
DuckDB's SQL returns the same 3 rows.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 funnel.py
Counting the users of each event separately gives 6 visits, 3 carts and 4 payments: more payments than carts, which no funnel can have. u4 added to the cart before visiting, and u5 and u6 paid without a cart event in the data. An ordered funnel counts a step only when it happens after the step before: each user’s first visit, then their first cart at or after that visit, then their first payment at or after that cart. That gives 6, 2 and 1. A third of the visitors reached the cart, half of those paid, and one visitor in six did both.
Agree on three choices before you count: whether the steps must happen in order, whether they must fall in one session or within a time limit, and whether the funnel counts users or sessions. Each choice changes the numbers, and the interviewer usually has one of them in mind.
Rows with no match
The task: customers who ordered in March but not in April.
# Rows with no match: customers who ordered in March but not in April.
import duckdb
import pandas as pd
from agree import same_rows
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4, 5, 6, 7, 8],
"customer_id": ["C1", "C2", "C2", "C3", "C1", "C4", "C5", "C2"],
"month": ["2026-03", "2026-03", "2026-03", "2026-03",
"2026-04", "2026-04", "2026-03", "2026-04"],
})
# pandas: March's customers, once each, minus every customer with an April order
# (how="left_anti" is new in pandas 3.0)
march = orders.loc[orders["month"] == "2026-03", ["customer_id"]].drop_duplicates()
april = orders.loc[orders["month"] == "2026-04", ["customer_id"]]
lapsed = march.merge(april, on="customer_id", how="left_anti").sort_values("customer_id")
sql = duckdb.sql("""
SELECT DISTINCT m.customer_id
FROM orders AS m
WHERE m.month = '2026-03'
AND NOT EXISTS (SELECT 1 FROM orders AS a
WHERE a.customer_id = m.customer_id AND a.month = '2026-04')
ORDER BY m.customer_id
""").df()
same_rows(lapsed, sql)
# The same filter with isin, and what an inner join would have answered instead
no_april = ~march["customer_id"].isin(april["customer_id"])
print("isin agrees:", sorted(march.loc[no_april, "customer_id"]))
stayed = march.merge(april.drop_duplicates())["customer_id"]
print("An inner join gives the customers who stayed:", sorted(stayed))Output
customer_id
C3
C5
DuckDB's SQL returns the same 2 rows.
isin agrees: ['C3', 'C5']
An inner join gives the customers who stayed: ['C1', 'C2']
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 anti_join.py
This is an anti-join: the rows of one set that have no partner in another. pandas 3.0 added how="left_anti" to
merge (pandas merge), and ~isin()
gives the same list when the key is one column. In SQL, NOT EXISTS with a correlated subquery says it directly, and
LEFT JOIN … WHERE the right-hand key IS NULL is the other common form. Take March’s customers once each first: C2
ordered twice in March, and an answer is a list of customers, not of orders. An inner join answers the opposite
question, the customers who came back.
Summarise or reshape: medians and pivots
Median by group
The task: the typical delivery time in each city. One Kochi order has no delivery time yet, and one took 90 minutes.
# Median by group: typical delivery time per city, with one time missing and one extreme.
import duckdb
import numpy as np
import pandas as pd
from agree import same_rows
deliveries = pd.DataFrame({
"city": ["Pune", "Pune", "Pune", "Pune", "Kochi", "Kochi", "Kochi", "Kochi", "Indore"],
# one Kochi order has no delivery time yet
"minutes": [22, 35, 41, 58, 30, 33, np.nan, 90, 25],
})
# pandas: size counts rows, count counts times; median and mean skip the missing time
summary = (deliveries.groupby("city", as_index=False)
.agg(orders=("minutes", "size"), timed=("minutes", "count"),
median=("minutes", "median"), mean=("minutes", "mean"))
.round(1))
sql = duckdb.sql("""
SELECT city, count(*) AS orders, count(minutes) AS timed,
round(median(minutes), 1) AS median, round(avg(minutes), 1) AS mean
FROM deliveries
GROUP BY city
ORDER BY city
""").df()
same_rows(summary, sql)Output
city orders timed median mean Indore 1 1 25.0 25.0 Kochi 4 3 33.0 51.0 Pune 4 4 38.0 39.0 DuckDB's SQL returns the same 3 rows.
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 median_by_group.py
The median is the middle of the sorted values, and the mean of the middle two when the count is even: Pune’s 35 and
41 give 38.0. pandas and DuckDB both leave the missing time out, so Kochi’s median is the middle of three values, 33.0.
Say which count you report: size counts all four Kochi orders, count only the three with a time. The mean shows why
the task asks for a median: one 90-minute delivery pulls Kochi’s mean to 51.0 while the median stays at 33.0. DuckDB
has a median function. PostgreSQL has none, and there you write
percentile_cont(0.5) WITHIN GROUP (ORDER BY minutes)
(PostgreSQL aggregate functions).
Pivot and unpivot
The task: one column per month for a report, then back to one row per city and month.
# Pivot and unpivot: a column per month for a report, then back to a row per city and month.
import duckdb
import pandas as pd
from agree import same_rows
long = pd.DataFrame({
"city": ["Pune", "Pune", "Pune", "Kochi", "Kochi", "Pune"],
"month": ["2026-01", "2026-02", "2026-03", "2026-01", "2026-03", "2026-03"],
# Pune has two March rows; Kochi has no February row
"revenue": [100, 120, 90, 70, 80, 60],
})
# pandas: pivot_table sums repeated city-month pairs (pivot would raise an error on them)
wide = (long.pivot_table(index="city", columns="month", values="revenue", aggfunc="sum")
.reset_index())
wide.columns.name = None
# SQL: one conditional sum per month, which every SQL database understands
sql = duckdb.sql("""
SELECT city,
sum(CASE WHEN month = '2026-01' THEN revenue END) AS "2026-01",
sum(CASE WHEN month = '2026-02' THEN revenue END) AS "2026-02",
sum(CASE WHEN month = '2026-03' THEN revenue END) AS "2026-03"
FROM long GROUP BY city ORDER BY city
""").df()
same_rows(wide, sql)
# Back to long rows: melt keeps Kochi's empty February, and DuckDB's UNPIVOT leaves it out
# unless told otherwise
melted = wide.melt(id_vars="city", var_name="month", value_name="revenue")
months = '"2026-01", "2026-02", "2026-03"'
dropped = duckdb.sql(f"SELECT * FROM wide UNPIVOT (revenue FOR month IN ({months}))").df()
kept = duckdb.sql(
f"SELECT * FROM wide UNPIVOT INCLUDE NULLS (revenue FOR month IN ({months}))").df()
print(f"melt: {len(melted)} rows; UNPIVOT: {len(dropped)} rows;",
f"UNPIVOT INCLUDE NULLS: {len(kept)} rows")Output
city 2026-01 2026-02 2026-03 Kochi 70.0 NaN 80.0 Pune 100.0 120.0 150.0 DuckDB's SQL returns the same 2 rows. melt: 6 rows; UNPIVOT: 5 rows; UNPIVOT INCLUDE NULLS: 6 rows
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 pivot_unpivot.py
pivot_table turns the rows into one column per month and sums Pune’s two March rows. pivot would stop with
“Index contains duplicate entries, cannot reshape”, because it takes exactly one value per cell. Kochi has no
February row, so its cell is empty (NaN), which also turns the month columns into floats. Use fill_value=0 only when
a missing cell really means no sales. In SQL, one sum(CASE WHEN month = … THEN revenue END) per column works in every
database, and DuckDB also has a PIVOT statement.
Going back to rows, melt keeps the empty cell as a row with NaN, 6 rows. DuckDB’s UNPIVOT left it out in this
run, 5 rows, and kept it once INCLUDE NULLS was added
(DuckDB UNPIVOT). Check the row count after every reshape.
The same moves in polars
When you may choose the tool, polars is a third option, and it writes the same moves as expressions:
# Three patterns in polars: group_by().head(), window expressions with over(), an anti-join.
import polars as pl
sales = pl.DataFrame({
"city": ["Pune", "Pune", "Pune", "Pune", "Kochi", "Kochi", "Kochi", "Indore"],
"restaurant": ["Masala Hub", "Dosa Den", "Chai Point", "Vada Corner",
"Fish Fry", "Appam House", "Biryani Bay", "Poha Stop"],
"revenue": [5200, 4100, 4100, 3000, 3900, 3900, 2800, 2500],
})
# Top N per group: sort with the tie-breaker, then keep the first two rows of each city
top2 = (sales.sort(["city", "revenue", "restaurant"], descending=[False, True, False])
.group_by("city", maintain_order=True).head(2))
print(top2)
events = pl.DataFrame({
"user_id": ["u1", "u2", "u1", "u1", "u1", "u2", "u1"],
"ts": ["09:00", "09:05", "09:10", "09:40", "10:11", "11:00", "10:20"],
}).with_columns(pl.col("ts").str.to_time("%H:%M"))
# Sessions: diff() and cum_sum() inside over("user_id") work per user, as groupby does
starts = pl.col("gap").is_null() | (pl.col("gap") > pl.duration(minutes=30))
sessions = (events.sort(["user_id", "ts"])
.with_columns(gap=pl.col("ts").diff().over("user_id"))
.with_columns(session=starts.cast(pl.Int64).cum_sum().over("user_id"))
.group_by("user_id", "session", maintain_order=True)
.agg(start=pl.col("ts").min(), events=pl.len()))
print(sessions)
orders = pl.DataFrame({
"customer_id": ["C1", "C2", "C2", "C3", "C1", "C4", "C5", "C2"],
"month": ["2026-03", "2026-03", "2026-03", "2026-03",
"2026-04", "2026-04", "2026-03", "2026-04"],
})
# Rows with no match: an anti-join keeps March customers with no April order
march = orders.filter(pl.col("month") == "2026-03").select("customer_id").unique()
april = orders.filter(pl.col("month") == "2026-04")
lapsed = march.join(april, on="customer_id", how="anti").sort("customer_id")
print(lapsed["customer_id"].to_list())Output
shape: (5, 3) ┌────────┬─────────────┬─────────┐ │ city ┆ restaurant ┆ revenue │ │ --- ┆ --- ┆ --- │ │ str ┆ str ┆ i64 │ ╞════════╪═════════════╪═════════╡ │ Indore ┆ Poha Stop ┆ 2500 │ │ Kochi ┆ Appam House ┆ 3900 │ │ Kochi ┆ Fish Fry ┆ 3900 │ │ Pune ┆ Masala Hub ┆ 5200 │ │ Pune ┆ Chai Point ┆ 4100 │ └────────┴─────────────┴─────────┘ shape: (4, 4) ┌─────────┬─────────┬──────────┬────────┐ │ user_id ┆ session ┆ start ┆ events │ │ --- ┆ --- ┆ --- ┆ --- │ │ str ┆ i64 ┆ time ┆ u32 │ ╞═════════╪═════════╪══════════╪════════╡ │ u1 ┆ 1 ┆ 09:00:00 ┆ 3 │ │ u1 ┆ 2 ┆ 10:11:00 ┆ 2 │ │ u2 ┆ 1 ┆ 09:05:00 ┆ 1 │ │ u2 ┆ 2 ┆ 11:00:00 ┆ 1 │ └─────────┴─────────┴──────────┴────────┘ ['C3', 'C5']
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 polars_moves.py
After a sort, group_by("city", maintain_order=True).head(2) keeps the first two rows of each city. over("user_id")
runs diff and cum_sum once per user, as groupby does in pandas
(Polars window functions), and
join(how="anti") is the anti-join (Polars joins). The
answers match the pandas ones above: the same five restaurants, the same four sessions, C3 and C5.
A take-home, start to finish
A take-home gives you a dataset, a few questions and a deadline of a day or more. The write-up is judged as closely as the code: whether the answer is easy to find, whether every number can be traced back to the data, and whether you say what the data cannot show. A README in this order covers all three:
- The question, restated in your own words, with the decision it serves.
- The answer first: two to four sentences with the numbers that matter.
- Data and checks: the rows received, each cleaning rule with the rows it touched, and the rows analysed.
- Method: the definitions (what counts as late, active or a session), the grain and the time window.
- Results: one table or chart per question, each with its definition.
- Limitations and next steps: what the data cannot tell, how uncertain small groups are, what to check next.
- How to run: one command, the versions used, and where the raw data goes.
Here is a short README for a synthetic food-delivery dataset, followed by the script whose output it quotes:
# Late deliveries: where and when
## The question
Operations believe late deliveries are a Kochi problem. They asked where and when orders
arrive after the promised 35 minutes, and how many orders that affects, to decide where
the next riders should work.
## The answer
Late deliveries are mostly an evening problem, in Kochi and in Pune. Between 7 and 11 pm,
55.2 % of Kochi's orders and 35.6 % of Pune's arrived late, against 18.2 % and 11.7 % at
other hours. Indore stays at 15 to 16 % all day. Evening orders are 41 % of all orders but
63 % of the late ones. Overall, 23.4 % of 1,769 delivered orders were late.
## Data and checks
| Step | Rows |
| ----------------------------------------------- | ----: |
| Rows received | 1,812 |
| Exact duplicate rows removed (an export glitch) | 12 |
| Cancelled, no delivery time: left out | 27 |
| Delivered before they were ordered: left out | 4 |
| Orders analysed | 1,769 |
## Method
- Late: delivered more than 35 minutes after the order was placed, the time the app
promises.
- Evening: ordered between 19:00 and 22:59.
- Each share is late orders divided by delivered orders, per city and time slot.
## Limitations
- The smallest cell, Indore in the evening, has 189 orders, so its share is uncertain by
about 5 points either way (a 95 % interval); the 0.6-point gap between Indore's two
slots is well inside that.
- The data has no distances, weather or kitchen times, so it shows when orders are late,
not why.
- The four rows delivered before they were ordered suggest a clock problem that may also
shift other times.
## Next steps
Compare riders on shift with orders per hour in Kochi and Pune evenings, and ask whether
35 minutes is the right promise for evening orders.
## How to run
`python analysis.py` with Python 3.14.8 and pandas 3.0.6. It rebuilds the synthetic
orders from a fixed seed and prints every number in this file.# A take-home in miniature: where and when are food deliveries late? The orders are
# synthetic, made by a seeded generator so that every run prints the same numbers; in a real
# take-home this block reads the file you were given.
import numpy as np
import pandas as pd
rng = np.random.default_rng(2026)
n = 1800
city = rng.choice(["Pune", "Kochi", "Indore"], size=n, p=[0.45, 0.30, 0.25])
by_hour = np.array([2, 4, 9, 10, 8, 4, 3, 4, 6, 11, 12, 10, 6, 3]) / 92 # 10:00 to 23:00
hour = rng.choice(np.arange(10, 24), size=n, p=by_hour)
ordered_at = (pd.Timestamp("2026-03-02") + pd.to_timedelta(rng.integers(0, 56, n), unit="D")
+ pd.to_timedelta(hour, unit="h")
+ pd.to_timedelta(rng.integers(0, 60, n), unit="min"))
dinner = (hour >= 19) & (hour <= 22)
extra = np.where(dinner & (city == "Kochi"), 9, np.where(dinner & (city == "Pune"), 4, 0))
minutes = np.round(rng.normal(28, 7, n) + extra)
orders = pd.DataFrame({"order_id": np.arange(1, n + 1), "city": city,
"ordered_at": ordered_at,
"delivered_at": ordered_at + pd.to_timedelta(minutes, unit="min"),
"promised_min": 35})
# Cancelled orders, a clock error on four rows, and an export glitch that repeats 12 rows
orders.loc[rng.choice(n, 27, replace=False), "delivered_at"] = pd.NaT
orders.loc[[101, 707, 1203, 1650], "delivered_at"] -= pd.Timedelta(hours=1)
orders = pd.concat([orders, orders.sample(12, random_state=7)]).reset_index(drop=True)
# The checks, each logged with the rows it touched
log = [("rows received", len(orders))]
orders = orders.drop_duplicates()
log.append(("exact duplicate rows removed", log[0][1] - len(orders)))
cancelled = orders["delivered_at"].isna()
log.append(("cancelled, no delivery time (left out)", int(cancelled.sum())))
orders = orders[~cancelled].copy()
orders["minutes"] = (orders["delivered_at"] - orders["ordered_at"]).dt.total_seconds() / 60
impossible = orders["minutes"] <= 0
log.append(("delivered before ordered (left out)", int(impossible.sum())))
orders = orders[~impossible]
log.append(("orders analysed", len(orders)))
for step, rows in log:
print(f"{step:<40}{rows:>6,}")
# The answer: the share of orders delivered after the promised time, by city and time of day
orders["late"] = orders["minutes"] > orders["promised_min"]
evening_hours = orders["ordered_at"].dt.hour.between(19, 22)
orders["slot"] = np.where(evening_hours, "7-11 pm", "other hours")
by = orders.groupby(["city", "slot"]).agg(orders=("late", "size"), late=("late", "sum"))
by["late_pct"] = (100 * by["late"] / by["orders"]).round(1)
print(f"\nLate overall: {orders['late'].mean():.1%} of {len(orders):,} orders")
print(by.to_string())
small = by.loc[by["orders"].idxmin()] # the cell with the fewest orders
p = small["late"] / small["orders"]
# A 95 % interval with the normal approximation, in percentage points
margin = 100 * 1.96 * np.sqrt(p * (1 - p) / small["orders"])
print(f"Smallest cell: {int(small['orders'])} orders,",
f"95 % margin about ±{margin:.1f} points")
evening = orders["slot"].eq("7-11 pm")
share_late = evening[orders["late"]].mean()
print(f"Placed between 7 and 11 pm: {evening.mean():.0%} of orders,",
f"{share_late:.0%} of late orders")Output
rows received 1,812
exact duplicate rows removed 12
cancelled, no delivery time (left out) 27
delivered before ordered (left out) 4
orders analysed 1,769
Late overall: 23.4% of 1,769 orders
orders late late_pct
city slot
Indore 7-11 pm 189 30 15.9
other hours 275 42 15.3
Kochi 7-11 pm 212 117 55.2
other hours 319 58 18.2
Pune 7-11 pm 320 114 35.6
other hours 454 53 11.7
Smallest cell: 189 orders, 95 % margin about ±5.2 points
Placed between 7 and 11 pm: 41% of orders, 63% of late orders
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 analysis.py
The script logs each check with the number of rows it touched, so the “Data and checks” table is copied from its output, never retyped. The answer contradicts the brief, which expected a Kochi problem, and says so in its first sentence. The limitations give the uncertainty of the smallest group, 189 orders and a 95 % margin of about 5 points, which stops a reader from treating the 0.6-point gap between Indore’s two time slots as a finding. Code that rebuilds every number from the raw data with one command is what makes the rest believable.
Two timed drills
Set a timer for each. Both drills are plain Python, so they run in the browser against their sample tests, and each is a pattern from this lesson in the form an interviewer can ask without any library.
Exercise · Medium · Python
Number each user's sessions from an event log
A timed drill: give yourself 10 minutes. Write sessionise(events, gap_minutes=30) in sessions.py, in plain Python: the same steps the lesson takes with diff, a comparison and cumsum in pandas, or LAG and a running SUM in SQL.
events is a list of (user, minute) pairs in any order, where minute is a whole number of minutes since the start of the day. Return a new list of (user, minute, session) tuples, sorted by user and then by minute, where:
- each user's first event starts session 1;
- an event more than
gap_minutesafter the same user's previous event starts the next session (2, 3 and so on); - an event exactly
gap_minutesafter the previous one stays in the same session.
For example, sessionise([("u1", 0), ("u1", 30), ("u1", 61)]) returns [("u1", 0, 1), ("u1", 30, 1), ("u1", 61, 2)]: 30 minutes is not more than 30, and 31 is. The function must not change the list it is given.
The sample tests check the boundary, several users in a shuffled list, another gap, an empty list and the input left as it was.
Starter code · sessions.py
def sessionise(events, gap_minutes=30):
"""Return (user, minute, session) tuples sorted by user and minute; a gap of more than gap_minutes starts a session."""
# Replace these lines with your code.
return [(user, minute, 1) for user, minute in events]The sample tests · test_sessions.py
from sessions import sessionise
def test_boundary():
"""keeps an event exactly 30 minutes later in the session and starts a new one after 31"""
assert sessionise([("u1", 0), ("u1", 30), ("u1", 61)]) == [("u1", 0, 1), ("u1", 30, 1), ("u1", 61, 2)]
def test_shuffled_users():
"""sorts by user and minute, and numbers each user's sessions from 1"""
events = [("u2", 95), ("u1", 70), ("u2", 5), ("u1", 0), ("u1", 20), ("u2", 30), ("u1", 150)]
assert sessionise(events) == [
("u1", 0, 1), ("u1", 20, 1), ("u1", 70, 2), ("u1", 150, 3),
("u2", 5, 1), ("u2", 30, 1), ("u2", 95, 2),
]
def test_other_gap():
"""uses the gap it is given"""
assert sessionise([("a", 0), ("a", 10), ("a", 21), ("a", 31)], gap_minutes=10) == [
("a", 0, 1), ("a", 10, 1), ("a", 21, 2), ("a", 31, 2),
]
def test_empty():
"""returns an empty list for no events"""
assert sessionise([]) == []
def test_input_unchanged():
"""leaves the list it is given as it was"""
events = [("u1", 50), ("u1", 0)]
sessionise(events)
assert events == [("u1", 50), ("u1", 0)]A hint
Sort a copy first: sorted(events) orders the pairs by user and then by minute. Walk through it once, remembering the previous user and minute. When the user changes, start again at session 1; when the user is the same and minute - previous_minute > gap_minutes, add 1 to the session number.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Exercise · Medium · Python
Count an ordered funnel from raw events
A timed drill: give yourself 15 minutes. Write funnel(events, steps=("visit", "cart", "pay")) in funnel.py, in plain Python, with the rule the lesson's funnel example uses.
events is a list of (user, event, minute) tuples in any order. Return a list with one number per step: how many users reached that step. A user reaches
- the first step at the earliest time they did it;
- each later step at the earliest time they did it at or after the time they reached the step before.
A user who has not reached a step cannot reach the steps after it, and events of a step done before the previous step do not count. Events whose name is not in steps are ignored. Each user counts at most once per step.
For example, a user with ("u1", "cart", 5), ("u1", "visit", 8) and ("u1", "pay", 12) reaches only the first step: the cart came before the visit. So funnel([("u1", "cart", 5), ("u1", "visit", 8), ("u1", "pay", 12)]) returns [1, 0, 0].
The sample tests check a complete path, a skipped step, events in the wrong order, repeated events and other steps.
Starter code · funnel.py
def funnel(events, steps=("visit", "cart", "pay")):
"""Return how many users reached each step, each step at or after the time the user reached the step before."""
# Replace these lines with your code: this version counts every step separately.
return [len({user for user, event, minute in events if event == step}) for step in steps]The sample tests · test_funnel.py
from funnel import funnel
def test_complete_path():
"""counts a user at every step when the steps happen in order"""
assert funnel([("u1", "visit", 0), ("u1", "cart", 4), ("u1", "pay", 9)]) == [1, 1, 1]
def test_skipped_step():
"""does not count a payment by a user who never reached the cart"""
events = [("u1", "visit", 0), ("u1", "pay", 3), ("u2", "visit", 1), ("u2", "cart", 2)]
assert funnel(events) == [2, 1, 0]
def test_wrong_order():
"""ignores a cart added before the visit, but counts a later one"""
events = [("u1", "cart", 5), ("u1", "visit", 8), ("u1", "pay", 12),
("u2", "cart", 1), ("u2", "visit", 2), ("u2", "cart", 6)]
assert funnel(events) == [2, 1, 0]
def test_repeated_events():
"""counts each user once per step, from the first time that qualifies"""
events = [("u1", "visit", 0), ("u1", "visit", 7), ("u1", "cart", 3), ("u1", "cart", 4), ("u1", "pay", 3)]
assert funnel(events) == [1, 1, 1]
def test_other_steps():
"""works for any list of steps and ignores other events"""
events = [("a", "signup", 0), ("a", "search", 2), ("a", "book", 5), ("b", "search", 1), ("b", "signup", 3)]
assert funnel(events, steps=("signup", "book")) == [2, 1]A hint
First collect, for each user, the minutes of each step: a dictionary such as {"u1": {"visit": [0, 7], "cart": [3, 4]}}. Then walk the steps in order for each user, keeping the time of the step just reached (start with no limit for the first step). The next step counts when the user has a time at or after that one; take the smallest such time.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Check yourself
Six questions on the patterns
6 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.
Interview questions
Fresher · How do you split each user’s events into sessions with a 30-minute inactivity rule, in pandas and in SQL?
Sort the events by user and time, and compare each event with the same user’s previous one. An event starts a new
session when it is the user’s first, or when more than 30 minutes have passed since the previous event; a running
count of those starts numbers the sessions. In pandas, gap = df.groupby("user_id")["ts"].diff(), then
starts = gap.isna() | (gap > pd.Timedelta(minutes=30)) and starts.astype(int).groupby(df["user_id"]).cumsum(). In
SQL, LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) gives the previous time, a CASE turns each gap into 1 or 0,
and SUM(...) OVER (PARTITION BY user_id ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) numbers the
sessions. Say which side of the boundary exactly 30 minutes falls on, and how two events with the same timestamp are
ordered.
Fresher · What belongs in the README of a take-home analysis?
Enough for a reader to find the answer in a minute and to trust it. Restate the question and the decision it serves. Give the answer first, in two to four sentences with the key numbers. List the data checks: the rows received, each cleaning rule with the rows it removed or changed, and the rows analysed. Define the method: what counts as late, active or a session, the grain of each table and the time window. Show one table or chart per question. State the limitations: what the data cannot show, how uncertain small groups are, and what you would check next. End with how to run it: one command that rebuilds every number from the raw data, with the versions you used.
Key takeaways
- Before any code, say the grain, the keys, what a missing value means and how ties are broken.
- Twelve recurring tasks rest on four moves: rank inside a group, compare with the row before, match across tables or time, and summarise or reshape.
- Put a tie-breaker in every sort that feeds a top N or a latest-record answer, in pandas and in SQL alike.
- A SQL running total without a
ROWSframe gives rows with the sameORDER BYvalue the same total. - Complete the calendar before computing growth, and treat growth from zero as undefined.
- In a retention table, a month the data has not reached is empty, not 0 %.
- A funnel counts a step only when it happens after the step before.
- A take-home README leads with the answer, logs every check with row counts, states its limits and rebuilds every number from the raw data with one command.
References
- Comparison with SQL (pandas documentation) (pandas development team (NumFOCUS))
- Group by: split-apply-combine (pandas user guide) (pandas development team (NumFOCUS))
- pandas API reference: DataFrameGroupBy.head (pandas development team (NumFOCUS))
- pandas API reference: DataFrame.merge (pandas development team (NumFOCUS))
- pandas API reference: Series.pct_change (pandas development team (NumFOCUS))
- pandas API reference: DataFrame.duplicated (pandas development team (NumFOCUS))
- pandas API reference: DataFrame.pivot_table (pandas development team (NumFOCUS))
- pandas API reference: DataFrame.melt (pandas development team (NumFOCUS))
- Window functions (DuckDB documentation) (DuckDB Foundation)
- Numeric functions: division operators (DuckDB documentation) (DuckDB Foundation)
- Date functions: date_diff (DuckDB documentation) (DuckDB Foundation)
- Aggregate functions: median (DuckDB documentation) (DuckDB Foundation)
- PIVOT statement (DuckDB documentation) (DuckDB Foundation)
- UNPIVOT statement (DuckDB documentation) (DuckDB Foundation)
- SQL on pandas (DuckDB documentation) (DuckDB Foundation)
- Value expressions: window function calls (PostgreSQL documentation) (PostgreSQL Global Development Group)
- Mathematical functions and operators (PostgreSQL documentation) (PostgreSQL Global Development Group)
- Aggregate functions: ordered-set aggregates (PostgreSQL documentation) (PostgreSQL Global Development Group)
- Window functions (Polars user guide) (Polars)
- Joins (Polars user guide) (Polars)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress