Microsoft Data Analyst Interview Preparation Guide - Junior Level

Direct answer

Before I say anything to anyone, I confirm and precisely scope the error privately, since announcing a correction and then having to correct the correction is worse than the original mistake. Once I know exactly what's wrong and for how long, I restate only the affected historical periods, side by side with the old numbers during a transition window, communicate internally with full technical detail and externally with the same honesty at a calibrated level of detail, and I hold the line on disclosure even when a stakeholder would rather I fix it quietly and move on.

Structured elaboration

Confirm and scope before saying anything. I validate the direction and size of the error and identify precisely which historical periods and downstream reports are affected. A vague "something might be wrong" announcement forces people to make decisions on even less certain information than before, so I do this scoping work quietly first.

Timeline. Same-day, I escalate to my manager and to whoever is actively relying on the number for near-term decisions. While the fix is being built, I put a visible caveat on the live report so nobody keeps consuming the known-wrong number unknowingly. I commit to a specific date for the corrected numbers rather than an open-ended "working on it."

Internal versus external communication. Internally, people get the full technical detail: the root cause, the exact periods affected, and the corrected methodology, so they can independently verify it if they want to. Externally, whether that's customers, a board, or the public, the explanation stays plain and focused on what changed and what it means for them, with the same honesty about scope, just less technical detail.

Correcting historical numbers. I restate exactly as far back as the error actually existed, no further and no less. I publish the old and corrected numbers side by side for a transition period so nobody using either version is confused mid-flight, and I timestamp the correction so a future audit can trace exactly when and why the number changed.

Managing pushback. The most common pushback is a request to quietly fix it going forward without restating history, since the swing looks bad. I hold that a systematically wrong historical number needs disclosure proportional to how many real decisions were made on it; quietly fixing it forward just means someone finds the discrepancy themselves later, which costs more trust than owning it now.

Worked example

Say a monthly revenue dashboard used by finance and leadership had been overstating revenue for five months due to a join bug that double-counted a subset of refunded transactions. The dashboard's reported average was about $2,000,000 a month; the true figure was closer to $1,940,000, an overstatement of roughly $60,000, or about 3% of the reported number (60,000 divided by 2,000,000).

Day 0: I confirm the direction of the bug privately and escalate to the finance lead and the report's owner the same day, without yet making any broader announcement. Days 1 to 2: I confirm precisely which five months are affected and validate the corrected figure through a manual reconciliation against source transaction data, not just a second automated query that could share the same bug. Day 3: I put a visible caveat banner on the live dashboard flagging that the historical figures are under review. Day 5: the corrected numbers are ready and validated; I send the full internal restatement, showing the old and new numbers for all five affected months side by side, with the root cause explained. Day 7: since these figures had also appeared in external investor materials, a calibrated external correction goes out, plain about what changed and why, without the internal technical detail.

Pushback: the finance lead initially asks whether we can just fix the join bug going forward and leave the historical dashboard numbers alone, since restating five months of revenue looks bad. I hold the line, because those five months of numbers had already been used for quarterly forecasting and a budget allocation decision; leaving them uncorrected means anyone who later cross-references old reports against the true figures discovers the discrepancy themselves, which is a worse outcome for trust than a clean, proactive restatement now.

Trade-offs and pitfalls

Announcing before the scope is fully confirmed risks a second, more damaging correction once the real extent becomes clear. Restating further back than the error actually existed erodes trust in the other direction, by casting doubt on periods that were never actually wrong. Under-communicating externally, when the wrong numbers were seen by customers, investors, or a board, leaves the correction looking like it was buried rather than disclosed. And treating this as a purely technical fix, without investing real effort in the communication and the restatement, misses that the harder and more important part of this problem is rebuilding trust in the number, not just repairing the query.

Direct answer

Convert a sample-size target into a run duration in two steps: compute the required sample size per variant with the standard two-proportion test formula, then divide that by how much daily traffic actually lands in each variant (daily uniques times allocation), rounding up. The formula gives you a headcount; the traffic split is what turns a headcount into a calendar.

Structured elaboration

Step 1: the sample-size formula

For a two-sided test comparing baseline conversion $p_1$ against a target conversion $p_2$, at significance $\alpha$ and power $1-\beta$:

$n = \frac{\left(z_{1-\alpha/2}\sqrt{2\bar p(1-\bar p)} + z_{1-\beta}\sqrt{p_1(1-p_1)+p_2(1-p_2)}\right)^2}{(p_2-p_1)^2}, \quad \bar p = \frac{p_1+p_2}{2}$

This is per-variant sample size; assumes a two-sided test with no sequential peeking (one look at the end) and independent, one-conversion-per-user data. Sequential monitoring and multiple-comparisons corrections change this number and are a separate design decision, not part of the raw duration estimate.

Step 2: convert daily traffic into per-variant daily volume

$\text{daily per variant} = \text{daily unique visitors} \times \text{allocation share}$

Step 3: convert sample size into days

$\text{days} = \frac{n}{\text{daily per variant}}$

Round the sample size up (never down, since undershooting trades away the power you asked for) and round the resulting day count up to a whole day. If the metric has meaningful weekday/weekend variation, round further up to a whole number of weeks so every arm sees the same mix of weekdays and weekends; that decision is a separate seasonality question with its own reasoning (see the seasonality-planning answer), not something the raw formula above accounts for.

Assumptions and rounding choices worth stating out loud

• Two-sided test with a single, pre-planned final look; no interim peeking.
• Users are independent and contribute one conversion event each; no repeated exposure double counting.
• All intermediate values kept to about five significant figures before rounding the final answer, and the final day count always rounds up, not to the nearest day.
• A buffer of roughly 10-20% is a common practical addition on top of the raw day count to absorb data loss (bot filtering, QA holds, instrumentation gaps); state it as a buffer, not as part of the statistical requirement.
• If daily traffic is volatile rather than a single stable number, use a conservative (lower) daily estimate for the duration calculation rather than the average, since under-running the test is far more costly than over-running it by a day or two.

Worked example

Inputs: 60,000 daily unique eligible visitors, 50/50 allocation, baseline conversion $p_1 = 3.0\%$, desired minimum detectable effect of 8% relative, $\alpha=0.05$ two-sided ($z_{1-\alpha/2}=1.9600$), power 80% ($z_{1-\beta}=0.8416$).

Absolute MDE: $\Delta = 0.03 \times 0.08 = 0.0024$, so $p_2 = 0.0324$, $\bar p = 0.0312$.

$z_{1-\alpha/2}\sqrt{2\bar p(1-\bar p)} = 1.9600 \times \sqrt{2 \times 0.0312 \times 0.9688} = 1.9600 \times 0.2459 = 0.4819$

$z_{1-\beta}\sqrt{p_1(1-p_1)+p_2(1-p_2)} = 0.8416 \times \sqrt{0.03\times0.97 + 0.0324\times0.9676} = 0.8416 \times 0.2459 = 0.2069$

$n = \frac{(0.4819+0.2069)^2}{(0.0024)^2} = \frac{0.4744}{0.00000576} \approx 82{,}376 \text{ users per variant}$

Daily per variant: $60{,}000 \times 0.5 = 30{,}000$.

$\text{days} = \frac{82{,}376}{30{,}000} \approx 2.75 \rightarrow \text{round up to } 3 \text{ days minimum}$

Because this crosses a weekday/weekend boundary either way, the practical duration recommendation would be at least 7 days (one full week) rather than the bare 3-day statistical minimum, so weekday and weekend behavior are represented in the same proportion in both arms.

Trade-offs & pitfalls

• MDE sensitivity. The required sample size scales with the inverse square of the MDE, so halving the effect you want to detect roughly quadruples the required sample and the resulting duration; a stakeholder asking for a smaller MDE "just to be safe" is asking for a much longer test, not a marginally longer one.
• Binary-outcome formula does not transfer to continuous metrics. Revenue-per-user or time-on-task outcomes use the outcome's variance, not $p(1-p)$, in the same general formula shape; plugging a conversion-rate formula into a continuous metric silently understates or overstates the required sample.
• Shortcutting the calendar rounding. A raw day count under 7 does not mean the test is safe to run for that literal number of days; the weekly-cycle rounding matters as much as the raw arithmetic and is a common place teams cut a real corner under launch pressure.
• Traffic volatility. A single "daily uniques" number hides day-to-day swings; a duration plan built on a lucky high-traffic day will run short in practice.

Direct answer

Don't declare the old number wrong and switch overnight. Explain the change in terms partners can verify for themselves, run both definitions side by side for a defined period so people can reconcile the gap at their own pace, and give a concrete accounting of why the numbers differ before asking anyone to adopt the new one as their working reality.

Structured elaboration

1. Find out what's actually anchored to the old number. It's rarely the number itself that people resist, it's the targets, dashboards, or comp plans built on top of it. Identify those dependencies before you talk about the redefinition in the abstract.
2. Show a concrete case where the old definition misled someone. An abstract "this is more accurate" argument doesn't land. A specific example where the old calculation gave a wrong or misleading answer does.
3. Run dual reporting, don't hard-cutover. Publish both the old and new metric side by side for a fixed window so partners can watch the two track each other (or diverge) and build intuition for the new number before they have to rely on it alone.
4. Break the gap into named components. Instead of "the number moved," account for the difference: how much of the change comes from the new inclusion/exclusion criteria, how much from a data-quality fix, how much from a genuine behavior shift. A gap people can decompose feels explainable; an unexplained gap feels arbitrary.
5. Set an explicit cutover date and update every downstream artifact by name, dashboards, target-setting docs, comp formulas, rather than assuming people will notice and adjust on their own.
6. Keep the old metric available, read-only, for a grace period after cutover instead of deleting it immediately, so people can still check their own prior conclusions against it while they adjust.

Worked example

Suppose "active users" currently counts anyone who logs in during the month. The new definition additionally requires at least one core in-product action during that session, because the team found that a meaningful share of logins were automated health-checks or bounced sessions that didn't reflect real engagement. If the old metric counted 10,000 monthly logins, and historically about 30% of logins involve no core action (a figure pulled from existing session logs, not asserted), the new definition would show roughly 10,000 x (1 - 0.30) = 7,000 active users, a drop of 3,000 driven entirely by the new inclusion criterion, not by an actual usage decline. Dual reporting both numbers for a month, with that 3,000-user gap explicitly labeled "removed for lacking a core action, not a real drop," lets a marketing partner whose Q3 target was set against the old 10,000-count number understand exactly why their dashboard changed before they have to defend it to their own leadership.

Trade-offs & pitfalls

• Pitfall: cutting over immediately without a dual-reporting window. It looks like the number was changed to hit or dodge a target, even when it wasn't.
• Pitfall: mandating adoption from authority ("this is the new source of truth, use it") without walking anyone through the why. Technically correct, but it burns trust and invites people to quietly keep using their own old tracking.
• Pitfall: deleting the old metric immediately, which strands anyone mid-adjustment and turns a change-management problem into an access problem.
• Senior differentiator: treating a metric redefinition as a change-management effort you own end to end (explanation, parallel run, decomposition, migration of dependents), not just a technical correction you announce and move on from.

Direct answer

Three shapes come up over and over when you plot the percent of a cohort still active by day since signup. A sharp drop between day 0 and day 1 followed by a long, roughly flat tail usually points to an onboarding or first-session problem: most people who were going to bounce did so almost immediately, and the ones who got past that moment stick around. A steady, roughly exponential decay with no clear floor usually means the product has not yet found a stable core audience: every week keeps losing a fixed fraction of who is left, with no group that has settled into habitual use. A curve that starts flat and then drops later, sometimes weeks in, usually points to a trial, billing, or content-exhaustion event rather than a first-use problem: something specific happens at that later point that pushes people out.

Structured elaboration

The shape of a retention curve is really a summary of many individual user journeys, so before trusting the shape you should check that it is not an artifact: uneven event volume by day of week, a definition of "active" that changed mid-cohort, or a cohort that mixes very different acquisition sources into one curve. Once you trust the shape, read it as a sequence of questions rather than a single verdict.

• Sharp day-0 to day-1 drop, then a tail: look at the first-session experience itself. Was there a broken step, a confusing empty state, or a value moment that never arrived? A/B tests on the first-run flow are the natural next move, and the metric to watch is not overall retention but the specific step where people who never return stop taking actions.
• Steady exponential decay, no visible floor: this is a harder problem because nothing in the curve tells you which moment matters. The useful next step is usually to split the cohort by early behavior (did they complete a specific action in week 1?) rather than by acquisition channel, because a decay curve with no floor often hides a bimodal population: a small group behaving like the flat-tail case above, diluted by a much larger group that never really engaged.
• Flat, then a later drop: look for something that happens on a fixed schedule near that point, such as a trial ending, a subscription renewal, or running out of the content or inventory that brought people in. The fix usually lives outside the product surface you are looking at (pricing, content pipeline, notification cadence) rather than inside the first-run flow.

Worked example

Take a cohort of 200 signups with observed active-user counts of 200, 110, 84, 68, 58, 48, 40, 34 across weeks 0 through 7 (an illustrative cohort decaying from 100% to a 17% week-7 level). Retention by week is $110/200=55\%$, $84/200=42\%$, $68/200=34\%$, and so on down to $34/200=17\%$ at week 7. The steepest single-step loss is between week 0 and week 1 (45 percentage points), after which the week-over-week loss shrinks (13, 8, 5, 5, 4, 3 points). That shape, a large first-week loss followed by a slowly shrinking tail, is the "sharp drop then long tail" pattern: it says the product's onboarding, not its ongoing value, is where most of the addressable loss is concentrated, since the users who survive week 1 are churning at a much gentler and still-decreasing rate.

Trade-offs and pitfalls

Reading a shape from a single cohort is risky when the cohort is small: a 200-user cohort's week-7 point (34 users) already has meaningful sampling noise, so do not over-interpret small wiggles late in the curve as a real change in behavior. It is also easy to mistake a mix of two different curve shapes (some users who onboard well, some who never do) for one curve; if a decay curve looks steady but the cohort is large and heterogeneous, plotting it split by an early-behavior signal often reveals that the "steady decay" is really two flat-tail curves blended together.

Direct answer. Expand each view definition in place, substituting it for a plain subquery, until you're looking at the actual raw tables and joins underneath every layer, then run EXPLAIN against that fully-expanded query rather than the outer view alone, since the outer query's own plan will already reflect the full expansion but reading the SQL text itself, layer by layer, is what tells you where in that stack the expensive work actually lives.

Structured elaboration. A chain of views (a view built on another view, built on another) is transparent to the query planner, which sees straight through to the underlying tables regardless of how many logical layers you've stacked, but it's NOT transparent to a human reading the SQL, who sees only the outermost view's definition unless they deliberately expand each layer. Most databases let you inspect a view's definition directly (a system catalog or an equivalent introspection command); walk that chain, substituting each view reference for its actual underlying query, until you have one fully-expanded query with no view references left, and THAT is the query whose real execution plan you should be reading, cross-referenced against the expanded SQL so you know which line of text a given expensive plan node actually corresponds to.

Worked example. A "customer_summary" view built on top of an "active_customers" view, itself built on top of a raw customers table joined to a status lookup, might look innocuous when you only read the outermost customer_summary definition; expanding both layers might reveal that the underlying join involves an unindexed column, or a function-wrapped predicate, buried two layers down where nobody reading only the top-level report query would think to look.

Trade-offs and pitfalls. It's tempting to optimize the outer, visible query (adding an index that seems related to what the report is doing) without actually finding where the real cost lives inside the view chain; that often produces no improvement at all, since the fix has to target the actual underlying tables and predicates the expensive operator touches, which view-layer abstraction can easily hide from a reader who never expands past the top level.

Clarifying questions (grouped & specific)

• Purpose & legal basis: What is the audit purpose and legal/legal hold basis? Who requested it and do we have written approval?
• Scope & time window: Which customers (all, by region, product, segment)? Exact date range (start/end) or retention window?
• Fields & PII: Which fields are required? (e.g., name, email, phone, address, SSN/NIN, payment info, device IDs, IPs, behavioral logs). Are hashed/derivative identifiers acceptable or do you need raw values?
• Minimization & retention: Can we limit to minimum necessary fields and time slices? How long may Legal retain the data?
• Access & use: Who will access it (names, roles, orgs)? Will external parties see it? Will data be used beyond the audit?
• Security & delivery: Preferred export format (CSV/Parquet), encryption requirement, delivery channel (SFTP, secure share), MFA, logging/audit trail.
• Compliance checks: Do you need anonymized/ pseudonymized data, linkage keys, or re-identification risks assessed? Any regulatory rules to observe (GDPR, CCPA)?
• Approval & documentation: Who signs off on scope and retention? Any required Data Processing Agreement or internal DPIA?

Minimal safe deliverable

• A scoped, pseudonymized export containing only minimally necessary fields, with direct PII replaced by irreversible hashes or tokens and a separate secure mapping held only if explicitly requested and approved. Encrypted CSV/Parquet delivered via SFTP with access limited to named approvers, with an audit log and retention policy (e.g., auto-delete after X days) plus a checksum and sample records for schema validation.

Next steps to validate legal requirements

1. Confirm answers with Legal and Data Protection Officer; get written approval of scope, fields, retention, and access list.
2. Check data inventory to locate sources and assess PHI/PII sensitivity.
3. Produce a small masked sample (schema + 5–10 rows) for Legal to validate format/fields.
4. Implement export with pseudonymization, encrypt, log access, and run QA tests; capture provenance.
5. Deliver per approved method and document retention/destruction actions; update compliance records (DPIA, change logs).

Direct answer

When the training sample over-represents highly active users relative to the target population, reweight so each record's contribution matches its true population share, most simply through inverse-probability weighting (IPW) or post-stratification to known population margins. Point estimates then need weighted variance formulas (linearization or a replicate method like the bootstrap), not the plain unweighted formula, because unequal weights inflate variance beyond what the raw sample size suggests.

Structured elaboration

For most product analytics use cases, start with post-stratification: it is the simplest of the methods below and is what the worked example uses. Reach for IPW when the selection probability varies continuously rather than falling into a few known population buckets, and raking only when correcting on several dimensions at once; the variance and design-effect material further down is about sizing the resulting uncertainty once one of these is picked, not a prerequisite to understanding the core idea.

Inverse probability weighting (IPW). If $\pi_i$ is unit $i$'s probability of being selected into the sample, weight by $w_i = 1/\pi_i$ so under-sampled units count more:

$\pi_i$ is either known by design or estimated with a propensity model (e.g. logistic regression predicting "was this user included") when the sampling mechanism isn't explicit. Extreme weights (very small $\pi_i$) should be trimmed or stabilized, since a handful of huge weights can dominate the estimate and blow up its variance.

Post-stratification. When population counts $N_h$ are known for a set of strata $h$ (e.g., activity-level buckets), reweight each sampled unit in stratum $h$ by $w_i = N_h/n_h$, where $n_h$ is the sample count in that stratum. This is a special, low-variance case of IPW that only needs stratum-level population totals, not a fitted propensity model.

Raking (iterative proportional fitting). When several marginal population distributions are known (age margin, region margin, activity margin) but their joint distribution is not, iteratively rescale weights so each margin's weighted total matches the known population margin, cycling through variables until convergence. More flexible than post-stratification for multi-dimensional bias, but can produce extreme weights if the margins are mutually inconsistent with the sample.

Variance with weights. Unweighted $s/\sqrt n$ is wrong once weights vary, because unequal weighting reduces the effective sample size even though $n$ hasn't changed. The standard linearization estimator for a weighted mean is:

Alternatives: a bootstrap that resamples units and recomputes both the weights and $\hat\mu_w$ on each resample, or a weighted sandwich estimator for regression coefficients, $(X'WX)^{-1}(X'W\hat\Omega WX)(X'WX)^{-1}$ with $W=\mathrm{diag}(w_i)$, where $\hat\Omega$ is the estimated covariance of the residuals, built from squared (and, if clustered, cross-multiplied) residuals the same way the earlier per-observation variance estimate above was built for a simple weighted mean.

Kish's rule of thumb for how much precision is lost to unequal weighting is the design effect, $\mathrm{deff}\approx 1+CV(w)^2$, where $CV(w)$ is the coefficient of variation of the weights. It is a widely-used heuristic, not an exact result (see the worked example).

Worked example

Training data has 200 low-activity and 200 high-activity users, but the true population is 80% low-activity / 20% high-activity, and the two groups differ in the metric of interest (weekly minutes, mean 20 vs 90, sd 8 vs 15). Post-stratification weights are $w_i = \text{pop share} / \text{sample share}$ (pinned seed):

import numpy as np
rng = np.random.default_rng(3)
pop_share = {"low": 0.80, "high": 0.20}
true_mean = {"low": 20.0, "high": 90.0}
true_sd   = {"low": 8.0, "high": 15.0}
n_low, n_high = 200, 200
y = np.concatenate([rng.normal(true_mean["low"], true_sd["low"], n_low),
                     rng.normal(true_mean["high"], true_sd["high"], n_high)])
stratum = np.array(["low"]*n_low + ["high"]*n_high)
sample_share = {"low": n_low/400, "high": n_high/400}
w = np.array([pop_share[s]/sample_share[s] for s in stratum])
weighted_mean = np.sum(w*y)/np.sum(w)

naive_mean = y.mean()
true_pop_mean = pop_share["low"]*true_mean["low"] + pop_share["high"]*true_mean["high"]
weight_cv = w.std(ddof=0) / w.mean()
kish_deff = 1 + weight_cv**2

print(f"naive unweighted mean          = {naive_mean:.2f}   (biased away from the true population mean)")
print(f"true population mean (known)   = {true_pop_mean:.2f}")
print(f"post-stratified weighted mean  = {weighted_mean:.2f}   (matches, within sampling noise)")
print(f"weight CV = {weight_cv:.3f} -> Kish deff = 1 + CV(w)^2 = {kish_deff:.3f}")

Output:

naive unweighted mean          = 55.34   (biased away from the true population mean)
true population mean (known)   = 34.00
post-stratified weighted mean  = 34.37   (matches, within sampling noise)
weight CV = 0.600 -> Kish deff = 1 + CV(w)^2 = 1.360

Running 2,000 replicates of this design (master seed 2026) and comparing the empirical SE of the weighted-mean estimator to the empirical SE of an unweighted simple random sample of the same total size (n=400, drawn directly at the true 80/20 population shares):

R, master = 2000, np.random.default_rng(2026)
n_low_srs, n_high_srs = 320, 80   # same total n=400, drawn directly at the true 80/20 shares

weighted_means, srs_means = np.empty(R), np.empty(R)
for r in range(R):
    yl = master.normal(true_mean["low"], true_sd["low"], n_low)
    yh = master.normal(true_mean["high"], true_sd["high"], n_high)
    weighted_means[r] = np.sum(w * np.concatenate([yl, yh])) / np.sum(w)

    yl_srs = master.normal(true_mean["low"], true_sd["low"], n_low_srs)
    yh_srs = master.normal(true_mean["high"], true_sd["high"], n_high_srs)
    srs_means[r] = np.concatenate([yl_srs, yh_srs]).mean()

empirical_se_weighted = weighted_means.std(ddof=1)
empirical_se_srs = srs_means.std(ddof=1)
empirical_ratio = empirical_se_weighted / empirical_se_srs

print(f"weighted-design empirical SE ({R} reps) = {empirical_se_weighted:.3f}")
print(f"matched-size SRS empirical SE ({R} reps) = {empirical_se_srs:.3f}")
print(f"empirical SE ratio (weighted/SRS) = {empirical_ratio:.3f}         <- Kish deff predicted {kish_deff**0.5:.3f}")

Output:

weighted-design empirical SE (2000 reps) = 0.485
matched-size SRS empirical SE (2000 reps) = 0.484
empirical SE ratio (weighted/SRS) = 1.003         <- Kish deff predicted 1.166

The naive Kish formula overstated the precision loss here (it predicted a 36% variance inflation, deff = 1.360), but the exact closed-form variances tell a more precise, and different, story than that R=2,000 empirical ratio suggests:

P = {"low": 0.80, "high": 0.20}
sd = {"low": 8.0, "high": 15.0}
n_total = 400

def strat_var(n_low, n_high):
    return P["low"]**2 * sd["low"]**2 / n_low + P["high"]**2 * sd["high"]**2 / n_high

# Neyman-optimal allocation: n_h proportional to N_h * sigma_h
w_low, w_high = P["low"] * sd["low"], P["high"] * sd["high"]
n_low_ney = n_total * w_low / (w_low + w_high)
n_high_ney = n_total - n_low_ney

var_actual = strat_var(200, 200)               # the design actually used
var_srs_equiv = strat_var(320, 80)              # proportional allocation, the SRS benchmark used above
var_neyman = strat_var(n_low_ney, n_high_ney)   # optimal allocation for this sd(low), sd(high)

ratio_actual = (var_actual / var_srs_equiv) ** 0.5
ratio_neyman = (var_neyman / var_srs_equiv) ** 0.5

print(f"Neyman-optimal allocation: n_low={n_low_ney:.0f}, n_high={n_high_ney:.0f} (a {n_low_ney/n_total*100:.0f}/{n_high_ney/n_total*100:.0f} split)")
print(f"actual design used:        n_low=200, n_high=200 (a 50/50 split)")
pct_actual = abs(ratio_actual - 1) * 100
pct_neyman = abs(ratio_neyman - 1) * 100
dir_actual = "LESS" if ratio_actual > 1 else "MORE"
dir_neyman = "LESS" if ratio_neyman > 1 else "MORE"
print(f"SD ratio, actual design / SRS-equivalent = {ratio_actual:.3f}   (about {pct_actual:.0f}% {dir_actual} precise than SRS)")
print(f"SD ratio, Neyman design / SRS-equivalent = {ratio_neyman:.3f}   (about {pct_neyman:.0f}% {dir_neyman} precise than SRS)")

Output:

Neyman-optimal allocation: n_low=272, n_high=128 (a 68/32 split)
actual design used:        n_low=200, n_high=200 (a 50/50 split)
SD ratio, actual design / SRS-equivalent = 1.019   (about 2% LESS precise than SRS)
SD ratio, Neyman design / SRS-equivalent = 0.958   (about 4% MORE precise than SRS)

Neyman allocation is optimal in a specific sense: it minimizes the variance of the weighted estimate for a fixed total sample size. The formula above (n_h proportional to N_h * sigma_h) puts more sample into strata that have a larger population share, a larger standard deviation, or both, since those are exactly the strata where an additional observation buys the most reduction in overall variance. So the actual 50/50 design is not close to Neyman-optimal: the optimal split here is 68/32 toward low-activity (which has the larger population share, even though it has the smaller per-unit variance - its bigger population share dominates the allocation formula), while the actual design overshoots in the other direction by putting equal sample into both strata. The corrected read is the reverse of the original claim: this weighted design ends up about 2% less precise than a matched-size SRS-equivalent, not "almost exactly as precise," while a genuinely Neyman-optimal design would have been about 4% more precise. The R=2,000 empirical ratio of 1.003 quoted above understated this small gap due to Monte Carlo noise at that replicate count (rerunning with R=20,000 gives an empirical ratio of about 1.020, consistent with the 1.019 exact value).

Trade-offs & pitfalls

Kish's $1+CV(w)^2$ design effect is a heuristic that assumes weights are essentially unrelated to the outcome's variance structure; as the simulation shows, it can be a pessimistic (or optimistic, depending on the direction) approximation when the oversampling pattern happens to correlate with where the outcome variance actually lives - senior candidates should quote it as a rule of thumb, not a guarantee. A separate common failure is trimming extreme IPW weights without reporting the resulting bias-variance trade-off: trimming reduces variance but reintroduces some of the bias the weighting was meant to remove, and both directions should be reported. Finally, weighting corrects for observed over-representation; if activity level is a noisy proxy for the true selection driver (e.g., users who install a tracking SDK are both more "active" and unrepresentative in other ways), IPW leaves residual bias that no amount of weight-tuning fixes.

Direct answer
When an analytics or warehouse cloud bill grows faster than the value being delivered, fixing it sustainably means finding where the cost is actually concentrated (not guessing), then applying a mix of technical changes and organizational controls together, since technical fixes alone tend to get eroded by new, unmonitored usage, and organizational controls alone don't fix inefficient queries or storage that already exist.

Structured elaboration
Finding where the cost actually comes from: most cloud warehouses expose per-query or per-job cost/usage data; breaking this down by team, by report, and by query pattern usually reveals that cost is concentrated in a small number of expensive offenders (a handful of inefficient recurring queries, or a small number of teams running disproportionately expensive ad-hoc work) rather than being evenly distributed, which tells you where to focus effort first.

Technical changes, several levers together: storage costs come down through tiered storage (moving older, less-frequently-queried data to cheaper storage classes) and retention policies (not keeping data forever if it's never queried past a certain age). Compute costs come down through query optimization (fixing the specific expensive queries identified above), materialized views and precomputed aggregates for frequently-repeated expensive queries (the same discipline), and caching so repeated identical requests don't recompute from scratch. Data modeling choices (partitioning, clustering) reduce how much data a typical query has to scan.

Organizational controls: cost ownership (a team or individual accountable for a given workload's spend, so cost isn't nobody's problem), budgeting and cost alerts (a threshold that flags before a runaway cost becomes a monthly-bill surprise), tagging (so spend can actually be attributed to a team or project in the first place, which most of the technical analysis above depends on), and query quotas for ad-hoc usage (connecting to the workload-isolation discipline).

Measuring impact and guarding against regressions: after implementing changes, track cost trend against the specific levers pulled (did the queries you optimized actually get cheaper, did the retention policy actually reduce storage spend) rather than just watching the aggregate bill, since the aggregate can be affected by many things at once and won't tell you which specific change worked.

Worked example
An analytics warehouse bill has tripled year over year. Breaking cost down by team and query pattern reveals: 40% of compute cost comes from a single recurring dashboard refresh job that's scanning a full 2-year history of raw event data on every run, when it only actually needs the trailing 90 days; 25% comes from a handful of analysts running large, unbounded ad-hoc queries with no query-cost guardrails; and storage cost has grown steadily because no retention policy exists, keeping raw data indefinitely even though nothing older than a year has been queried in the last six months. The fix: repoint the dashboard refresh to query only the necessary trailing window (directly addressing the single biggest cost driver first, rather than spreading effort evenly across many smaller issues), add a query-cost warning/approval threshold for ad-hoc queries above a defined data-scanned limit, and implement a retention policy moving data older than a year to cheaper storage. Tracking cost specifically for the dashboard refresh job before and after the fix confirms it alone accounts for most of the total reduction, validating that this was the right lever to pull first rather than assuming the aggregate bill decrease proves every change helped equally.

Trade-offs and pitfalls
A retention or tiered-storage policy that moves data to cheaper, slower storage without warning can silently break a report that occasionally needs older data, turning a cost optimization into an unexpected incident; any retention change needs a clear communication and grace period, and ideally a way for someone to explicitly flag data that needs to stay in fast storage longer than the default policy. Cost-reduction efforts can also chase the wrong thing if measured only by the aggregate bill: a genuine business growth in usage (more legitimate reports, more real analysts) will also increase cost, and conflating that with waste risks cutting things that are actually delivering value in the name of hitting a cost target, which is exactly why breaking cost down by driver, and measuring the SPECIFIC levers pulled, matters more than watching one aggregate number.

Direct answer

FIRST_VALUE, LAST_VALUE, and NTH_VALUE each return one specific value from the rows currently inside the window frame, not the whole partition. That distinction is exactly why LAST_VALUE(amount) OVER (PARTITION BY store_id ORDER BY sale_date) usually just returns the current row's own amount: with an ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so "the last row in the frame" is, by construction, the current row itself. The fix is to widen the frame to cover the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Structured elaboration

• FIRST_VALUE(expr) OVER (...) returns the value from the first row of the current frame.
• LAST_VALUE(expr) OVER (...) returns the value from the last row of the current frame.
• NTH_VALUE(expr, n) OVER (...) returns the value from the n-th row (1-indexed) of the current frame.
• Why FIRST_VALUE looks unaffected: the default frame always starts at UNBOUNDED PRECEDING, so "the first row of the frame" is stable no matter where the frame's end boundary sits; only the end boundary (CURRENT ROW by default) moves as you scan down the partition, which is why LAST_VALUE is the one that changes per row while FIRST_VALUE does not.
• Fix: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING makes the frame equal to the entire partition for every row, so LAST_VALUE (and NTH_VALUE) become stable, partition-wide values instead of per-row artifacts of where the default frame happened to end.
• NULLs inside the value column: the IGNORE NULLS clause (LAST_VALUE(amount) IGNORE NULLS OVER (...)) skips NULLs when locating the target row, and is supported in Snowflake, Oracle, SQL Server (2022 and later, and Azure SQL Database; earlier SQL Server versions do not have it), BigQuery, DuckDB, and Databricks, but PostgreSQL and MySQL do not support it as a window-function clause; on those engines, the portable fallback is a correlated subquery: (SELECT amount FROM sales s2 WHERE s2.id = s.id AND s2.amount IS NOT NULL ORDER BY s2.date DESC LIMIT 1).

Worked example

-- BUGGY: default frame, LAST_VALUE just echoes the current row
SELECT store_id, sale_date, amount,
  LAST_VALUE(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS last_value_buggy
FROM sales;

-- FIXED: widen the frame to the whole partition
SELECT store_id, sale_date, amount,
  LAST_VALUE(amount) OVER (PARTITION BY store_id ORDER BY sale_date
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value_fixed
FROM sales;

Executed against two stores (store 1: amounts 50, 70, 40 on three consecutive days; store 2: amounts 20, 90 on two consecutive days): the buggy version returns 50, 70, 40 for store 1 (each row echoing itself) and 20, 90 for store 2; the fixed version returns 40, 40, 40 for store 1 (the true last amount, repeated on every row) and 90, 90 for store 2. FIRST_VALUE(amount) OVER (PARTITION BY store_id ORDER BY sale_date) (no explicit frame needed) correctly returns 50, 50, 50 and 20, 20 without any modification, confirming it was never affected by the default-frame issue in the first place.

Trade-offs & pitfalls

• Widening the frame to the whole partition changes the query's cost profile: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW can often be evaluated incrementally in one pass over sorted data, while UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING requires the engine to have the entire partition available before it can emit any row for that partition, which raises memory pressure and spill risk on large partitions.
• The same root cause (an implicit frame that ends at CURRENT ROW) is what makes a running total silently change when the ORDER BY column has duplicate values; LAST_VALUE's default-frame bug and that running-total tie behavior are two symptoms of the same underlying rule, not two unrelated gotchas.
• FIRST_VALUE is not immune to every ordering mistake: if you order DESC to get "the highest" instead of "the chronologically first," the semantics of what FIRST_VALUE returns change completely, so be explicit and deliberate about ORDER BY direction, not just about the frame.

Check the most recent timestamp actually observed in the data against the current time, not whether the pipeline reported success, since a pipeline can run and complete cleanly while processing zero new rows if the upstream source stopped sending data.

Approach

```sql
SELECT MAX(occurred_at) AS latest_event,
EXTRACT(EPOCH FROM (now() - MAX(occurred_at))) / 60.0 AS minutes_since_latest,
(now() - MAX(occurred_at)) > INTERVAL '2 hours' AS is_stale
FROM events;
```

Worked example (verified by execution)

If the latest row in `events` is timestamped 3 hours ago, the query reports `is_stale = true` even if a scheduled ETL job that reads this table ran and exited with a success status ten minutes ago; the pipeline's own success signal says nothing about whether new DATA actually arrived, which is exactly the gap this check closes.

Trade-offs and pitfalls

A related but distinct signal is per-partition lateness: a row whose `event_date` is earlier than the partition it physically landed in indicates a late-arriving record, worth tracking as its own per-partition count rather than folding into the overall freshness check. For a more granular view than "is the whole table stale," measure event-time lag directly, the difference between when an event happened (`event_time`) and when it was actually ingested (`ingestion_time`), and report what percentage of events are arriving later than an acceptable bound per hour; a rising late-percentage trend often gives earlier warning of a developing pipeline problem than a binary staleness flag alone.