Coding questions · Snowflake
Query seven-day conversion with windows and dedupe
This question evaluates proficiency in time-windowed aggregations, event deduplication, and window-function–based user-level analytics across SQL and Pandas workflows.
Assume all timestamps are UTC. Treat "today" as 2025-09-01. Define "last 7 days" as the inclusive window [2025-08-26 00:00:00, 2025-09-01 23:59:59]. Schema (invented):
users(user_id INT, signup_at TIMESTAMP, tz STRING)
orders(order_id INT, user_id INT, order_ts TIMESTAMP, amount DECIMAL(10,2), status STRING) -- status in ('completed','canceled')
events(event_id INT, user_id INT, event_ts TIMESTAMP, event_type STRING, product_id INT, device_id STRING)
Sample rows:
users
user_id | signup_at | tz
1 | 2025-08-10 09:00:00 | America/Los_Angeles
2 | 2025-08-28 14:00:00 | UTC
3 | 2025-08-30 23:30:00 | America/New_York
orders
order_id | user_id | order_ts | amount | status
101 | 1 | 2025-08-27 23:55:00| 20.00 | completed
102 | 1 | 2025-08-28 00:05:00| 15.00 | canceled
103 | 2 | 2025-08-31 12:00:00| 9.99 | completed
104 | 2 | 2025-09-01 00:00:10| 49.00 | completed
105 | 3 | 2025-08-26 23:59:59| 5.00 | completed
events
event_id | user_id | event_ts | event_type | product_id | device_id
1 | 1 | 2025-08-27 23:50:00| view_product | 555 | A
2 | 1 | 2025-08-27 23:50:00| view_product | 555 | A -- duplicate of 1
3 | 1 | 2025-08-28 00:03:00| add_to_cart | 555 | A
4 | 1 | 2025-08-28 00:04:00| purchase | 555 | A
5 | 2 | 2025-08-31 11:58:00| view_product | 777 | B
6 | 3 | 2025-08-26 23:55:00| view_product | 999 | C
7 | 3 | 2025-08-26 23:58:00| purchase | 999 | C
8 | 2 | 2025-09-01 00:00:10| purchase | 777 | B
Tasks (write a single Standard SQL query; CTEs allowed):
- At the day level (UTC days), for each day in the 7-day window, compute: unique_viewers = COUNT(DISTINCT user_id with >=1 view_product event that day after deduplicating exact duplicate events), purchasers = COUNT of completed orders that day (exclude canceled), and conversion_rate = purchasers / NULLIF(unique_viewers,0).
- At the user level, within the same window, return for every user_id: first_completed_order_ts, last_completed_order_ts (NULL if none), and total_completed_orders. Use window functions, not correlated subqueries.
- Event deduplication: treat rows with identical (user_id, event_ts, event_type, product_id, device_id) as duplicates; keep only one (e.g., ROW_NUMBER over these keys).
- Output two result sets: (a) daily metrics ordered by day; (b) user-level metrics ordered by user_id. Bonus: Provide a concise Pandas solution sketch achieving (1)-(3) with correct time windowing and deduplication.
7-day daily conversion metrics (UTC), with exact-event deduplication
Assume all timestamps are stored in UTC. Treat "today" as 2025-06-01, and define the last 7 days as the inclusive window from 2025-05-26 00:00:00 to 2025-06-01 23:59:59.
Using PostgreSQL, return one row for every UTC calendar day in the 7-day window (2025-05-26 through 2025-06-01), even on days that have zero events or orders. For each day report:
day— the UTC calendar day (adate).unique_viewers—COUNT(DISTINCT user_id)of users who had at least one deduplicatedview_productevent that day (0if none).purchasers— the number of completed orders placed that day, i.e.COUNT(*)overorderswherestatus = 'completed'(excludecanceled);0if none.conversion_rate—purchasers / unique_viewers, returned as a numeric rounded to 4 decimal places. Whenunique_viewers = 0the rate must beNULL(useNULLIFto avoid division by zero).
Event-deduplication rule: within the events table, rows that share an identical tuple (user_id, event_ts, event_type, product_id, device_id) are duplicates; keep exactly one of them (e.g. via ROW_NUMBER() partitioned by those five columns) before counting viewers.
The day a row belongs to is the UTC day of its timestamp (event_ts for events, order_ts for orders). Only rows whose timestamp falls inside the 7-day window count.
Order the final output by day ascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_at | TIMESTAMP | NOT NULL |
| tz | VARCHAR(64) | NOT NULL |
Sample data:
| signup_at | tz | user_id |
|---|---|---|
| 2025-05-10 09:00:00 | America/Los_Angeles | 1 |
| 2025-05-28 14:00:00 | UTC | 2 |
| 2025-05-30 23:30:00 | America/New_York | 3 |
| 2025-05-20 08:15:00 | UTC | 4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_ts | TIMESTAMP | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| status | VARCHAR(16) | NOT NULL |
Sample data:
| amount | order_id | order_ts | status | user_id |
|---|---|---|---|---|
| 20 | 101 | 2025-05-27 23:55:00 | completed | 1 |
| 15 | 102 | 2025-05-28 00:05:00 | canceled | 1 |
| 9.99 | 103 | 2025-05-31 12:00:00 | completed | 2 |
| 49 | 104 | 2025-06-01 00:00:10 | completed | 2 |
| 5 | 105 | 2025-05-26 23:59:59 | completed | 3 |
| 12 | 106 | 2025-06-02 00:00:00 | completed | 3 |
Table events
| column | type | constraints |
|---|---|---|
| event_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(32) | NOT NULL |
| product_id | INT | NOT NULL |
| device_id | VARCHAR(64) | NOT NULL |
Sample data:
| device_id | event_id | event_ts | event_type | product_id | user_id |
|---|---|---|---|---|---|
| A | 1 | 2025-05-27 23:50:00 | view_product | 555 | 1 |
| A | 2 | 2025-05-27 23:50:00 | view_product | 555 | 1 |
| A | 3 | 2025-05-28 00:03:00 | add_to_cart | 555 | 1 |
| A | 4 | 2025-05-28 00:04:00 | purchase | 555 | 1 |
| B | 5 | 2025-05-31 11:58:00 | view_product | 777 | 2 |
| C | 6 | 2025-05-26 23:55:00 | view_product | 999 | 3 |
| C | 7 | 2025-05-26 23:58:00 | purchase | 999 | 3 |
| B | 8 | 2025-06-01 00:00:10 | purchase | 777 | 2 |
| B | 9 | 2025-06-01 08:00:00 | view_product | 777 | 2 |
| D | 10 | 2025-05-29 10:00:00 | view_product | 888 | 4 |
| D | 11 | 2025-05-29 10:00:00 | view_product | 888 | 4 |
Expected output
One row per UTC day from 2025-05-26 through 2025-06-01 (inclusive) with unique_viewers, purchasers, and conversion_rate (NULL when there are no viewers), ordered by day ascending.
| conversion_rate | day | purchasers | unique_viewers |
|---|---|---|---|
| 1.0000 | 2025-05-26 | 1 | 1 |
| 1.0000 | 2025-05-27 | 1 | 1 |
| NULL | 2025-05-28 | 0 | 0 |
| 0.0000 | 2025-05-29 | 0 | 1 |
| NULL | 2025-05-30 | 0 | 0 |
| 1.0000 | 2025-05-31 | 1 | 1 |
| 1.0000 | 2025-06-01 | 1 | 1 |
Hints
- Build a calendar with generate_series(start_day, end_day, INTERVAL '1 day') and LEFT JOIN your daily aggregates onto it so zero-activity days still appear.
- Deduplicate events with ROW_NUMBER() OVER (PARTITION BY user_id, event_ts, event_type, product_id, device_id ORDER BY event_id) and keep rn = 1 before counting viewers.
- Guard the ratio with purchasers::numeric / NULLIF(unique_viewers, 0) so empty-viewer days return NULL instead of erroring.
7-day user-level completed-order metrics using window functions
Assume all timestamps are UTC. Treat “today” as 2025-06-01. Define the “last 7 days” as the inclusive window from 2025-05-26 00:00:00 to 2025-06-01 23:59:59.
Using Standard SQL (CTEs allowed) and window functions (no correlated subqueries), return one row per user_id (including users with zero completed orders in the window) with:
- first_completed_order_ts: earliest completed order timestamp in the window (NULL if none)
- last_completed_order_ts: latest completed order timestamp in the window (NULL if none)
- total_completed_orders: number of completed orders in the window (0 if none)
Exclude canceled orders. Order the output by user_id ascending.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_at | TIMESTAMP | NOT NULL |
| tz | VARCHAR(64) | NOT NULL |
Sample data:
| signup_at | tz | user_id |
|---|---|---|
| 2025-05-10 09:00:00 | America/Los_Angeles | 1 |
| 2025-05-28 14:00:00 | UTC | 2 |
| 2025-05-30 23:30:00 | America/New_York | 3 |
| 2025-05-20 08:15:00 | UTC | 4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_ts | TIMESTAMP | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| status | VARCHAR(16) | NOT NULL |
Sample data:
| amount | order_id | order_ts | status | user_id |
|---|---|---|---|---|
| 20 | 101 | 2025-05-27 23:55:00 | completed | 1 |
| 15 | 102 | 2025-05-28 00:05:00 | canceled | 1 |
| 9.99 | 103 | 2025-05-31 12:00:00 | completed | 2 |
| 49 | 104 | 2025-06-01 00:00:10 | completed | 2 |
| 5 | 105 | 2025-05-26 23:59:59 | completed | 3 |
| 12 | 106 | 2025-06-02 00:00:00 | completed | 3 |
Table events
| column | type | constraints |
|---|---|---|
| event_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(32) | NOT NULL |
| product_id | INT | NOT NULL |
| device_id | VARCHAR(64) | NOT NULL |
Sample data:
| device_id | event_id | event_ts | event_type | product_id | user_id |
|---|---|---|---|---|---|
| A | 1 | 2025-05-27 23:50:00 | view_product | 555 | 1 |
| A | 2 | 2025-05-27 23:50:00 | view_product | 555 | 1 |
| A | 3 | 2025-05-28 00:03:00 | add_to_cart | 555 | 1 |
| A | 4 | 2025-05-28 00:04:00 | purchase | 555 | 1 |
| B | 5 | 2025-05-31 11:58:00 | view_product | 777 | 2 |
| C | 6 | 2025-05-26 23:55:00 | view_product | 999 | 3 |
| C | 7 | 2025-05-26 23:58:00 | purchase | 999 | 3 |
| B | 8 | 2025-06-01 00:00:10 | purchase | 777 | 2 |
| B | 9 | 2025-06-01 08:00:00 | view_product | 777 | 2 |
| D | 10 | 2025-05-29 10:00:00 | view_product | 888 | 4 |
| D | 11 | 2025-05-29 10:00:00 | view_product | 888 | 4 |
Expected output
One row per user_id with first/last completed order timestamp and count of completed orders within the window. Users with no completed orders should have NULL timestamps and 0 count.
| first_completed_order_ts | last_completed_order_ts | total_completed_orders | user_id |
|---|---|---|---|
| 2025-05-27 23:55:00 | 2025-05-27 23:55:00 | 1 | 1 |
| 2025-05-31 12:00:00 | 2025-06-01 00:00:10 | 2 | 2 |
| 2025-05-26 23:59:59 | 2025-05-26 23:59:59 | 1 | 3 |
| NULL | NULL | 0 | 4 |
Hints
- Filter to completed orders within the timestamp window first, then apply window functions.
- Use MIN/MAX/COUNT window functions partitioned by user_id, and pick one row per user with ROW_NUMBER().
- Left join to users to keep users with zero completed orders.