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)

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

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