Deduplicate events and rank products with SQL
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two tables.
Schema:
- events(event_id INT PRIMARY KEY, user_id INT, product_id INT, event_time TIMESTAMP, idempotency_key TEXT, amount_cents INT)
- products(product_id INT PRIMARY KEY, product_name TEXT)
Sample data (minimal but representative):
events
+----------+---------+------------+---------------------+-----------------+--------------+
| event_id | user_id | product_id | event_time | idempotency_key | amount_cents |
+----------+---------+------------+---------------------+-----------------+--------------+
| 101 | 1 | 10 | 2025-08-31 23:59:58 | abc | 1299 |
| 102 | 1 | 10 | 2025-08-31 23:59:59 | abc | 1299 |
| 103 | 2 | 10 | 2025-09-01 00:00:03 | def | 1299 |
| 104 | 2 | 10 | 2025-09-01 00:05:01 | def | 1299 |
| 105 | 2 | 20 | 2025-09-01 00:06:00 | ghi | 2599 |
| 106 | 3 | 20 | 2025-09-01 12:00:00 | jkl | 2599 |
| 107 | 1 | 30 | 2025-09-01 12:05:00 | mno | 3099 |
+----------+---------+------------+---------------------+-----------------+--------------+
products
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 10 | Basic Tee |
| 20 | Hoodie |
| 30 | Socks |
+------------+--------------+
Task A — De-duplicate retry events:
Some payments are retried and share the same (user_id, idempotency_key). Write ANSI SQL that keeps exactly one row per (user_id, idempotency_key), choosing the row with the earliest event_time; break ties by the smallest event_id. Return all columns of the kept rows.
Task B — Rank products by distinct purchasers for a given date:
Using the de-duplicated rows from Task A, write a single SQL query that returns, for the calendar date 2025-09-01 (UTC), the top 2 products by distinct purchasing users.
- Output columns: product_id, product_name, distinct_buyers, rank.
- Use window functions to compute ranks; break ties by product_id ASC.
- Do not use temporary tables; a CTE-based solution is acceptable.
Explain how your solution scales if events has 10^9 rows and how you would index to support it.
Overview: This question evaluates proficiency in SQL data manipulation and analytical querying—specifically deduplication using idempotency keys, window functions/CTEs for ranking, distinct aggregation by date, timestamp tie-breaking, and awareness of indexing and scalability trade-offs in large tables; the domain is Data Manipulation (SQL/Python).
De-duplicate retry events by (user_id, idempotency_key)
You are given an events table containing payment events. Some payments are retried and share the same (user_id, idempotency_key). Write ANSI SQL that keeps exactly one row per (user_id, idempotency_key), choosing the row with the earliest event_time; if there is still a tie, choose the row with the smallest event_id. Return all columns of the kept rows.
Tables
events(event_id INT, user_id INT, product_id INT, event_time TIMESTAMP, idempotency_key VARCHAR(64), amount_cents INT)
Hints
- Use a window function to assign a row number within each (user_id, idempotency_key) group.
- Order the window by event_time and then event_id to enforce the tie-breaker.
Rank products by distinct purchasers on a given date after de-duplication
You have a raw purchase-event log in the `events` table and a product catalog in the `products` table. Because the client retries network calls, the same logical purchase can be recorded multiple times — duplicate rows share the same `(user_id, idempotency_key)` pair (the duplicates may have different `event_id` and `event_time` values).
**Task.** Write a single PostgreSQL query that:
1. **De-duplicates** `events` so that, within each `(user_id, idempotency_key)` group, only ONE row survives — the one with the **earliest `event_time`**, breaking any tie on `event_time` by the **smallest `event_id`**. Do the de-duplication inside a CTE (or CTEs) — do not use temporary tables.
2. From those de-duplicated events, keep only the ones whose `event_time` falls on the calendar date **2025-09-01 (UTC)** — i.e. `event_time >= '2025-09-01 00:00:00'` and `< '2025-09-02 00:00:00'`.
3. For each product, count the number of **distinct purchasing users**, join to `products` for the name, and return the **top 2 products**.
**Output columns (in this order):** `product_id`, `product_name`, `distinct_buyers`, `rank`.
- `distinct_buyers` is the count of distinct `user_id` values for that product on that date (after de-duplication).
- `rank` is computed with a window function `RANK()` ordered by `distinct_buyers` DESC, then `product_id` ASC.
- Keep only rows with `rank <= 2`, and order the final result by `rank` ASC, then `product_id` ASC.
Tables
events(event_id INT, user_id INT, product_id INT, event_time TIMESTAMP, idempotency_key VARCHAR(64), amount_cents INT)
products(product_id INT, product_name VARCHAR(100))
Hints
- De-duplicate first: number rows with ROW_NUMBER() OVER (PARTITION BY user_id, idempotency_key ORDER BY event_time, event_id) inside a CTE and keep rn = 1.
- Reference the subquery's alias (e.g. t.*) in the outer SELECT, not the inner table alias — that scoping mistake is a common cause of an error.