LDS Google-Style SQL 30

Before the Technical Phone Screen. 5 single-table-and-basic-join problems that gate entry to a 45–60 min shared-editor screen.

Google's phone screen assumes you can already write a clean 2-table JOIN, a GROUP BY aggregation, and a basic date filter. If any of these feel slow, fix that first — Google moves past these in the first 5 minutes.

• Most relevant for: every Google data role (universal floor)
• Skip if: you're senior — jump to Stage 2
• Domain: Google Ads / AdSense / AdMob (Google's primary data surface)

Google's first real loop round — 45–60 min on a shared editor with a senior IC. Typical report: 1 medium SQL + 1 medium Python in 60 min. For this SQL-only collection, this stage focuses on the SQL side.

Patterns tested:

• Multi-table JOINs with IN filters
• LEFT JOIN with NULL-aware filtering
• GROUP BY across multiple dimensions
• Conditional-rate aggregation (SUM(CASE WHEN ...))
• First window function (RANK partitioned)
• Correlated subquery (above-average filtering)

Train yourself to write each in 10–12 minutes and to explain your approach out loud as you write — Google scores fluency and articulation, not just correctness.

• Most relevant for: DSA, DSP, DE
• Also mirrors: Stripe technical screen, Snap phone interview

The dedicated 45-min SQL round inside Google's onsite. A peer IC walks you through one or two hard questions, then probes for 30+ minutes:

"Now partition this by device_type…" "What's the cost in BigQuery?" "How would you partition the underlying table?"

Patterns tested (the Google canon):

• Top-N within group — ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
• Running totals — SUM(...) OVER (... ROWS BETWEEN ...)
• Anti-joins — "creatives never used" / "campaigns with no clicks"
• Multi-table funnels — clicks → conversions chains
• Sessionization with 30-min idle — the Google-signature pattern, one Medium claim that "80% of Google DE candidates fail this on first attempt"
• Set operations — INTERSECT and EXCEPT shapes
• Anti-pattern subquery — clicks but no conversions (NOT EXISTS)

This is where Google separates L3/L4 from L5 candidates — window-function fluency and sessionization are the gate.

• Most relevant for: DSA, DSP, DE
• Also mirrors: YouTube data onsite, Apple analytics onsite

Google's distinctive 45-min round — the one no other FAANG still has as a standalone slot. Probability, statistics, hypothesis testing, A/B test math, and metric-design depth — all under time pressure.

Google is the original A/B-testing company (the search-results-page A/B-test framework is the canonical example used in every stats course). Every level (L3 → L6) gets tested on experimentation rigor: lift computation, sample-size adequacy, significance Z-scores, control-vs-treatment SQL reads. Plus cohort retention math — anchor users to first event, check return-in-window, compute retention curves.

Patterns tested:

• A/B test reads — variant comparison with significance Z-score
• Cohort retention — D7 / D14 / D28 acquisition cohort analysis (the Google-canonical metric)
• Distribution analysis — PERCENT_RANK for conversion-value buckets
• Rolling KPIs — 7-day moving CTR for trend detection
• Multi-signal risk — d4 fraud audit pattern (sparse-data probe)
• Date-distribution — avg days to conversion by month

If you skip this stage, you skip the round that decides Google data offers.

• Most relevant for: DSA, DSP, DS-Core
• Also mirrors: Stripe analytical reasoning, Microsoft DS stats round

The 45-min product-sense round. "No math, no one right answer" — but you'll still write SQL.

The interviewer hands you an under-specified business prompt:

"How would you measure ad-quality health on Search?" "What's the right metric for advertiser ROI?" "If a campaign's CTR drops 15% overnight, what query do you write first?"

You're being graded on judgment, scope, and metric design. The SQL is downstream — the harder skill is deciding what to compute.

The 6 problems are deliberately multi-CTE composite-score scorecards:

• Campaign KPI summary — CTR + CPC + CPM in one statement (metric definition discipline)
• Top advertisers leaderboard — ranking on spend, the Google Ads NORTH-STAR view
• Campaign health scorecard — multi-metric composite
• Funnel drop-off analysis — step-by-step conversion with the variant cut
• ROAS by attribution model — first-touch vs last-touch vs linear (d4 advanced)
• Cross-device attribution & fraud audit — multi-signal risk (d4 capstone)

Every explainer ends with a "How an L5/L6 interviewer would extend this" section — the BigQuery-cost and metric-ownership probes Google specifically asks.

• Most relevant for: L5+ candidates at Google (DSA, DSP, DE)
• Also mirrors: Meta Product Sense, Stripe analytical reasoning