Find high-value crypto users and top-CTR product
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given three tables (timezone: UTC). Assume `create_date`, `transaction_time`, and `event_time` are timestamps.
## Tables
### `users`
- `user_id` BIGINT PRIMARY KEY
- `create_date` TIMESTAMP -- account creation time
### `transactions`
- `transaction_id` BIGINT PRIMARY KEY
- `user_id` BIGINT NOT NULL REFERENCES `users(user_id)`
- `transaction_time` TIMESTAMP NOT NULL
- `product` VARCHAR NOT NULL -- e.g., `'crypto'`, `'card'`, etc.
- `amount_gbp` NUMERIC(18,2) NOT NULL
- `status` VARCHAR NOT NULL -- `'completed'` or `'declined'`
### `activity`
- `user_id` BIGINT NOT NULL REFERENCES `users(user_id)`
- `event_time` TIMESTAMP NOT NULL
- `product` VARCHAR NOT NULL
- `event_type` VARCHAR NOT NULL -- `'view'` or `'click'`
## Tasks
### 1) Find users with > £100 completed crypto volume in first 7 days
Return all users whose **cumulative** `amount_gbp` from **completed** transactions with `product = 'crypto'` is **strictly greater than 100** within the window:
- from `users.create_date` (inclusive)
- to `users.create_date + INTERVAL '7 days'` (exclusive)
**Output columns**:
- `user_id`
- `crypto_amount_7d_gbp`
### 2) Find the product with the highest CTR
Using the `activity` table, compute per-product:
- `views = count(*) where event_type = 'view'`
- `clicks = count(*) where event_type = 'click'`
- `ctr = clicks / views`
Return the product with the **highest CTR**.
**Output columns**:
- `product`
- `ctr`
Notes:
- Define how you handle products with `views = 0` (e.g., exclude them).
Quick Answer: This question evaluates the ability to perform time-windowed aggregations, cumulative transaction summation, cross-table joins, and click-through-rate (CTR) calculations using SQL or Python, with attention to timestamp handling and event filtering.