The Google Analytical Interview — How They Actually Test SQL

Google's analytical interview is the most misunderstood loop in big tech. People prepare for it like a SQL exam, then walk into the room and discover it's actually an ambiguity exam — and SQL is just the medium. Candidates who treat the prompt as a riddle to solve fast end up failing it. The ones who treat it as a conversation about how they'd structure an investigation tend to pass.

This is a guide to what Google actually tests, the structure that earns score, and a worked example you can use as a template.

What Google Actually Tests

The Google analytical interview is one round in the data scientist and analyst loops. It runs about 45 minutes. The interviewer poses an open-ended business question, often deliberately under-specified, and watches how you turn it into a structured answer.

Three things get scored:

1. Problem framing. Did you ask the right clarifying questions? Did you decompose the question into something tractable?
2. Query design. When you wrote SQL, was it clean, correct, and the right tool for the question?
3. Interpretation. When you saw the results, did you tell a story about what they meant for the product?

SQL execution is roughly a third of the score, not the whole thing. That's the first mindset shift.

The Shape Of The Question

Google's analytical prompts almost always follow a pattern:

"YouTube's product team is concerned that engagement is dropping among teen users in the US over the last month. How would you investigate? Walk me through your analysis."

Notice what isn't in that prompt: no schema, no exact metric definition, no specific tables. The interviewer wants to see how you handle that.

The framework that works is the DCRI framework — Define, Compare, Root-cause, Interpret. Four sentences before you write a line of SQL.

Define

Restate the business question in measurable terms. "You said engagement is dropping. To investigate, I'd want to operationalize 'engagement' first. The cleanest options are: daily active users, watch time per user, or sessions per user. I'd default to watch time per teen US user, but I'd want to confirm with the PM."

This single move signals senior judgment. Two-thirds of candidates skip it and dive into SQL.

Compare

Specify the comparison. "I'd compare the last 30 days to the same 30 days a year ago, and also to the rolling 30-day average over the past 6 months. Year-over-year filters out seasonality; rolling baseline catches recent drift."

You don't need to write the SQL yet. You're declaring intent.

Root-cause

Sketch how you'd decompose the metric to find the cause. "If engagement is down, I'd segment by device (mobile vs. TV), by content category, and by acquisition cohort. Whichever segment shows the biggest delta is where I'd dig deeper."

Interpret

Tell them what you'd report. "I'd come back with a one-page diagnosis: the magnitude of the drop, the segment that explains most of it, and a recommendation for what to test next."

Then — and only then — write the SQL.

The Worked Example

Same prompt, same framework. Let's run it.

Define. Operationalize engagement as daily watch minutes per active US teen user (age 13-17). Active = had at least one session in the day.

Compare. Window the analysis to the last 30 days. Use Day −30 to Day 0 as the current window; Day −395 to Day −365 as the year-ago comparison (filters seasonality).

Root-cause sketch. Slice by device platform first, since device is usually the largest source of variance in teen engagement.

SQL — current vs. year-ago, by device:

WITH bounds AS (
  SELECT
    DATE_TRUNC('day', NOW() - INTERVAL '30 days')  AS current_start,
    DATE_TRUNC('day', NOW())                       AS current_end,
    DATE_TRUNC('day', NOW() - INTERVAL '395 days') AS prior_start,
    DATE_TRUNC('day', NOW() - INTERVAL '365 days') AS prior_end
),
us_teens AS (
  SELECT id AS user_id
  FROM users
  WHERE country = 'US'
    AND age BETWEEN 13 AND 17
),
windowed AS (
  SELECT
    s.user_id,
    s.device_platform,
    s.session_date,
    s.watch_minutes,
    CASE
      WHEN s.session_date >= (SELECT current_start FROM bounds)
       AND s.session_date <  (SELECT current_end   FROM bounds) THEN 'current'
      WHEN s.session_date >= (SELECT prior_start   FROM bounds)
       AND s.session_date <  (SELECT prior_end     FROM bounds) THEN 'prior'
    END AS window_label
  FROM sessions s
  JOIN us_teens u USING (user_id)
)
SELECT
  device_platform,
  window_label,
  COUNT(DISTINCT user_id)                                     AS active_users,
  ROUND(SUM(watch_minutes)::numeric / NULLIF(COUNT(DISTINCT user_id), 0), 1)
                                                              AS watch_min_per_user
FROM windowed
WHERE window_label IS NOT NULL
GROUP BY device_platform, window_label
ORDER BY device_platform, window_label;

The query isn't doing anything exotic — it's a windowed aggregation with conditional bucketing. The skill is in what you call out about it:

• The NULLIF guards division by zero if a device had no active users in a window.
• The DATE_TRUNC ensures comparable day boundaries.
• The BETWEEN on age uses inclusive bounds — explicitly noted, because teen is sometimes defined as 13-19 elsewhere.

Interpret. Now you read the result. "If mobile watch-min-per-user is flat year-over-year but TV is down 18%, the drop is concentrated in TV. My next query would slice TV further — by content category, by app version, by region."

The Mistakes That Cost Offers

Diving in cold. Candidates who start writing SQL in the first 90 seconds almost never get the offer. The framing step isn't filler — it's the test.

Optimizing the SQL too early. This isn't a query plan interview. Don't reach for indexes, materialized views, or query hints. Solve correctness first.

Ignoring sample size. Whatever your query returns, ask out loud: "Are these numbers stable, or am I looking at noise?" Especially if you've segmented down to small cohorts. This is the move that earns senior credit.

Treating the interview as a monologue. The interviewer wants to see you adjust. Pause. Check in. "Does that approach make sense, or would you like me to start with a different angle?" Candidates who never check in get marked down on collaboration.

The Larger Pattern

Google's analytical interview rewards judgment, not speed. You can write slow SQL and still get the offer if your framing is sharp and your interpretation is tight. You can write fast SQL and still bomb if you can't explain why you wrote it that way.

The candidates who do well here are the ones who've practiced narrating their analytical work, not just writing queries in silence. That's the muscle worth building.