Find high-value crypto users and top CTR
Company: Revolut
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`.
Overview: 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.
Read the full Revolut Data Analyst interview experience this question came from
Two tasks: (1) Find users whose total completed crypto transaction amount in the first 7 days after signup exceeds £100, returning user_id and total_crypto_amount_7d. (2) Find the product(s) with the highest CTR from the activity table, where CTR = clicks/views, excluding products with zero views.
Tables
users(user_id BIGINT, create_date TIMESTAMP)
transactions(transaction_id BIGINT, user_id BIGINT, product VARCHAR, amount_gbp DECIMAL(12,2), status VARCHAR, transaction_time TIMESTAMP)
activity(user_id BIGINT, product VARCHAR, event_type VARCHAR, event_time TIMESTAMP)
Hints
- Use a join from users to transactions and filter by product='crypto', status='completed', and the 7-day window using create_date bounds.
- The 7-day window is inclusive at the start and exclusive at the end: [create_date, create_date + INTERVAL '7 day').