Interview guides · Argus Media

Argus Media Data Scientist Interview Questions & Guide 2026

Data scientist prep for Argus Media: practice defining a streaming and media metric, picking its guardrails and defending the tradeoff.

The 60-second brief

Argus Media is a leading global provider of energy and commodity price benchmarks, market data, and analytical insights. In this highly specialized environment, a Data Scientist plays a pivotal role in translating vast, complex, and often unstructured physical market data into actionable intelligence. Rather than building generic software models, data scientists here construct the mathematical representations of physical markets that global businesses rely on to manage risk, trade commodities, and allocate resources.

Allocate prep to your weakest link rather than your favourite topic. Of the three things that usually gate the outcome (SQL that is correct under messy joins, sound reasoning about experiments, and structured framing of an open-ended problem), candidates tend to over-invest in modelling theory and under-invest in framing.

Focus areas

Define a qualified stream and defend it
Separate release-calendar spikes from underlying trend
Measure catalogue breadth beyond head consumption

Overview

The impact of this role is immediate and far-reaching. By developing sophisticated forecasting models, optimization algorithms, and predictive pipelines, you will directly contribute to core proprietary products, such as the company’s flagship Possibility Curves (forward curves). Your work will enable market participants to understand supply-demand dynamics, price volatility, and future market trends across crude oil, natural gas, chemicals, agriculture, and metals.

To succeed in this position, you must possess a unique blend of quantitative rigor, financial curiosity, and software engineering discipline. The data science team at Argus Media operates at the intersection of quantitative finance, machine learning, and domain-specific market analysis, making it an intellectually stimulating environment for professionals who thrive on solving high-stakes, real-world mathematical challenges.

The Argus Media interview process

Argus Media candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

HR Screening Call

Whoever runs this call is usually not a practitioner. They take notes, and a hiring manager skims those notes later, so the real question is whether your work survives being written down by someone outside the field. Test every project sentence against that: could a non-specialist repeat it correctly without knowing what a propensity score is? Carry a plain-language version of each project and one reason you want this particular role that you could not copy onto another application. Vagueness at this stage reads as inexperience, even when the underlying work was genuinely deep.

What to demonstrate

Whether a non-specialist can restate your projects accurately, since their paraphrase is what reaches the hiring manager
Whether your reason for wanting the role points at the work itself rather than the company's reputation
Whether your language signals the level being screened for: what you decided yourself versus what you were handed

How to prepare

Write a two-sentence, jargon-free version of each major project: the question nobody could answer, and the decision your work changed. Read it to someone outside data and have them repeat it back
Point your 'why this role' answer at something concrete in the job description or the product surface you would be working on, and keep it to two sentences
Have two questions ready about measurement: which metric the team is held to, and who acts on an analysis once it lands

Take-Home Assessment

Your submission is read asynchronously by someone who cannot ask you a clarifying question, and who will usually skim it before reading it properly. That changes what good looks like. The conclusion belongs near the top with the supporting analysis beneath it, and the code should run start to finish on a clean machine without a manual step you forgot to document. A reviewer forced to reconstruct your reasoning from the order of notebook cells is already discounting the work. The submissions that land are the ones where a busy reader gets the answer immediately and can verify it if they want to.

What to demonstrate

Whether the answer arrives before the methodology, so a reader who stops after the first page still has the recommendation
Whether the code runs end to end from the submitted files, with dependencies and data paths declared rather than assumed
Whether each chart is legible on its own, carrying axis labels and units, and exists to support a claim made in the text

How to prepare

Restructure a past analysis so the opening paragraph holds the recommendation and the number behind it, then check that nothing later in the document quietly contradicts it
Copy your own submission into an empty directory, run it in a clean environment, and fix everything that breaks; hidden local state is caught here or by the reviewer
For every chart, write the one sentence it is meant to prove, and delete the chart if you cannot write that sentence

Virtual Interviews

Rounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.

What to demonstrate

Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options

How to prepare

Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.

Where candidates lose points

Collapsing cancellation and payment failure into one churn number.

Involuntary churn from expired or declined payment instruments is a large and volatile share of gross churn, and it responds to retry schedules, card-updater coverage and billing provider, not to anything in the product. It also resolves late, so a period that looks involuntary today can be a successful retry next week, and reading the split before the dunning horizon closes overstates it. Compounding this, cancel-at-period-end means the cancellation request and the entitlement end are different timestamps on different rows, so a churn curve keyed on cancel_requested_ts and one keyed on churn_ts disagree by a full billing period.

Reading recommendation quality from click-through or play-through on logged slates.

Slate position drives interaction independently of relevance, and the ranker that produced the log also decided what got exposure, so logged data is confounded by the policy you are trying to evaluate. Inverse-propensity estimators fix this only when the logging propensities are known and every item had a non-zero chance of being shown; without exploration slots that positivity condition fails silently and the estimate is confidently wrong. The visible symptom is a feedback loop: offline metrics improve, consumption concentrates further into the head of the catalogue, and the online test does not reproduce the offline gain.

Averaging per-user rates to produce a population rate

Decide which quantity you want: the mean of per-user ratios and the ratio of summed numerator to summed denominator are different estimands, and heavy users dominate one but not the other. For a ratio metric, aggregate numerator and denominator separately and use the delta method for its variance.

Reporting a mean for a heavy-tailed metric without saying what it hides

For spend, session length or items per order, a small fraction of units carries most of the total, so the mean has a wide standard error and one account can move it. Fix the handling before you see the result: cap or winsorise at a pre-declared percentile, and report the median or the share above a threshold next to the mean. Capping changes the estimand, so say which question the capped number answers, and check how much of any difference comes from the top 0.1 percent of units.

Questions & practice

Explain the theoretical difference between bagging and boosting, and when you would choose one over the other.

Medium · statistics and probability

Approach

Sanity-check the answer against a simple bound or a simulated case.
Quantify uncertainty explicitly rather than reporting a point estimate alone.
Write down the assumption the method needs before you use the method.

Follow-up

Which assumption here is most likely to be violated in practice?
How would you explain this result to someone who does not know statistics?

Describe a machine learning paper you read recently. What were its key contributions, and how would you implement its findings?

Medium · machine learning and modelling

Approach

Say how the offline result would be validated online before it is trusted.
Pick an evaluation metric that matches the cost of each error type, not a default.
Set a baseline first, so any model has something honest to beat.

Follow-up

How would you choose the decision threshold, and who owns that choice?
Where could label leakage enter this setup?

Explain how you would debug a silent failure in a distributed machine learning pipeline.

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
Say how the offline result would be validated online before it is trusted.
Check what information would not exist at prediction time, and exclude it.

Follow-up

Where could label leakage enter this setup?
How would you choose the decision threshold, and who owns that choice?

Five invariant checks over a playback fact table

Easy · data-quality · pandas · validation

fct_stream arrives with stream_id, started_at, ended_at (nullable), played_seconds, max_position_seconds, completion_ratio, is_qualified, event_date, and duration_seconds joined from the content dimension (null for live events). Write one function returning a tidy frame with a row per rule: rule name, eligible rows, violations, violation share, one example stream_id. The five rules are duplicate stream_id; played_seconds exceeding wall-clock ended_at minus started_at; max_position_seconds exceeding duration_seconds; completion_ratio disagreeing with max_position_seconds divided by duration_seconds; and event_date not equal to the UTC date of started_at.

Approach

Give every rule its own eligible-row mask before its violation mask. Rules 2 to 4 are undefined where ended_at or duration_seconds is null, and a comparison against NaN evaluates False, so a mostly-null column otherwise reports a clean bill of health.
Compare the recomputed completion_ratio with a tolerance (absolute difference above 1e-6), never with ==. It is stored as a float division and exact equality fails on rows that are correct.
Allow a small tolerance on rules 2 and 3 too: a final segment can carry the playhead a second or two past duration_seconds, and heartbeat timestamps come off client clocks. State the tolerance you chose rather than burying it.
Return violations and eligible rows as separate columns so the share has a stated denominator, and carry one example stream_id per rule so the output is actionable rather than a number.
Read rule 5 as a signal rather than corruption: event_date drifting from the UTC date of started_at is the signature of offline playback uploaded after the partition closed, and its distinct dates are the recompute list.

Worked solution (20 min)

Write a helper taking (name, eligible_mask, violation_mask) and returning one result row, which forces every rule to declare its eligible population.
Rule 1: duplicated('stream_id', keep=False), eligible is all rows. Rule 5: event_date not equal to started_at.dt.date, eligible is all rows.
Rules 2 to 4: eligible masks are ended_at.notna() and duration_seconds.notna(); violation masks are played_seconds above wall_seconds plus 2, max_position_seconds above duration_seconds plus 2, and the absolute completion_ratio difference above 1e-6.
Take one example stream_id per rule with .loc[mask].head(1), guarding the empty case so a clean rule does not raise.
Concatenate the five rows and sort by violation share descending.

Expected result: A five-row frame with columns rule, eligible_rows, violations, violation_share and example_stream_id, where eligible_rows for rules 2 to 4 is strictly below the table length whenever ended_at or duration_seconds contains nulls.

Verify it yourself

Injecting one row whose played_seconds is ten times its wall clock raises exactly rule 2's count by one and leaves the other four unchanged.
Setting duration_seconds to null on that same row moves it out of the eligible counts for rules 3 and 4, rather than into their violation counts.
violation_share is violations divided by that rule's eligible_rows, not by len(df); the two differ for rules 2 to 4.
Rule 1's violation count is even when duplicates arrive in pairs, because keep=False flags both copies.

Follow-up

Rule 5 fires on 0.4 percent of rows, all from one app_version, all with reported_at days after started_at. Is that a bug, and what do you do about the daily numbers already published?
How would you turn these into a blocking check in the pipeline without failing the load every time one client version misbehaves?
Which of the five would you expect to fire on live events specifically, and how do you keep them out of the denominator?

Walk me through how you would structure a clean, reproducible data pipeline for daily price updates.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Check whether any join is one-to-many before aggregating, or the sums inflate.

Follow-up

What breaks if events arrive late or out of order?
How would you verify this result without re-running the same query?

How do you ensure your local code matches production standards in terms of testing and documentation?

Medium · sql and data manipulation

Approach

Check whether any join is one-to-many before aggregating, or the sums inflate.
Compute rates by summing numerator and denominator separately, never by averaging rates.
Say which table is the grain you start from, and join outward from it.

Follow-up

How does the query change if the join becomes one-to-many?
What breaks if events arrive late or out of order?

Home-row impressions that never converted, and an empty result

Medium · anti-join · null semantics · attribution

fct_impression has impression_id, profile_id, content_version_id, surface, slate_position, rendered_at and viewport_visible_ms. fct_stream has stream_id, profile_id, content_version_id, started_at, is_qualified and a nullable impression_id, populated only when the play started from a rendered slate. For surface = 'home_row' impressions rendered yesterday with viewport_visible_ms > 0, return content_version_id and the number of impressions that produced no qualified stream within 30 minutes. A colleague's draft filters WHERE impression_id NOT IN (SELECT impression_id FROM fct_stream) and returns zero rows on data that plainly contains misses. Explain why, then write the correct query.

Approach

Name the mechanism rather than the symptom. fct_stream.impression_id is nullable, so the subquery returns a set containing NULL. In SQL's three-valued logic x NOT IN (..., NULL) evaluates to UNKNOWN whenever x matches nothing else, never to TRUE, so the WHERE clause admits no rows. This is correct engine behaviour on correct data.
Fix with NOT EXISTS, which is a correlated existence test and is NULL-safe by construction, or with LEFT JOIN ... WHERE s.impression_id IS NULL. Adding IS NOT NULL to the subquery also works but leaves the same landmine armed for whoever edits the query next.
Put is_qualified = true and the 30-minute window inside the join or the correlated predicate, never in an outer WHERE over a LEFT JOIN. An outer filter on a right-side column turns a LEFT JOIN back into an inner join and returns the exact complement of the set you were asked for.
Match on impression_id, not on (profile_id, content_version_id). The second is a many-to-many between two event tables and will count one stream against every impression of that title for that profile.
Keep viewport_visible_ms > 0. A row that was never scrolled into view is not a recommendation that failed; it is a recommendation that was never made, and including it depresses every title's conversion by whatever share of the row went unseen.

Worked solution (20 min)

Prove the mechanism in one line: SELECT COUNT(*) FROM fct_stream WHERE impression_id IS NULL. A non-zero count is the entire explanation and should be shown before the rewrite.
Rewrite as NOT EXISTS, with is_qualified and started_at BETWEEN rendered_at AND rendered_at + INTERVAL '30 minutes' inside the correlated predicate.
Compute eligible and converted impression counts per content_version_id separately; the requested answer must be their difference.
Spot-check one high-count content_version_id by listing its impressions alongside any streams sharing those impression_ids.

Expected result: Rows of (content_version_id, unconverted_impressions) where, for every title, unconverted + converted equals the count of eligible home_row impressions with viewport_visible_ms > 0 rendered yesterday.

Verify it yourself

The NOT IN version returns zero rows while NOT EXISTS returns a non-empty result on the identical filtered data.
converted + unconverted reconciles to the eligible impression count for every content_version_id, with no title missing from either side.
Every impression counted as converted has a matching fct_stream row with is_qualified = true and started_at inside the 30-minute window.

Follow-up

Two home rows on the same screen render the same title, and the stream carries one impression_id. Should the other impression count as a miss? Defend whichever rule you pick.
Would NOT EXISTS and LEFT JOIN ... IS NULL produce the same plan here, and is there a case where you would prefer one over the other?
What attribution window between rendered_at and started_at do you allow, and why does an unbounded window overstate conversion rather than merely add noise?

How do you optimize memory usage and execution speed when handling large data frames in R?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.
Fix the population and the time window before naming any metric.

Follow-up

What would you do if the primary metric and the guardrail moved in opposite directions?
How would you detect that the metric is being gamed rather than genuinely improving?

Read a test that won early and faded late

Medium · peeking · sequential testing · novelty effect

A ranker test was declared for 21 days at 80 percent power for a 1.0 percent lift in qualified hours per account-week, alpha 0.05 two-sided, with every eligible account ramped on day one. The team watched a daily dashboard. It crossed p < 0.05 on day 3 reading plus 2.9 percent, read plus 1.7 percent at p = 0.002 on day 9, and at day 21 reads plus 0.6 percent at p = 0.09. The same dashboard reports weekly slices of plus 2.1, plus 0.4 and minus 0.7 percent. Reconcile the cumulative readings against the slices and against the declared power before interpreting either, say what the day-21 p-value is worth given the daily looks, and say what you would run next.

Approach

Reconcile the dashboard against itself before interpreting anything on it. With a day-one ramp, exposure is roughly flat across the window, so each cumulative estimate must be the day-weighted average of the slices beneath it, and the declared MDE pins the standard error at the horizon. Both the point estimates and the p-values are checkable arithmetic; a cumulative that does not match its own slices is computed on a different population and neither number is usable.
Separate the arithmetic from the decision. Because the test ran to its pre-declared 21-day horizon and nobody stopped, the day-21 p-value is a valid fixed-horizon p-value needing no correction. What the daily looks damaged is the decision rule, not this number.
Quantify what stopping would have cost. Under the null, repeated significance testing on accumulating data at alpha 0.05 gives a true error rate near 14 percent at five looks, 19 percent at ten and roughly 25 percent at twenty, so shipping on day 3 was a bet at those odds.
Recognise the day-3 estimate as conditioned on having crossed. An effect read at the moment it becomes significant is selected for being large, so it is biased upward before any decay argument is made.
Re-index the effect on days since each account's first exposure rather than on calendar date. Novelty appears as a per-account effect decaying with exposure age; a calendar decay that vanishes under re-indexing is a release or a mix shift.
Separate novelty from primacy by tenure. Accounts created after the change never saw the old surface, so a genuine novelty effect is absent or much smaller in them, while primacy has the opposite sign and grows with exposure.
Distinguish the cumulative from the steady state. A cumulative estimate over a decaying profile is an average of a launch effect the product delivers once and a post-novelty effect it delivers forever. Neither is the other, and the second is the one a ship decision needs.
Conclude at the declared horizon: not significant at 0.05, a cumulative effect around plus 0.6 percent whose post-novelty component is not separable from zero, and any further looking requires an alpha-spending boundary fixed in advance.

Worked solution (25 min)

Reconcile the cumulative readings against the slices. Day 9 = (7 * 2.1 + 2 * 0.4) / 9 = 15.5 / 9 = 1.72 percent, matching the reported plus 1.7. Day 21 = (2.1 + 0.4 - 0.7) / 3 = 0.6 percent, matching the reported plus 0.6. Back out the within-week-one profile too: days 1 to 3 at plus 2.9 forces days 4 to 7 to average (7 * 2.1 - 3 * 2.9) / 4 = 1.5 percent, so the per-day path is 2.9, 1.5, 0.4, minus 0.7, monotone decay.
Reconcile the p-values against the declared power. 80 percent power for 1.0 percent at alpha 0.05 two-sided fixes the day-21 standard error at 1.0 / 2.802 = 0.357 percent, so z = 0.6 / 0.357 = 1.68 and p = 0.09. Scaling the standard error as 1 / sqrt(t) gives 0.545 percent at day 9 (z = 3.12, p = 0.002) and 0.944 percent at day 3 (z = 3.07, p = 0.002). Every reported number falls out of the design; none of them has to be taken on trust.
Recompute the day-21 estimate outside the dashboard with one row per account and the pre-registered cap, and confirm plus 0.6 percent at p = 0.09 reproduces.
Rebuild the series indexed on days since first exposure per account rather than calendar day, and plot the treatment effect against exposure age 1 to 21.
Split the day-21 estimate by tenure at assignment: accounts created after the change versus accounts with 90 or more days of history.
Check the release and rights calendar inside the window, and confirm the week-one lift is not one arm's share of a release landing.
Estimate the steady state from the week-three slice alone: minus 0.7 percent with a one-week standard error of 0.357 * sqrt(3) = 0.618 percent, so z = minus 1.13 and p = 0.26. It is not separable from zero, and it is the quantity the follow-up must be powered on.

Expected result: Every reported figure reconciles: the cumulative readings are the day-weighted averages of the slices, and the p-values fall out of the declared 0.357 percent standard error at the horizon. The day-21 p = 0.09 therefore stands as a valid fixed-horizon test because the declared horizon was honoured, so the ship decision is no, and roughly 25 percent is the false-positive rate the team was exposed to across 21 daily looks. The 2.9 / 1.5 / 0.4 / minus 0.7 per-day path re-indexed on exposure age is the novelty signature. Powering a re-run for plus 0.6 percent needs (1.0 / 0.6)^2 = 2.78 times the sample, about 58 days, and is the wrong design: plus 0.6 percent is an average over a decaying profile, not an effect the product will keep delivering. Power the post-novelty slice instead. Reaching a 0.7 percent MDE on a one-week slice whose standard error is currently 0.618 percent needs (0.618 / 0.250)^2 = 6.1 times that slice's exposure, about 43 days of measurement after novelty has decayed, on top of a burn-in at least as long as the decay observed here.

Verify it yourself

Each cumulative reading must equal the exposure-weighted average of the slices beneath it, to within rounding. A gap means the cumulative and the slices are computed on different denominators or different populations, and the dashboard is reporting two incompatible experiments.
1 / sqrt(t) is a floor on the standard error, because within-account correlation across days makes precision accumulate more slowly than independent sampling would. The reconstructed early z-scores are therefore upper bounds. If the dashboard's own error bars are tighter than 1 / sqrt(t) implies, it is treating account-days as independent observations.
Re-indexing on exposure age flattens a release-driven decay and preserves a novelty-driven one, but only if the ramp was staggered. With everyone exposed on day one, exposure age is collinear with calendar day and this cut cannot separate them; the tenure split is then the only available separation.
Accounts with no exposure to the old surface should show a smaller early lift than tenured accounts if novelty is real. Equal lifts in both argue against it.
An A/A run over the same 21 days should cross p < 0.05 at least once on roughly a quarter of replicates, which is the direct demonstration of the peeking rate rather than an argument about it.

Follow-up

Specify a boundary that lets the team look daily and still ship at 5 percent. What does an O'Brien-Fleming boundary cost in final-look MDE compared with a Pocock boundary?
The team proposes re-running the test powered for plus 0.6 percent. What is wrong with that target, and which quantity should the follow-up be powered on instead?
What would primacy look like on this dashboard, and which metric would register it first?

Consumption fell the week a new ranker shipped

Hard · confounding · catalogue-supply · causal-inference

Qualified hours fell 4% in the seven days after a home-row ranker shipped to 100%, and a rollback is being demanded for tomorrow. You have fct_stream (played_seconds, is_qualified, content_version_id, start_source, started_at, play_territory), fct_impression (ranker_version, content_version_id, rendered_at), dim_content_version (content_id, parent_id, rights_expires_at, available_territories, catalogue_added_ts, is_active_version), and no holdout — the rollout went from 5% straight to 100%. Determine whether the ranker caused the fall. Deliverable: a defensible causal read, or an explicit statement of what cannot be concluded and what you would run instead.

Approach

Check supply before demand. Sum the prior period's qualified hours on content_version_ids whose rights_expires_at falls inside the window, whose is_active_version flipped, or whose available_territories lost a territory. If titles carrying several points of hours left the catalogue that Monday, the fall is partly accounted for before the ranker is even discussed.
Separate the mechanically unavailable from the merely deranked. Hours lost on items still available but no longer surfaced belong to the ranker; hours lost on items that cannot legally be served do not, and no ranking change recovers them. Only the first category is evidence for a rollback.
Recover a comparison the rollout destroyed. With no holdout, use the 5% phase as a pre-period and territories unaffected by the expiry as a control, running difference-in-differences with the expiry as the treatment. State the parallel-trends assumption out loud and test it on the four pre-window weeks; abandon the estimator if it fails rather than reporting it with a caveat.
Measure displacement instead of gross hours. For accounts whose usual items left, did total hours fall or did they substitute within the catalogue? Fully substituted removal costs nothing in hours and shows up only in the affected rights holder's payout, which is a different problem with a different owner.
Ask separately what the ranker did to exposure: impression share by content_id under each ranker_version, and top-1% exposure concentration. A ranker that concentrates exposure can depress hours through a mechanism nobody has named yet, and this is what distinguishes that story from the rights story.
Refuse the causal claim the rollout cannot support. Say plainly that a 5%-to-100% ramp across a confounded week yields no unbiased effect estimate, and propose the switchback or re-randomised holdout that would, with its cost stated.

Follow-up

Design the switchback: slice length, burn-in long enough to clear carryover on a personalised surface, and how the standard error handles correlation between adjacent slices.
If the rollback is ordered regardless of your analysis, what do you measure during the rollback to get a second, independent read?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

Where your data came from

Data people depend on systems owned by other teams, and much of the job is negotiating for instrumentation, access, or a fix to a broken pipeline. Prepare an example of getting something changed upstream that you did not control. Describe what you asked for, what you traded, and how you worked while you waited.

What are the primary packages you use in R for data visualization, and how do you customize them for stakeholder presentations?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Name the disagreement or constraint, and how you resolved it with evidence.
Quantify the outcome, including what you would not claim credit for.

Follow-up

What did you decide not to do, and why?
What would you do differently if you ran that project again?

What is your experience with using LLM as a judge to evaluate model outputs or unstructured text?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Name the disagreement or constraint, and how you resolved it with evidence.
State the situation in two sentences and spend the rest on your reasoning.

Follow-up

What did you decide not to do, and why?
What would you do differently if you ran that project again?

Disagree with a product manager about a completion metric

Medium · disagreement · metric design · influence

A product manager proposes making duration-normalised completion rate the team's primary metric for the quarter, and wants a plain global unweighted version "so it's simple enough to put on a wall." You expect the unweighted version to move several points on catalogue mix alone, with no change in how satisfying anything was. You have one meeting, they own the roadmap, and you will work with them for years. Deliverable: the argument, the evidence you bring, and the fallback you accept if they still want the simple version.

Approach

The probe is whether you can lose the decision without damaging either the metric or the relationship. Bring the failure already reproduced: recompute the unweighted global rate over the last two quarters and point at the weeks it moved several points where the only input that changed was which content people played.
Decompose the variance instead of objecting. Attribute the movement in the unweighted rate to between-cell mix — (content_type, duration decile) — versus within-cell movement. A decomposition is an argument; an assertion that mix matters is a preference.
Concede the real cost honestly: the mix-weighted version is harder to explain and harder to recompute. Bring the mitigation rather than dismissing the objection — weights fixed from a stated reference month, published once, so anyone can reproduce the number without redoing the weighting.
Offer the fallback deliberately and in advance: ship both, with the unweighted rate labelled a diagnostic and the weighted one as the decision metric, and pre-agree the one observation that would mean the simple version has misled the team.
Leave the decision with them, with the consequence written down before it happens. That is what makes a later correction a shared prediction coming true rather than a retrospective argument about who was right.

Follow-up

They ship the simple version and it moves four points during a heavy release week. How do you raise it without saying you told them so?
Is there a case where the unweighted rate is genuinely the right primary metric?
Suppose you are wrong and the mix effect turns out to be small. What would you change about how you argued this?