Apple Senior Data Analyst Interview Preparation Guide (5-12 Years Experience)

Direct answer

Absolute churn rate (also called customer or logo churn) is the percentage of customers lost in a period; revenue churn rate is the percentage of recurring revenue lost in that period, usually measured against starting MRR (monthly recurring revenue). They diverge whenever the customers who leave are not representative of the average customer: losing a few high-paying accounts can shrink revenue churn even when the customer count looks fine, and vice versa. A team that tracks only customer churn can miss a revenue problem building up in its largest accounts.

Structured elaboration

Definitions

• Customer churn rate = customers lost in period / customers at start of period.
• Revenue churn rate = MRR lost in period (from cancellations and downgrades) / MRR at start of period.

Because customer churn counts every account equally, it is blind to price. Revenue churn weights each departure by its dollar size, so it reacts to who left, not just how many. The gap between the two metrics is itself a diagnostic: rising revenue churn alongside flat or falling customer churn usually points at concentration risk in high-value accounts, expansion not offsetting churn among large accounts, or a pricing and segmentation shift underway.

Worked example

Start of Month 1: 100 customers. 20 large accounts at $2,000/month = $40,000 MRR, and 80 small accounts at $750/month = $60,000 MRR. Total starting MRR = $100,000 (20 x $2,000 + 80 x $750 = $40,000 + $60,000).

Month 1: 8 small accounts cancel.
$\text{Customer churn}_1 = \frac{8}{100} = 8\%$
$\text{Revenue churn}_1 = \frac{8 \times \$750}{\$100{,}000} = \frac{\$6{,}000}{\$100{,}000} = 6\%$

Month 2 (same starting base: 100 customers, $100,000 MRR): only 4 large accounts cancel, no small-account churn.
$\text{Customer churn}_2 = \frac{4}{100} = 4\%$
$\text{Revenue churn}_2 = \frac{4 \times \$2{,}000}{\$100{,}000} = \frac{\$8{,}000}{\$100{,}000} = 8\%$

Result: customer churn fell (8% to 4%) while revenue churn rose (6% to 8%), because churn shifted from small, low-ARPU (average revenue per account) accounts to large, high-ARPU accounts.

Interpretation: fewer customers left overall, but the ones who left carried disproportionate revenue weight. This points at concentration risk in the largest accounts and possibly a pricing or segmentation issue, and argues for splitting churn dashboards by ARPU band or account tier rather than reporting one blended number.

Trade-offs & pitfalls

• Reporting only customer churn understates real financial exposure when the base has a long tail of small accounts and a few large ones.
• Reporting only revenue churn can look artificially healthy if a company over-serves a few large accounts while quietly losing many small customers, masking a broader satisfaction problem.
• A common pitfall is computing revenue churn against ending MRR instead of starting MRR, or mixing expansion revenue into the same ratio; keep churn and expansion as separate line items so this ratio measures loss only.
• Net revenue churn (which nets expansion/upsell against contraction and cancellations) is a different, usually more favorable, signal than gross revenue churn; naming the exact variant matters when comparing figures across teams.

Start by profiling the model to find hot spots: use Performance Analyzer in Power BI Desktop, DAX Studio / VertiPaq Analyzer to see table/column memory, and measure refresh and visual render times.

Practical steps (in order):

• Remove unused columns/tables: drop any fields not used in visuals, measures, or relationships. This directly reduces VertiPaq footprint.
• Reduce column cardinality: replace high-cardinality columns (GUIDs, long strings) with surrogate keys or bucketize (e.g., month-year, category groups). Where full detail isn’t needed, store truncated values or hashes only when necessary.
• Change data types: convert strings to integers, use Date vs DateTime when time not required, and set columns to the smallest numeric type supported. Avoid storing booleans as text.
• Replace calculated columns with measures: move row-level calculations into measures where possible (measures compute at query time and don’t consume model storage). Use variables in DAX to avoid repeated work.
• Introduce aggregation tables: create pre-aggregated fact tables (monthly, product-category) for common queries; configure Power BI aggregations so visuals hit these instead of the detailed fact.
• Consider DirectQuery or composite models when data is extremely large or frequently changing: DirectQuery offloads storage but increases query latency and depends on source performance. Use composite models to keep commonly queried aggregates in-import and detail in DirectQuery.

Validation:

• Track model .pbix size, VertiPaq memory by table/column before/after.
• Compare refresh duration, visual render times, and DAX query durations (DAX Studio).
• Use Performance Analyzer to ensure visuals now call aggregates/measures and fewer queries hit large tables.
• Run stakeholder scenarios (top 10 reports) to confirm correctness and end-user perceived responsiveness.

Trade-offs: importing reduces latency but uses memory; DirectQuery reduces footprint but needs robust source performance and may limit DAX. Document changes and keep a rollback plan.

Direct answer: With a 2% baseline conversion rate, 100k eligible users/day, 80% power, and alpha 0.05, and assuming a 15% relative minimum detectable effect (the question does not state one explicitly, so I am stating this assumption up front, a common convention for this kind of sizing exercise; new baseline = 2.30%), the design needs roughly 18,346 users PER CELL to power the main effects, or about 36,693 users per cell to ALSO power the interaction at the same MDE. Traffic should be split evenly, 25% into each of the 4 cells (control/control, pricing-B/layout-old, pricing-A/layout-new, pricing-B/layout-new), randomized independently on pricing and layout so every user has an equal chance of any of the 4 combinations.

Sample-size computation (executed, two-proportion z-test):

For a MAIN EFFECT comparison (pooling the 2 cells that share a pricing level against the other 2), $p_1=0.02$, $p_2=0.023$ (15% relative lift):

gives n = 36,693 per POOLED arm (i.e. per 2-cell group). Since each pooled arm is 2 of the 4 cells, that is 18,346 users per CELL for main-effect power, 73,386 total across all 4 cells, which at 100k/day takes about 0.73 days.

For the INTERACTION contrast (an unpooled, single-cell-vs-single-cell comparison, since the interaction estimate uses the +1/-1/-1/+1 contrast across all four individual cells rather than pooled halves), the SAME formula applied per cell (no pooling) requires 36,693 users per cell, 146,771 total, about 1.47 days at 100k/day, exactly 2.00x the main-effect total. This is the general result cited in S0: interaction detection at the same MDE costs about 2x a main effect's sample size.

Traffic allocation: randomize pricing (A vs B) and layout (old vs new) INDEPENDENTLY via two separate hash-based coin flips per user (verified orthogonal in S9), producing four roughly-equal 25% cells automatically; do not manually stratify into 4 named buckets, since independent-factor randomization already balances the design and keeps it robust to later adding a third factor.

Analysis and interpretation: fit

on the binary conversion outcome (linear probability model or logistic regression with an interaction term). $\beta_1$ is the pricing main effect averaged over both layouts, $\beta_2$ the layout main effect averaged over both pricing versions, and $\beta_3$ the interaction: how much the pricing effect CHANGES when layout changes (equivalently, how much the layout effect changes when pricing changes). Run the interaction test FIRST: if $\beta_3$ is significant, report cell-specific conversion rates (all 4 cells) rather than a single "average" pricing effect, since the average would be misleading if the effect flips sign across layouts. If the interaction is not significant, report the two main effects as if from independent tests and recommend shipping whichever combination of winning main effects performs best. Present results to stakeholders as: (1) the interaction test verdict first, in plain language, (2) a 2x2 table of observed conversion rates per cell with confidence intervals, (3) the recommended cell to ship, and (4) the guardrail-metric check on that winning cell (per this topic's sibling a-b-test-design-and-statistical-rigor, which owns guardrail-metric mechanics).

Parentheses change what OR groups with, and SQL's default precedence (AND binds tighter than OR) can silently produce the wrong result if you assume left-to-right reading.

Structured elaboration

Without parentheses, WHERE category = 'shirts' AND price < 20 OR on_sale = true is evaluated as (category = 'shirts' AND price < 20) OR (on_sale = true): any row that is on sale qualifies, category and price notwithstanding. Adding parentheses around the OR, category = 'shirts' AND (price < 20 OR on_sale = true), restricts the OR branch to shirts only. Neither form is "wrong" in isolation; the bug is writing one when you meant the other. NOT is the third operator the question names, and its precedence is the highest of the three: SQL evaluates NOT before AND, and AND before OR. So NOT category = 'shirts' AND price < 20 parses as (NOT category = 'shirts') AND (price < 20), not as NOT (category = 'shirts' AND price < 20); the NOT applies only to the single comparison immediately next to it, not to the whole AND expression, unless parentheses say otherwise.

Worked example

Using products(product_id, category, price, on_sale) with rows (1, shirts, 15, not on sale), (2, shirts, 25, on sale), (3, pants, 10, on sale), (4, shirts, 30, not on sale):

• Without parentheses: rows 1, 2, 3 match. Row 3 (pants) sneaks in purely because it's on sale.
• With parentheses: rows 1, 2 match. Row 3 is correctly excluded because it isn't a shirt.
• Adding NOT: WHERE NOT category = 'shirts' AND price < 20 matches only row 3 (pants, price 10). The NOT applies to category = 'shirts' alone (true for rows 1, 2, 4, so NOT makes it false for them), then ANDs that with price < 20, leaving only the one row that is both not-a-shirt and under $20. Wrapping the whole expression instead, WHERE NOT (category = 'shirts' AND price < 20), flips which rows are excluded: it matches rows 2, 3, and 4, everything except row 1 (the only row where category = 'shirts' AND price < 20 was true to begin with).

Trade-offs and pitfalls

The fix costs nothing and the failure mode is silent (no error, just wrong rows), which is what makes it dangerous in report queries nobody double-checks. As a habit: whenever AND and OR appear in the same WHERE clause, add explicit parentheses even where they aren't strictly required, purely for the next reader. The same silent-wrong-result risk applies to NOT: many people mentally read NOT a AND b as NOT (a AND b), but SQL does not; when NOT needs to negate a whole compound condition rather than just the term next to it, parenthesize it explicitly.

Direct answer

CUPED (Controlled-experiment Using Pre-Experiment Data) is a variance-reduction technique, published by Microsoft researchers (Deng, Xu, Kohavi and Walker, 2013), that uses a metric measured before the experiment started to strip out some of the between-user noise in the outcome metric, without touching the unbiasedness of the treatment-effect estimate. It requires one thing: a pre-experiment value of a covariate, ideally the same metric you are about to test, measured for the same units, from a period before assignment happened (so it cannot itself have been affected by treatment). The payoff is a tighter estimate of the same treatment effect from the same traffic, which functionally means you can detect the same effect size with less traffic or in less time.

Structured elaboration

What it requires and assumes

• A pre-experiment covariate $X$ for every unit, measured strictly before random assignment (so treatment cannot have influenced it) and ideally correlated with the outcome $Y$ you're testing.
• The scaling coefficient must be estimated without using the treatment label, typically pooled across both arms or from control alone. This is what keeps the adjustment from injecting bias: you are not letting "knowledge of which arm someone is in" leak into how you weight their pre-period value.

How the adjustment is computed, in plain terms

For each unit, adjust its outcome by subtracting a scaled version of how far its pre-experiment covariate was from the population average:

$Y_i^{\text{CUPED}} = Y_i - \theta \left(X_i - \bar{X}\right)$

where the scaling coefficient is the population covariance of $X$ and $Y$ divided by the variance of $X$:

$\theta = \frac{\operatorname{Cov}(X, Y)}{\operatorname{Var}(X)}$

Intuitively: if a user's pre-period activity was above average, some of their post-period activity being above average too is "expected" and not informative about treatment, so it gets subtracted out. The treatment effect is then estimated as the difference in adjusted outcome means between arms, $\bar{Y}^{\text{CUPED}}_{\text{treat}} - \bar{Y}^{\text{CUPED}}_{\text{control}}$, which is algebraically the same as the raw mean difference minus $\theta$ times the pre-period mean difference between arms; since randomization makes that pre-period difference expected to be zero, the adjustment does not shift the expected value of the estimate, it only removes the part of each unit's variance that $X$ already explained.

The resulting variance reduction has a clean form: if $\rho$ is the correlation between the pre-experiment covariate and the outcome, the adjusted estimator's variance is $(1-\rho^2)$ times the unadjusted variance. A stronger pre-period predictor buys a bigger reduction; a weak one buys almost nothing.

What makes a covariate good or poor

What goes wrong with a poor choice

• Using a post-treatment or treatment-adjacent value as $X$ breaks the "cannot be affected by treatment" requirement and reintroduces bias: you would be conditioning on something treatment itself moved, which is a different and more serious problem than losing precision.
• Using a weakly correlated covariate costs almost nothing in bias but also buys almost nothing in variance reduction; the $(1-\rho^2)$ factor is close to 1.
• Using a covariate that is missing for a large share of units (commonly new users with no pre-period history) forces a choice: drop those units (losing sample and possibly biasing toward existing users) or impute a value (introducing a modeling assumption you did not need before).

Worked example

Suppose, for illustration, we assume a pre-experiment 7-day conversion rate correlates with the in-experiment conversion rate at $\rho = 0.6$ for a given product (a hypothetical, stated input, not an empirical claim about any real product). The variance reduction from CUPED is then derived directly:

$\frac{\operatorname{Var}(Y^{\text{CUPED}})}{\operatorname{Var}(Y)} = 1 - \rho^2 = 1 - 0.6^2 = 1 - 0.36 = 0.64$

So the adjusted estimator's variance is 64% of the unadjusted one, a 36% reduction, given only the stated assumption $\rho = 0.6$. If $\rho$ had instead been a weak 0.2, the reduction would be $1 - 0.04 = 0.96$, i.e., only a 4% cut, illustrating why the strength of the pre-period correlation, not just its existence, determines whether CUPED is worth implementing for a given metric.

Trade-offs and pitfalls

• CUPED does not help metrics with no meaningful pre-experiment history, most obviously new-user-only experiments; for those, look at stratification or other covariate-adjustment techniques instead.
• Estimating $\theta$ using data that has already been split by arm (rather than pooled or control-only) risks contaminating the coefficient with treatment information; keep the estimation step blind to arm assignment.
• CUPED reduces variance, it does not change what the true effect is; do not present the tighter confidence interval as evidence the effect itself grew, only that you can now see it more precisely.
• CUPED composes with stratified randomization (apply the adjustment within strata), which is a reason to know both rather than treat them as competing choices.

Direct answer

A persuasive story that fails to drive adoption usually didn't fail in the room, it failed after the room, at the "so what do I do on Monday morning" step. Fix that gap with stakeholder interviews, metrics reframed to the team's own incentives, workflow-level training materials, and recommended actions tied to OKRs (objectives and key results, the team's own goal-setting framework) they already own.

Framework: a four-part revision approach

1. Stakeholder interviews. Talk to the customer-success reps doing the actual work, not just their managers. Ask what they're measured on day to day, what tools they already use, and specifically what part of the original pitch didn't stick or didn't apply to their routine.

2. Reframe metrics to team incentives. Translate the initiative's headline metric into whatever metric customer-success is actually evaluated on, and make the causal link explicit: this action reduces the thing your OKR is scored on, not just the thing the initiative cares about.

3. Training materials. Replace a slide recap of the original pitch with task-level job aids, a one-page workflow card or a short walkthrough of the exact new step inside the tool reps already use. The original narrative answered why; it never answered how, today, in my exact tool.

4. Align recommended actions to OKRs. Restate each recommended action as a line under an OKR the team already owns, instead of introducing a brand-new goal for them to track alongside everything else.

Worked example

Initiative: a new account health-score dashboard meant to help customer-success proactively call at-risk accounts before they churn. Original pitch led with model lift, "our model predicts churn risk with strong lift," and adoption stayed low. Interviews reveal two things: reps ignored the dashboard because logging into a separate tool was one more app, and their actual OKR is measured on ticket resolution time and CSAT (customer satisfaction score), not churn prevention directly. The revision: rename the metric from "model lift" to "accounts flagged this week that are worth a proactive call," tied directly to their existing renewal-focused OKR; replace the pitch deck with a one-page "if you see a red flag, do X, log Y" card embedded inside their existing CRM, not a new login; and restate the action as a line item under the OKR they already report on, "reduce churn in your book of business by acting on N flagged accounts per week."

Trade-offs and pitfalls

• The most common wrong turn is repeating the same persuasive story, louder, since the story already worked in the room, and the adoption problem lives downstream of the room.
• Running stakeholder interviews and then not visibly acting on what was heard burns trust for the next initiative that comes along.
• Tying an action to an OKR without genuinely reducing the reps' workload adds a mandate, not motivation, and adoption stays low for a new reason.

Direct answer

An implausibly-inflated aggregate from a multi-table join is the classic signature of join fan-out: joining a one-to-many (or many-to-many) relationship before aggregating multiplies each contributing row by however many matching rows exist on the other side of the join, so SUM() sums each value once per duplicate match rather than once per logical entity.

Structured elaboration

The debugging process: first check cardinality assumptions with simple intermediate row counts at each join step (how many rows does each table have, and how many rows does the join produce, is the joined row count larger than expected relative to the input sizes), which immediately localizes whether fan-out is happening and at which join. Then identify which specific join has a one-to-many relationship that was not accounted for before the aggregation. The fix is to pre-aggregate the "many" side down to one row per key before joining it to anything else, rather than aggregating after all the joins have already multiplied the rows.

Worked example

Given users, payments (2 payments for user 1, totaling $150), and sessions (3 sessions for user 1), a query joining all three before aggregating: SELECT u.user_id, SUM(p.amount) / COUNT(DISTINCT u.user_id) FROM users u JOIN payments p ON u.user_id=p.user_id JOIN sessions s ON u.user_id=s.user_id GROUP BY u.user_id returns $450 for user 1 on a DuckDB fixture (verified by execution), exactly 3x the true $150 total, because the join to sessions fans each of the 2 payment rows out across the 3 matching session rows, producing 6 joined rows, and SUM(p.amount) sums $100 and $50 three times each. Pre-aggregating payments per user first, into one summary row per user before it ever reaches a join, correctly returns $150. Note the corrected query below intentionally does not join sessions at all: sessions was never needed to compute total_paid, it only entered the original query because the question joined all three tables together, and it was precisely that unnecessary join that caused the fan-out in the first place. Once payments is pre-aggregated to one row per user_id, joining it to users alone is sufficient and correct; re-adding a join to sessions here would serve no purpose for this metric and, unless sessions were also first collapsed to one row per user, would reintroduce the exact same fan-out bug this fix is meant to eliminate:

WITH user_payments AS (SELECT user_id, SUM(amount) AS total_paid FROM payments GROUP BY user_id)
SELECT u.user_id, up.total_paid FROM users u JOIN user_payments up ON u.user_id = up.user_id;

Trade-offs and pitfalls

The regression check that would have caught this before it shipped: a simple sanity assertion comparing SUM(amount) computed directly from the payments table alone against the same sum computed through the full joined query, for a small, known test case, would immediately reveal a 3x discrepancy and fail loudly, rather than shipping a silently-wrong query to production. More broadly, any aggregate query that joins more than one "many" side of a relationship before its GROUP BY should be treated as a fan-out risk by default and reviewed for whether each many-side table needs pre-aggregating first, rather than trusting that the final GROUP BY will somehow correct for it, since GROUP BY only groups rows, it does not undo a multiplication that already happened during the join.

Direct answer: Model each metric once in dbt (or an equivalent metrics layer) as a versioned, tested SQL artifact built on top of incrementally-materialized staging models, with an explicit strategy for late-arriving data (a rolling reprocessing window) and backfills (parameterized full-refresh runs scoped to specific date ranges), so the SAME definition serves near-real-time and audited historical reporting from one source.

Structured elaboration:

• Model design: a layered structure: raw/staging models (lightly cleaned, deduplicated raw events) feed intermediate models (sessionized, identity-resolved) which feed final metric models (the actual named business metrics); each layer is independently testable, and a metric model never reads directly from raw events, keeping the "expensive cleaning" and "business logic" concerns separated.
• Incremental strategy: metric models use an INCREMENTAL materialization that only reprocesses a trailing window (e.g., the last 3 days, wide enough to cover typical late-arriving data) on each run, rather than a full rebuild, while periodically running a full-refresh reconciliation to catch anything the trailing window missed.
• Late-arriving data: the incremental window's width should be set based on measured, observed data on how late data typically arrives (not a guess), and any data arriving LATER than that window is caught by the periodic full-refresh reconciliation, with a documented, monitored lag metric so the team can see if the window's width assumption starts to look wrong.
• Backfills: a parameterized run (e.g., dbt run --vars '{"start_date": "...", "end_date": "..."}') targeting a specific historical range, using the SAME model logic as the regular incremental run, so a backfill is guaranteed to produce results consistent with what the incremental pipeline would have produced, rather than a separate, divergent backfill-specific code path.
• Testing: schema tests (not-null, uniqueness) on staging models, business-logic tests (the golden-fixture pattern from S18) on final metric models, and a freshness test asserting the incremental window is actually being refreshed on schedule.
• Deployment: metric model changes go through the same code-review + CI-test gate as any other code change (S58's registry-enforcement pattern), with semantic_version bumped and documented on any logic change.
• Auditability/lineage: dbt's own lineage graph (which models feed which) doubles as the lineage documentation this topic's registry sub-area calls for, generated automatically from the model dependency graph rather than maintained by hand.

Worked example: migrating a company's ad hoc Tableau-embedded SQL metrics into this dbt-based layer starts by picking the highest-value, most-disputed metric first (often "active users," per the recurring reconciliation theme throughout this topic), building it as a tested, versioned dbt model, and redirecting Tableau to read from that model's output table instead of its own embedded SQL, one metric at a time rather than attempting a single big-bang migration of all metrics simultaneously.

Trade-offs & pitfalls: An incremental window set too narrow silently misses genuinely late data (undercounting until the next full-refresh catches up, which can be days); set too wide, it defeats the purpose of incremental processing by reprocessing nearly as much as a full rebuild would. Tune the window from OBSERVED data-arrival-lag statistics, and monitor it as its own metric so the assumption doesn't silently go stale as upstream systems change.

Direct answer
Most large employers that codify their interview values name broadly similar underlying traits, even when their specific vocabulary differs: a customer or user-first orientation, taking ownership beyond a narrow scope, moving with appropriate urgency, holding a high quality bar, and being trustworthy and transparent recur across nearly every published framework, just under different labels.

Structured elaboration

Worked example
The table above is itself the worked example. A strong candidate should be able to reproduce a table like this from memory for whichever specific company's list they are asked about, translating each of that company's named principles onto one of these five underlying traits, rather than treating an unfamiliar company's vocabulary as an entirely new set of ideas to learn from scratch.

Trade-offs and pitfalls
Treating every company's list as identical is itself a mistake; the values differ in emphasis, and in what is explicitly left off the list. A company whose published list omits any explicit ownership language may culturally deprioritize individual initiative in favor of process, for example, and that is worth noticing rather than flattening away. A candidate who can only speak the vocabulary of one company, fluent in one set of terms but unable to translate the same underlying trait into a different company's language, reads as having memorized rather than internalized the competencies involved.

Direct answer. Selectivity is the fraction of a table's rows a predicate matches; cardinality is the actual number of rows that come out of an operation. A highly selective predicate (one matching few rows) is exactly the situation where an index earns its cost, and predicate selectivity is also the main input the optimizer uses to decide join order, since it wants to apply the most narrowing filters as early as possible.

Structured elaboration. If a predicate's selectivity is 1% (it matches roughly 1 in 100 rows), an index lookup that jumps straight to those rows beats scanning the whole table, since the fixed cost of the index lookup is repaid many times over by the rows it lets you skip; a predicate matching 50% of rows offers much less benefit from an index, since you'd be visiting half the table's rows either way, just via a more expensive access pattern. For join order, the optimizer generally wants to apply the predicate (or combination of predicates) that will shrink the working set the most, as early in the join sequence as possible, since every table joined afterward then only has to be matched against a smaller intermediate result rather than the full one.

To estimate a predicate's selectivity from existing statistics without running the full query, you'd typically look at the number of distinct values for that column (assuming a roughly uniform distribution, selectivity for an equality predicate is close to 1 divided by the distinct value count) or, where available, a stored histogram that captures the actual distribution more precisely for skewed columns.

Worked example. For a users table with a country column where 90% of rows are 'US' and the remaining 10% are spread across 40 other countries, filtering country = 'US' has low selectivity (matches ~90% of rows, an index doesn't help much, a scan is likely the right choice), while filtering country = 'Luxembourg' has high selectivity (matches a tiny fraction, an index on country earns its cost here even though it's the SAME column and the SAME index).

Trade-offs and pitfalls. Selectivity isn't a fixed property of a column, it's a property of a specific predicate VALUE against that column's distribution; assuming one index is uniformly "good" or "useless" for a column without accounting for which specific values are queried most is a common estimation mistake, and it's exactly why skewed columns need better-than-average statistics (histograms or multi-value frequency counts) to plan well.