Interview guides · Extra Space Storage

Extra Space Storage Data Scientist Interview Questions & Guide 2026

A two-week prep plan for Extra Space Storage data scientist candidates: write your answer before reading the solution, revisit misses three days later.

The 60-second brief

As a Data Scientist at Extra Space Storage, you will play a pivotal role in harnessing data to drive decision-making processes across the organization. This position is critical as it directly impacts product development, operational efficiency, and customer satisfaction. You will be working with vast datasets to uncover insights that influence business strategies and enhance user experiences, making your contributions essential to the company's growth.

Coding rounds for this role are usually data-manipulation shaped rather than data-structure shaped: group-bys, joins, time windows, ranking within a partition. Confirm the format before spending a week on graph traversal.

Focus areas

Separate authorization, settlement and dispute outcomes cleanly
Set fraud thresholds by expected cost
Read vintage curves, not blended portfolio averages

Overview

In this role, you will collaborate closely with product teams, engineers, and business stakeholders to address complex challenges such as optimizing storage solutions, improving customer engagement, and forecasting demand trends. The work is both intellectually stimulating and strategically significant, as you will be at the intersection of data analysis, machine learning, and business acumen. Expect to be involved in projects that require innovative thinking and a deep understanding of statistical methods, ensuring that Extra Space Storage stays ahead in a competitive market.

The Extra Space Storage interview process

Extra Space Storage candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

HR Screening

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical Interview

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

Whether your row counts survive each join, and whether you notice on your own when they do not
Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Where candidates lose points

Using written premium as the denominator of a loss ratio

Premium is written at inception and earned pro rata across the exposure period, so in a growing book written premium runs ahead of earned premium and the loss ratio comes out too low, with the error reversing when the book shrinks. The numerator has the mirror-image problem if it omits incurred-but-not-reported reserves, since recent accident periods then look profitable twice over. Both sides must refer to the same exposure period, which is what an accident-period view at a fixed development age enforces.

Assuming a model is fair because protected attributes are not among its inputs

Postcode, device, tenure, income proxies and even transaction patterns correlate with protected characteristics, so a model can produce a disparate outcome without ever reading the attribute. Credit decisions additionally carry an explainability obligation in many jurisdictions, since a denial has to be accompanied by its principal reasons, which constrains model form and feature engineering rather than being a reporting afterthought. Treating fairness testing and reason-code generation as design constraints from the first model version is far cheaper than retrofitting them to a deployed one.

Sizing estimates built on unnamed, unrevisable assumptions

Write each assumption as a named number you can change, then show the arithmetic so the interviewer can challenge one input instead of the whole answer. Finish by saying which assumption the result is most sensitive to, which matters more than the point estimate.

Interpreting a change before checking data quality and logging

Spend the first pass on row volume by day, null rates, duplicate keys, and whether the step change lands on a release or tracking-migration date. A discontinuity that coincides with a deploy is an instrumentation hypothesis before it is a behavioural one.

Questions & practice

Explain the significance of p-values in hypothesis testing.

Medium · statistics and probability

Approach

Translate the result into the decision it informs, in one plain sentence.
Quantify uncertainty explicitly rather than reporting a point estimate alone.
Say what the estimate is of, and over what population it generalises.

Follow-up

What sample size would you need to detect an effect half this size?
Which assumption here is most likely to be violated in practice?

Explain the concept of overfitting and how to prevent it.

Medium · machine learning and modelling

Approach

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.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

What would you monitor after launch to know the model is still valid?
How would you choose the decision threshold, and who owns that choice?

Describe a time when you successfully implemented a machine learning model.

Medium · machine learning and modelling

Approach

Set a baseline first, so any model has something honest to beat.
Say how the offline result would be validated online before it is trusted.
Frame the prediction: the label, the moment of prediction, and the action it triggers.

Follow-up

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

Measure calibration of a twelve-month default probability from scratch

Hard · calibration · brier score · binning

fct_loan_application gives application_id, model_pd_12m, model_version, decision, funded_at and loan_id. fct_loan_performance_monthly gives loan_id, months_on_book, days_past_due and charge_off_flag. Define the outcome as ever 90 or more days past due, or charged off, by months_on_book = 12. Without sklearn or scipy, build an equal-count binned reliability table, the expected calibration error, the Brier score and its reliability, resolution and uncertainty components, and report the residual the binned identity leaves behind. Restrict to cohorts that have actually reached 12 months on book.

Approach

Build the label first and name the population it covers out loud: only funded loans have outcomes, so this measures calibration on the approved population. The declined region is unmeasured, and no binning scheme repairs that.
Restrict to applications whose loans have reached months_on_book = 12. A cohort observed at 8 months has a mechanically lower default rate and will read as systematic over-prediction that is really just immaturity.
Bin by equal count, deciles of model_pd_12m through a rank-based cut, not equal width. The PD distribution is heavily right-skewed, so equal-width bins put most of the mass in the first bin and leave the risky bins with single-digit counts whose observed rates mean nothing.
Per bin compute n, mean predicted, observed rate, and the binomial standard error sqrt(o(1-o)/n) so a gap can be read against noise. ECE is the count-weighted mean absolute gap between mean predicted and observed.
Compute Brier directly as the mean squared error, then reliability = sum of n_k (pbar_k - obar_k)^2 over N, resolution = sum of n_k (obar_k - obar)^2 over N, uncertainty = obar(1 - obar). Report residual = Brier - (reliability - resolution + uncertainty). That identity is exact only for discrete forecasts, so with binned continuous scores the residual is the within-bin spread of the score; a large one means the bins are too wide to support the decomposition.
Split by model_version. A mixed-version population can look well calibrated in aggregate while each version is biased in opposite directions.

Worked solution (45 min)

Reduce fct_loan_performance_monthly to one row per loan_id with the maximum days_past_due and any charge_off_flag over months_on_book 0 to 12, plus the maximum months_on_book observed, and keep only loans reaching 12.
Inner-join to approved and funded applications, and record how many approved applications were dropped for immaturity and how many decisions were declines that never enter the measurement at all.
Assign deciles with a rank-based cut on model_pd_12m, then aggregate n, mean predicted, observed rate and standard error per bin.
Compute ECE, Brier, reliability, resolution, uncertainty and the residual, and print all six.
Repeat the whole computation split by model_version and compare the per-version reliability against the pooled figure.

Expected result: A ten-row reliability table with n, mean predicted, observed and standard error; an ECE of a few tenths of a percentage point to a couple of points; a Brier score close to the uncertainty term, because a rare-event model has little resolution to subtract; and a residual small relative to the reliability term.

Verify it yourself

Bin counts sum to the filtered row count, and the count-weighted mean of bin observed rates equals the overall observed rate.
Uncertainty equals obar(1 - obar) computed directly, and reliability is zero on a synthetic input where observed is set equal to predicted in every bin.
The residual shrinks as the bin count rises from 5 to 20; if it does not, the label or the binning is wrong.
Multiplying every PD by 1.5 must raise reliability and ECE while leaving resolution, uncertainty and AUC unchanged, since equal-count bins are invariant to a monotone transform.

Follow-up

AUC is unchanged after a population shift but the reliability curve has moved. What happened, and what do you do about it?
How would you recalibrate without retraining, and what would you check afterwards?
The top decile shows observed default well above predicted. Is that a calibration problem or a policy problem?

Collapse merchant retry chains before measuring authorization approval

Medium · window functions · gaps and islands · sessionisation

Merchant retry logic turns one declined purchase into several rows in fct_payment_authorization. Collapse attempts into chains: inside a partition of (card_token_id, merchant_id, amount_minor), consecutive rows whose requested_at gap is 15 minutes or less belong to the same chain. Return one row per chain with chain_started_at, attempt_count, amount_minor, channel, and chain_outcome set to 'approved' when any attempt in the chain was approved. Then report a 7-day rolling dollar-weighted approval rate by channel over chains rather than attempts. Exclude is_reversal rows.

Approach

In a CTE, take LAG(requested_at) OVER (PARTITION BY card_token_id, merchant_id, amount_minor ORDER BY requested_at) and set is_new_chain when the previous value is null or the gap exceeds 15 minutes.
Turn the flag into a chain identifier with SUM(is_new_chain::int) OVER (same partition, same order, rows unbounded preceding to current row), which is the standard gaps-and-islands construction.
Aggregate to one row per chain: MIN(requested_at), COUNT(*), and BOOL_OR(auth_result = 'approved') as the chain outcome, because the purchase succeeded if any attempt in the chain did.
Roll the chains to a daily grain per channel, then apply the rolling window as PARTITION BY channel ORDER BY chain_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW. An interval-offset RANGE frame is value-based: it admits every row in the partition whose chain_date falls in the closed window [D - 6 days, D], so a channel with no chains on some dates still gets the right seven-day sums — sparsity is exactly what this construct tolerates. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is the one that needs a dense grain, because it counts rows and on a sparse grain reaches back further than seven days.
Left-join a dense date spine only if the output needs a row on days with no chains. That is a presentation requirement, not a correctness one, and it leaves every rolling sum unchanged. The RANGE frame's preconditions are that chain_date is a date or timestamp (an interval offset needs an ordering column it can be added to) and that the aggregation left one row per (channel, chain_date).
Divide approved chain value by total chain value inside the rolling frame, never the average of daily rates, since averaging rates weights a quiet day the same as a heavy one.

Worked solution (40 min)

CTE 1: filter out is_reversal rows, then compute prev_requested_at with LAG and the is_new_chain flag.
CTE 2: build chain_id with a running SUM of the flag over the same partition and order.
CTE 3: group by partition keys plus chain_id to emit chain_started_at, attempt_count, amount_minor, channel and chain_outcome.
CTE 4: aggregate chains to (chain_date, channel) with approved value and total value. Add the date spine here only if the report must show empty days.
Final SELECT: two rolling SUMs over PARTITION BY channel ORDER BY chain_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, divided at the end.

Expected result: A rolling series with one row per date and channel, where the chain count is strictly below the attempt count on ecommerce and recurring, and the chain-level approval rate is at or above the attempt-level rate for every channel, since collapsing removes failed attempts that later succeeded.

Verify it yourself

SUM(attempt_count) over all chains equals the filtered attempt row count exactly; if it does not, the chain identifier is not partitioned the same way as the LAG.
Every chain has exactly one row whose is_new_chain flag is true, so COUNT(DISTINCT chain_id) equals the number of flagged rows.
A chain whose attempt_count is 1 must have chain_outcome equal to that single row's approved status.
The rolling denominator on day D equals the sum of the daily denominators over every date present between D-6 and D inclusive — seven terms on a dense grain, fewer on a sparse one, and the same total either way.
Adding or removing the date spine leaves every rolling sum identical, because spine rows contribute zero. Swapping RANGE for ROWS changes them on any channel whose grain has a gap, which is the direct test of which construct tolerates sparsity.

Follow-up

This definition chains off the previous attempt, so a chain can span an hour if attempts arrive every 14 minutes. When is that right, and when would you anchor the window to the first attempt instead?
Two genuine purchases of the same amount at the same merchant, four minutes apart, collapse into one chain. How large is that error and how would you bound it?
A channel goes dark for three days mid-window. Which of the two frame types would silently widen, and what would the resulting rate look like?
What changes if the merchant retries with a slightly different amount to dodge a velocity rule?

Reconcile captured authorizations against the daily settlement total

Easy · reconciliation · currency · rounding

fct_payment_authorization holds captured_amount_minor in transaction_currency, and settlement_amount_minor in settlement_currency with settlement_fx_rate applied at settlement rather than at authorization. The rate is quoted in major units of settlement_currency per major unit of transaction_currency, and dim_currency.minor_unit_exponent carries the ISO 4217 exponent for each code (0, 2 or 3 depending on the currency). Produce a daily reconciliation: for each settled_at date and settlement_currency, return settled_count, total settlement_amount_minor, and the sum of captured_amount_minor converted into settlement minor units. Flag any date and currency pair whose two totals differ by more than one minor unit per settled authorization. Do not sum amounts across currencies anywhere in the output.

Approach

Restrict to rows that actually settled: settled_at is not null and settlement_amount_minor is not null, which is a smaller population than captured rows because a capture can still be in flight.
Truncate settled_at to a date with an explicit time zone so the cut matches the ledger's cut, since settled_at is timestamptz and date_trunc on timestamptz silently uses the session time zone.
Join dim_currency twice, once on transaction_currency and once on settlement_currency, so both exponents are on the row. Minor units are not a common scale: a bare captured_amount_minor * settlement_fx_rate is correct only when the two exponents are equal, and a zero-decimal currency settling into a two-decimal one is wrong by a factor of 100.
Convert per row as ROUND(captured_amount_minor::numeric / POWER(10::numeric, exp_txn) * settlement_fx_rate * POWER(10::numeric, exp_settle)) — minor units to major in the transaction currency, apply the major-per-major rate, then back to minor units in the settlement currency. The collapsed form ROUND(captured_amount_minor::numeric * settlement_fx_rate * POWER(10::numeric, exp_settle - exp_txn)) is the same expression. Round per row and then sum, not SUM(...) * an average rate, because the rate varies row by row and rounding per row is what the settlement file did.
Group by the settlement date and settlement_currency together, never by date alone, and carry the currency into every output column name or row.
Compare the two totals with a tolerance scaled by settled_count, since per-row rounding accumulates linearly in the number of rows rather than being a fixed constant.

Follow-up

A partial capture means captured_amount_minor is less than amount_minor. Where does that show up in this reconciliation, and where does it not?
On one currency pair the converted total is consistently about one hundredth of the settlement total, on every date, while the other pairs reconcile. Which two columns do you inspect first, and what single change fixes it?
The rate is documented as major-per-major. If a feed started publishing it minor-per-minor instead, which pairs would still reconcile and which would break?
How would you present a total across currencies to a finance partner who has asked for one number?

If you were tasked with reducing customer churn, what data would you analyze?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Restate the decision this analysis has to support, and who acts on the answer.
Fix the population and the time window before naming any metric.

Follow-up

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

Given a dataset of customer purchases, how would you identify purchasing trends?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Decompose the metric into the rates that drive it, and say which one you would check first.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

Which segment would you cut first, and what would that rule out?
How would you detect that the metric is being gamed rather than genuinely improving?

How do you manage competing priorities in a fast-paced environment?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

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

Describe your process for evaluating the effectiveness of a marketing campaign using data.

Medium · product sense and metrics

Approach

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

Follow-up

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

How would you design an A/B test for a new feature in the storage service?

Medium · a/b testing and experimentation

Approach

Say whether units interfere with each other, and switch design if they do.
Decide the analysis before seeing data, including how long it runs and when you look.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

What would you do if you could not randomise at all?
What would you conclude if the result is positive but the test is underpowered?

What is the difference between supervised and unsupervised learning?

Medium

Approach

Say what you would check first and why it is the highest-information step.
Work from the decision backwards to the evidence you would need.
Clarify what is being asked and what a complete answer would contain.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Define an active customer for a card portfolio

Easy · denominator design · engagement · scd2

Leadership wants one weekly number for how many customers are active. You have fct_payment_authorization (auth_id, customer_id, auth_result, captured_at, amount_minor, is_reversal, channel) and dim_customer (customer_id, is_current, kyc_status, onboarded_at, closed_at). Write the metric definition you would publish: numerator, denominator, window, and every exclusion with its reason. Then name two ways the definition gives a wrong read, one that inflates it and one that deflates it, and say what you would report alongside it.

Approach

Fix the grain before writing anything. The number counts distinct customers, so the authorization table has to be reduced to one row per customer_id first; a customer with forty approvals counts once.
Build the numerator from money that actually moved: auth_result = 'approved' AND captured_at IS NOT NULL, excluding is_reversal = true and zero-amount verification authorizations. An approval that is never captured, or is reversed, has no economic content behind it.
Build the denominator from the population that could have transacted: dim_customer with is_current = true, kyc_status = 'verified', onboarded_at on or before the window start, and closed_at null or after the window start. Joining without is_current fans the type 2 history out and multiplies the denominator by the number of attribute versions.
State the window and its recompute cadence, trailing 30 days recomputed daily, and say plainly that this series is not comparable to a calendar-month version of the same definition.
Name the inflation path (recurring and subscription-only customers, and wallet top-ups, count as active with no user intent behind them) and the deflation path (a customer onboarded inside the window had less than 30 days of opportunity, and activity on products outside this table is invisible).
Pair it with a depth metric such as settled volume per active customer, so breadth cannot be reported on its own and an acquisition push cannot pass as engagement.

Worked solution (20 min)

Write the denominator query first and record both the raw dim_customer row count and the distinct customer_id count; their ratio is the fan-out factor the is_current filter removes.
Write the numerator as a distinct customer_id count over the trailing 30 days, applying the three exclusions one at a time and recording the count after each.
Compute the rate, then recompute with zero-amount verifications left in, and record the difference in basis points.
Write the two failure modes as one sentence each, naming the customer population affected by each.

Expected result: An eight-line definition: distinct customer_id with an approved, captured, non-reversed, non-zero-amount authorization in the trailing 30 days, over is_current, KYC-verified, open customers onboarded on or before the window start; accompanied by an exclusion log giving the customer count removed by each exclusion.

Verify it yourself

The numerator count never exceeds the denominator count. If it does, the numerator contains customers who are absent from dim_customer or whose only row is not the current version.
Total dim_customer rows divided by distinct customer_id is greater than one, which is the fan-out the is_current filter prevents.
Dropping the captured_at filter raises the numerator, and the increase concentrates in specific channels such as holds and verifications rather than spreading evenly. An even spread means the filter is removing the wrong rows.

Follow-up

A product team proposes counting any successful login as activity. What breaks?
The number jumps four percent overnight with no product change. What do you check first?
How would you report customers onboarded inside the window, given they had a shorter opportunity to transact?

A single day of settled volume comes in thirty percent low

Medium · pipeline failure · null rates · data freshness

Yesterday's settled volume is 30 percent below the same weekday across the previous eight weeks, while authorization counts and approved counts for the day look normal. You have fct_payment_authorization with requested_at, captured_at, captured_amount_minor, settled_at, settlement_amount_minor, settlement_currency, created_at and updated_at, plus warehouse load metadata. Decide within the hour whether this is a real drop or a load problem, and say what you would post to the on-call channel in either case.

Approach

Check the data before the business. Compare max(updated_at) and row counts per load partition for the affected day against the prior eight same weekdays; a truncated load usually shows as a count deficit confined to a specific hour range.
Measure null rates per column by requested_at date. Authorization fields present while settled_at and settlement_amount_minor are null points at the settlement feed; a uniform deficit across all columns points at ingestion.
Separate late arrival from loss. Settlement lands after authorization by design, so re-measure the same day 24 and 48 hours later. A deficit that closes on the next load is lag, not a drop.
Reconcile against the independent source: tie the day's settled total to the settlement ledger or acquirer file. If the ledger has the money and the warehouse does not, the business number was never wrong.
Only after those four steps consider a business explanation, and then only if a segment cut produces a coherent story rather than a flat haircut spread evenly across every merchant and channel.
Close by splitting the alert: a freshness and completeness check on the settlement feed, separate from the volume alert, so the next occurrence is classified automatically.

Follow-up

How would you make the daily volume alert immune to settlement lag without also hiding a genuine drop?
What is the right way to restate a published number after a backfill lands?
Which check catches a partial load that is uniform across the day rather than concentrated in one window?

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.

Rigour against a deadline

Nearly every data role forces a trade between the analysis you want and the one that fits the decision window. Prepare a case where you deliberately shipped something less rigorous, named the weakness to the person relying on it, and said what would change your answer. The naming is the part interviewers listen for.

How do you handle missing data in a dataset?

Medium · behavioural and stakeholder questions

Approach

Close with what you would do differently, concretely.
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

How did you know the outcome was caused by your change?
What did you decide not to do, and why?

Defend a vintage finding that contradicts the portfolio dashboard

Medium · vintage analysis · mix shift · stakeholder pushback

The lending dashboard shows blended 90-plus days-past-due falling for four consecutive quarters while originations grew 60 percent. Using fct_loan_performance_monthly, you build a vintage view keyed on origination_month by months_on_book and find the three most recent vintages are worse than their predecessors at the same age. The business lead presents that dashboard weekly and pushes back hard, suggesting you picked favourable cohorts. You get one meeting and the vintage table. Present the finding so it survives the cherry-picking objection and ends in a decision.

Approach

Reconcile before you contradict: show that aggregating your vintage table along the calendar diagonal reproduces the published blended series, so the disagreement is about age mix rather than about data quality.
Make the mechanism arithmetic rather than rhetorical: a loan cannot reach 90 days past due before it is 90 days old, so rapid origination growth shifts weight onto young months-on-book where the rate is structurally near zero.
Show every vintage rather than a selected pair, all indexed at months_on_book equal to 12, with cohort sizes printed beside each curve so nobody can claim the divergence rests on a thin cohort.
Handle restructuring explicitly, because restructured_flag resets days_past_due: count each loan on its worst pre-restructure state, or recent vintages will look better than they are.
Close on the decision rather than the chart: state what the divergence implies for the cutoff or the channel mix, and state in advance what evidence would make you withdraw the claim.

Follow-up

Two cohorts differ at month 12. How do you separate a seasoning effect from a genuine credit-quality effect?
Someone argues the recent vintages are simply a broker-channel mix shift. How do you test that, and what would confirm it?

Recommend a decision whose true outcome matures a year later

Hard · decision under censoring · leading indicators · staged rollout

An underwriting rule change must be decided in six weeks. Its real outcome, the vintage 90-plus rate at months_on_book 12 in fct_loan_performance_monthly, matures in a year. The executive wants a yes or no, not a range. Randomising the credit decision across the whole population is not available. Name the leading indicator you would accept, state its bias and the direction of that bias, define the decision rule and stopping condition before any rollout starts, and say what reading would make you recommend reversing the change.

Approach

Fix the readout before the rollout, because a readout chosen after the data arrives is a story rather than a decision rule: indicator, window, threshold and reversal condition all go in writing first.
Choose the leading indicator on its measured relationship to the matured outcome in historical vintages rather than on availability. Early delinquency, typically the share reaching dpd_1_29 or missing a first scheduled payment by months_on_book 3, is the usual candidate, and you quantify how well it predicted the 12-month rate across past cohorts.
State the bias and its direction plainly: early delinquency under-represents default that emerges later and is contaminated by servicing and payment-date effects, so treat it as a floor on risk rather than an estimate of it.
Buy identification where full randomisation is unavailable: a narrow randomised approval band around the cutoff, or a staged rollout by channel or region read as a difference-in-differences, with the parallel-trends assumption stated and checked in the pre-period rather than assumed.
Give the executive the binary they asked for with the trigger attached in the same sentence: yes, conditional on the month-3 indicator staying inside a stated band, with an automatic hold if it breaches.

Follow-up

How would you validate that the month-3 indicator predicts the 12-month outcome, and what evidence would invalidate it mid-rollout?
Compliance refuses a randomised band. What is your next-best identification strategy, and what precision do you lose by taking it?