Practice Interview Questions

Edge → one specific project → bridge to this role anchored to a JD line → honest gap with closing plan. See 02-positioning for the through-line shape.

Anchor to one specific JD line and one specific thing in your trajectory. "The 'first analytics hire' framing — I've been the second or third hire and I want to be the one defining the metric vocabulary, not inheriting it. GPU marketplace is interesting because two-sided marketplaces are the analytical pattern I've gotten most fluent on, applied to a domain that's genuinely new to me."

STAR. Situation → task → action (lead with the framing decision and the alternative you discarded) → result (measurable, decision changed). The alternative-you-considered beat is what reads senior.

Have a real one. Not a near-miss. What you concluded → what you missed → how you found out → what you did → what changed in your process going forward. The last beat is the differentiator.

ROW_NUMBER: unique 1..N per partition, ties broken arbitrarily. RANK: ties share rank, next skips (1,2,2,4). DENSE_RANK: ties share rank, no skip (1,2,2,3). Use ROW_NUMBER for "exactly one row per group"; use RANK/DENSE_RANK when ties are real and you want them surfaced.

If the subquery returns any NULL, every NOT IN comparison evaluates to UNKNOWN, which filters everything. Default to NOT EXISTS for correlated lookups.

Daily distinct-user count as the base, then AVG() over ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. ROWS counts rows, so fill missing days from a date spine first, or use a RANGE frame with an interval. COUNT(DISTINCT) in a window frame isn't supported in most dialects — workarounds include self-join with date windows, or HyperLogLog approximate counts in BigQuery/Redshift.

CTEs for anything beyond two steps — they read top-to-bottom like prose and the CTE names document your thinking. Subqueries (especially correlated) when the logic is genuinely a filter and not a logical step. Caveat: in some warehouses, CTEs are re-evaluated on each reference; for expensive reused CTEs, materialize to a temp table.

The default window frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. LAST_VALUE over that frame returns the current row. Fix with explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or refactor to a ranked subquery.

Six checks. (1) Is the data complete? Sometimes the previous month is partial. (2) Did the definition change? A small SQL edit can shift a number meaningfully. (3) Did the denominator change? A growing user base inflates totals. (4) Is the data right? Run row counts at each CTE stage. (5) Slice by cohort/segment — is the growth uniform or one segment? (6) Compare against a second source if available. Report what you find honestly, including if the 12% holds up.

Trace both definitions: what's the date window, what counts as "active" (any event vs gpu-session vs minimum threshold), how are bots/internal accounts handled. Find the divergence. Pick the right one. Kill the other. Communicate proactively — stakeholders likely noticed. Long-term: a metric layer (LookML, dbt semantic models) so this can't drift again.

Three habits. (1) Row-count sanity checks at every CTE — if a CTE has more rows than its input, that's a fanout. (2) Comparison against an alternative source — does the SQL match what the warehouse aggregator reports, or what an engineer would estimate? (3) Trend continuity — sudden jumps without product change are usually data, not reality. Build alerts on these into the dashboard layer.

Three layers. (1) Pipeline-level: dbt tests on freshness, uniqueness, not-null, accepted-values, row-count thresholds. (2) Metric-level: anomaly detection on the headline numbers (z-score, MAD, or seasonal decomposition). (3) Source-level: reconciliation against upstream (does our session count match the application logs from engineering?). Senior signal: naming all three layers and pointing out the third is the one most teams skip.

SQL for anything the warehouse can do well — joins, groupbys, window functions over large data. Pandas for non-SQL operations: text manipulation, complex feature engineering, time-series cleaning with row-level logic, ad-hoc analysis on intermediate results. The line: if you'd ship it, push to SQL; if it's exploratory, pandas. Mention DuckDB as a third option for SQL-on-DataFrames when pandas is awkward but SQL fits the shape.

.apply with a Python function is row-by-row and pays Python overhead per row — 10–100× slower than vectorized ops. OK when the function is genuinely complex (multi-line, branching, external lookups) and the DataFrame is small enough that clarity matters more than speed. Default to vectorization; use .apply with an explicit comment about why.

(1) Build cohort_week column from signup. (2) Join cohorts to activity events; compute weeks_since per activity row. (3) Groupby (cohort_week, weeks_since) → unique customer count. (4) Divide by cohort size. (5) Pivot to a wide retention table (rows: cohorts, columns: weeks_since, values: rate). Mention the SQL alternative is much faster at scale and the result should be identical.

Two options. (1) Aggregate the right side to one row per key before merging — the safer default. (2) Accept the fanout intentionally — but then check the resulting row count and verify it matches expectations. Always pass validate='m:1' or '1:1' when you expect uniqueness — pandas raises if it isn't, catches bugs early.

Start with the audience and the decision they're making. If a chart doesn't change a decision, it doesn't belong. Cap at ~7 charts per dashboard; otherwise readers scan and miss everything. Pair every metric with comparison context (WoW, MoM, YoY) — a bare number is noise.

Hex: notebooks + dashboards in one — analyst-friendly, fast iteration. Metabase: open-source BI, good enough for early-stage. PostHog: product analytics specifically — funnels, retention, session replay, event tracking. At this stage I'd guess Hex or Metabase for general analytics, PostHog for product/usage funnels. Each has a niche; using all three for what they're each good at is reasonable.

Three sections. (1) Headline metrics: GPU-hours billed, revenue, take rate — current period vs prior, with trend chart. (2) Marketplace health: utilization by tier, fulfillment time, supplier and customer counts. (3) Cohorts and unit economics: retention curves, CAC/LTV, gross margin per GPU-hour. End with "open questions for the board" — analytical uncertainties leadership should weigh in on. Avoid vanity metrics like cumulative signups all-time.

H100s are scarcer, more expensive, demanded by training workloads (large models). A100s are workhorses — broader use, more elastic supply. Analytical implications: H100 utilization should run hotter; H100 supplier churn is more material; price elasticity differs sharply by tier. Treating them identically in the metric system misses where the levers are.

No single one. The closest single number is utilization-by-tier weighted by tier revenue. But the senior answer pairs it with fulfillment time (leading indicator of liquidity) and cohort retention on both sides (long-term health). One number masks the trade between supply and demand growth.

Inference servers process multiple concurrent requests in one GPU forward pass — much better GPU utilization. Batch size of 16 vs 1 can yield 5–10× higher throughput, lower per-request cost. Implication: cost per request drops sharply with concurrent demand. Two customers running at low concurrency are far more expensive than one running at high. Pricing should reflect that — usage-based pricing with volume discounts isn't a sales trick, it's the unit economics.

Hypotheses, in order of likelihood. (1) Tier mix shifted toward cheaper GPUs (more A10s, fewer H100s) — check tier composition. (2) Pricing changed — discounts or promotional pricing. (3) Currency / billing-cycle issues — revenue recognition lagging hours billed. (4) Data bug — hours billed is overcounting (duplicates, leaked test traffic). Investigate in that order; report what you find with a concrete recommendation.

Don't list infrastructure. Lead with the question: "I'd spend week one in customer + supplier conversations to learn what leadership doesn't trust about the data today. Then I'd build the three or four metrics that map to current decisions, with definitions documented and source-of-truth captured in a dbt-style layer. Then start retiring whatever dashboards disagree with those metrics." Senior signal: starting with the question, not the infrastructure.