Write SQL/Python for messy event data
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using the schema and sample data below, write: (1) a single SQL query to compute daily metrics for the local date 2025-09-01 in America/Los_Angeles, and (2) a Python (pandas) transformation.
Goal A (SQL): For 2025-09-01 (America/Los_Angeles local date derived from UTC timestamps), output one row with:
- new_buyers: users whose first paid order occurred on that local date, excluding paid orders refunded within 24 hours of order_time_utc and all canceled orders.
- cart_to_paid_new: among users counted as new_buyers, the share who had at least one add_to_cart event in the 7 local days prior to their first paid order; deduplicate add_to_cart events within the same session_id by collapsing events that are ≤10 minutes apart into one.
- cart_to_paid_returning: same conversion on that local date for users who placed a paid order that day but whose first paid order was before 2025-09-01.
- srm_p_value: a sample-ratio-mismatch p-value for the treatment vs control split among users observed on 2025-09-01 (based on experiments.variant), assuming 50/50 expected split.
Assumptions: timestamps are stored in UTC; convert to America/Los_Angeles for local dates; if multiple paid orders exist on the first-paid day, treat the earliest qualifying paid order as first.
Goal B (Python/pandas): Given a DataFrame events with the columns below (including a dict-like props_json), produce a DataFrame with columns [user_id, local_dt, sku, dedup_add_to_cart_cnt] where local_dt is the America/Los_Angeles date of event_time_utc; deduplicate add_to_cart within each [user_id, session_id, sku] by treating any subsequent add_to_cart within 2 minutes as the same action; count distinct deduped add_to_cart per [user_id, local_dt, sku]. Show idiomatic pandas code (no UDFs) and explain time zone handling.
Schema and small ASCII samples:
users
---------
user_id | signup_utc | referrer
1 | 2025-08-20 13:02:10 | ads
2 | 2025-08-28 21:50:05 | seo
3 | 2025-08-31 02:11:34 | direct
4 | 2025-09-01 04:00:12 | ads
5 | 2025-09-01 05:47:40 | partner
events
---------------------------------------------------------------------------------------------
user_id | event_time_utc | event_type | session_id | device | props_json
1 | 2025-08-30 16:00:00 | add_to_cart | s1 | ios | {"sku":"A1","qty":1}
1 | 2025-08-30 16:01:00 | add_to_cart | s1 | ios | {"sku":"A1","qty":1}
1 | 2025-08-30 16:05:00 | purchase_click | s1 | ios | {}
2 | 2025-09-01 01:02:03 | add_to_cart | s2 | web | {"sku":"B2","qty":2}
3 | 2025-08-25 09:10:00 | add_to_cart | s3 | android | {"sku":"C3","qty":1}
orders
--------------------------------------------------------------------------------------
order_id | user_id | order_time_utc | amount_usd | status | refund_time_utc
10 | 1 | 2025-09-01 16:15:00 | 120.00 | paid | 2025-09-01 18:00:00
11 | 1 | 2025-09-01 16:20:00 | 35.00 | canceled | null
12 | 2 | 2025-09-01 02:15:00 | 50.00 | paid | null
13 | 3 | 2025-08-26 11:00:00 | 20.00 | paid | 2025-08-27 08:00:00
14 | 4 | 2025-09-02 07:00:00 | 15.00 | paid | null
experiments
-----------------------------
user_id | dt | variant
1 | 2025-09-01 | treatment
2 | 2025-09-01 | control
3 | 2025-09-01 | treatment
4 | 2025-09-02 | control
5 | 2025-09-01 | treatment
Overview: This question evaluates SQL and pandas proficiency for event-level data manipulation, including time zone–aware local date conversion, session-based deduplication, cohort and conversion metric calculation, and basic experiment sanity checking via a sample-ratio-mismatch p-value.
Read the full Google Data Scientist interview experience this question came from
You are working with an e-commerce dataset spread across four tables (`users`, `events`, `orders`, `experiments`). **All `TIMESTAMP` columns are stored in UTC.** Write a **single PostgreSQL query** that produces daily buyer-funnel metrics for the **America/Los_Angeles local date `2025-09-01`**, plus a sample-ratio-mismatch (SRM) check.
The query must return **exactly one row** with these five columns, in this order:
1. **`local_dt`** — the literal date `2025-09-01` (the local date the metrics are computed for).
2. **`new_buyers`** — the count of users whose **first qualifying paid order** falls on local date `2025-09-01` (America/Los_Angeles).
- A **qualifying paid order** is an order with `status = 'paid'` where either `refund_time_utc IS NULL` **or** `refund_time_utc > order_time_utc + INTERVAL '24 hours'` (i.e. exclude paid orders refunded within 24 hours). Orders with `status = 'canceled'` never qualify.
- The local order date is derived by converting `order_time_utc` from UTC to America/Los_Angeles.
- "First" = the qualifying paid order with the earliest `order_time_utc` for that user.
3. **`cart_to_paid_new`** — among the `new_buyers`, the **fraction** (0–1, rounded to 4 decimals) who had **at least one deduplicated `add_to_cart` action** in the **7 local days before** their first qualifying paid order on `2025-09-01`.
- Lookback window (local time): from `anchor_local_ts - INTERVAL '7 days'` up to but **not including** `anchor_local_ts`, where `anchor_local_ts` is the LA-local timestamp of their first qualifying paid order on `2025-09-01`.
- Only consider `events.event_type = 'add_to_cart'`.
- **Dedup rule:** within the same `(user_id, session_id)`, collapse consecutive `add_to_cart` events that are **≤ 10 minutes apart** (in UTC) into a single action. A user "had add_to_cart" if at least one such deduplicated action falls in the window.
4. **`cart_to_paid_returning`** — the same conversion fraction (rounded to 4 decimals), but for **returning buyers**: users who placed at least one qualifying paid order on local date `2025-09-01` whose **first** qualifying paid order (by local date) was **before** `2025-09-01`. Anchor each such user's 7-day lookback to their **earliest qualifying paid order on `2025-09-01`** (LA-local).
5. **`srm_p_value`** — a **two-sided binomial SRM p-value** (rounded to 4 decimals) for the treatment-vs-control split among the rows in `experiments` where `dt = '2025-09-01'`, under the null of a 50/50 split.
- Let `n` = total treatment + control assignments on that date and `k_min = LEAST(treatment, control)`. Compute `p = 2 * sum_{x=0}^{k_min} C(n,x) * 0.5^n` (use `generate_series`, `factorial`, and `power`).
**Requirements:** correctly convert UTC → America/Los_Angeles for every local-date and 7-day-window computation; use window functions for the `add_to_cart` dedup and `DISTINCT ON` (or equivalent) for the first-paid logic; and order/shape the output so it returns a single deterministic row. With the sample data: user 3 is the only **new** buyer on `2025-09-01` (no cart in the 7-day window → `cart_to_paid_new = 0`), user 2 is a **returning** buyer who had a cart in-window (`cart_to_paid_returning = 1`), and the experiment split on `2025-09-01` is 3 treatment vs 1 control (`srm_p_value = 0.6250`).
*(Note: the `props_json` column is provided for context only and is not needed for this query.)*
Tables
users(user_id INT, signup_utc TIMESTAMP, referrer VARCHAR(50))
events(user_id INT, event_time_utc TIMESTAMP, event_type VARCHAR(50), session_id VARCHAR(50), device VARCHAR(20), props_json JSON)
orders(order_id INT, user_id INT, order_time_utc TIMESTAMP, amount_usd DECIMAL(10,2), status VARCHAR(20), refund_time_utc TIMESTAMP)
experiments(user_id INT, dt DATE, variant VARCHAR(20))
Hints
- Convert each UTC timestamp with `ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles'`, then take `::date` for the local date and compare local timestamps for the 7-day window.
- Use `DISTINCT ON (user_id) ... ORDER BY user_id, order_time_utc` to get each user's first qualifying paid order, and a separate pass for their first order on 2025-09-01 to set the lookback anchor.