Google·Data Scientist·Technical Phone Screen·Senior

Senior

May 2026Remote

Summary

Google DS technical screen, basically a two-part SQL and pandas gauntlet. The problem was dense and I spent way too long second-guessing the timezone conversion logic before even touching the SRM calculation. Not a vibe-check round at all.

Questions Asked (2)

Write a single SQL query to compute daily metrics for a specific local date derived from UTC timestamps, including new buyer counts, cart-to-paid conversion rates for new vs returning buyers, and a sample-ratio-mismatch p-value for an experiment variant split.

A/B Testing & ExperimentationProduct Analytics & MetricsData Modeling

Author's notes

This was the bulk of the session.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and assumptions (e.g., timestamp columns, buyer type definition, experiment assignment table). Then outline a single query using CTEs to filter by local date, aggregate daily metrics, and compute the p-value for sample ratio mismatch. Emphasize correctness of timezone conversion and statistical validity.

Pro tip: Mention that sample ratio mismatch should be checked before analyzing experiment metrics, and use a two-sided binomial test or chi-square test with expected 50/50 split. Also, ensure the local date conversion uses the correct timezone offset (e.g., America/Los_Angeles) and handles daylight saving time if applicable.

1. Clarify schema and assumptions

Ask about table structures, column names, and definitions (e.g., what constitutes a 'new buyer', how experiment variant is assigned). Confirm the local timezone and date range.

2. Filter and convert timestamps to local date

Use a WHERE clause to restrict to the target local date by converting UTC timestamps to the local timezone (e.g., using AT TIME ZONE or equivalent).

3. Compute daily metrics with conditional aggregation

Calculate new buyer counts, and cart-to-paid conversion rates for new vs returning buyers using CASE statements and aggregate functions.

4. Calculate sample ratio mismatch p-value

Count users per variant, compute the expected split (e.g., 50/50), and use a binomial test or chi-square test to derive the p-value.

5. Combine results into a single query

Use CTEs to organize the logic and join or union the metrics into one output row per day, ensuring all calculations are in a single SQL statement.

Key Points to Mention

Timezone conversion: use AT TIME ZONE or equivalent to derive local date from UTC timestamps, considering DST.
Definition of new vs returning buyers: typically based on first purchase date or a flag in the data.
Cart-to-paid conversion rate: number of paid orders divided by number of carts, segmented by buyer type.
Sample ratio mismatch: compare observed variant counts to expected using a statistical test (e.g., binomial or chi-square).
Use of CTEs for readability and to avoid repeating subqueries.
Handling of edge cases: nulls, multiple events per user, and ensuring correct aggregation level.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Given an events DataFrame with a JSON properties column, write idiomatic pandas code (no UDFs) to deduplicate add-to-cart events within a session and SKU by collapsing events within 2 minutes of each other, then count distinct deduplicated events per user, local date, and SKU.

Product Analytics & MetricsAlgorithms & Data StructuresTechnical Trade-offs

Author's notes

The 'no UDFs' constraint is the real constraint here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, parse the JSON properties column to extract event type, SKU, and timestamp, then filter for add-to-cart events. Next, sort by user, session, SKU, and timestamp, and use a vectorized approach to identify events that start a new cluster (i.e., more than 2 minutes after the previous event in the same session and SKU). Finally, assign cluster IDs and count distinct clusters per user, local date, and SKU.

Pro tip: Mention that you would validate the deduplication logic by checking the distribution of time gaps and ensuring that events exactly 2 minutes apart are handled consistently. Also, note that using `groupby` with `diff` and `cumsum` is efficient and avoids UDFs.

1. Parse and filter

Extract event type, SKU, and timestamp from the JSON properties column using `json_normalize` or `str.extract`, then filter to keep only add-to-cart events.

2. Sort and compute time differences

Sort the DataFrame by user, session, SKU, and timestamp, then compute the time difference between consecutive events within each user-session-SKU group.

3. Identify cluster boundaries

Create a boolean flag indicating when the time difference exceeds 2 minutes (or when it's the first event in the group), then use cumulative sum to assign a unique cluster ID to each group of events within 2 minutes.

4. Deduplicate and count

Drop duplicates based on user, session, SKU, and cluster ID to keep one event per cluster, then group by user, local date, and SKU to count distinct deduplicated events.

Key Points to Mention

Use vectorized operations like `groupby`, `diff`, and `cumsum` to avoid UDFs and ensure performance.
Handle edge cases such as events exactly 2 minutes apart and multiple sessions per user.
Ensure the timestamp is in datetime format and consider timezone if local date is derived from timestamp.
Clarify that deduplication is within a session and SKU, so the grouping for time differences should include session and SKU.
After deduplication, count distinct events per user, local date, and SKU, which may require extracting local date from timestamp.
Mention that the approach scales well for large datasets due to vectorization.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.