Find high-value crypto users and top CTR
Company: Bytedance
Role: Data Analyst
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
You are given three tables. Assume all timestamps are stored in UTC.
- `users(user_id BIGINT PRIMARY KEY, create_date TIMESTAMP)`: one row per user account.
- `transactions(transaction_id BIGINT PRIMARY KEY, user_id BIGINT, product VARCHAR, amount_gbp DECIMAL(12,2), status VARCHAR, transaction_time TIMESTAMP)`: one row per transaction. `status` is either `'completed'` or `'declined'`.
- `activity(user_id BIGINT, product VARCHAR, event_type VARCHAR, event_time TIMESTAMP)`: one row per product interaction. `event_type` is either `'view'` or `'click'`.
Write SQL for both tasks below:
1. **High-value crypto users in the first 7 days after signup**
- For each user, use the window `[create_date, create_date + INTERVAL '7 day')`.
- Only count rows from `transactions` where `product = 'crypto'` and `status = 'completed'`.
- Return users whose total crypto transaction amount in that 7-day window is greater than £100.
- Required output columns: `user_id`, `total_crypto_amount_7d`.
2. **Product with the highest CTR**
- Compute CTR by product from `activity`, where:
- `clicks = COUNT(*)` for `event_type = 'click'`
- `views = COUNT(*)` for `event_type = 'view'`
- `ctr = clicks / views`
- Exclude products with zero views.
- Return the product or products with the highest CTR.
- Required output columns: `product`, `views`, `clicks`, `ctr`.
Quick Answer: This question evaluates proficiency in SQL data manipulation for a Data Analyst role, focusing on time-window aggregations, conditional and group aggregations, and event-level metric computation; it is categorized under Data Manipulation (SQL/Python) and emphasizes practical application rather than purely conceptual understanding.