Find high-value crypto users and top-CTR product
Company: Revolut
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).
Overview: 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.
Read the full Revolut Data Scientist interview experience this question came from
Two-part SQL task: (1) Return all users whose cumulative amount_gbp from completed crypto transactions is strictly greater than 100 within the first 7 days from their create_date (inclusive start, exclusive end), with columns (user_id, crypto_amount_7d_gbp). (2) From activity, compute per-product CTR = clicks/views (clicks = count where event_type = 'click'; views = count where event_type = 'view'), exclude products with views = 0, and return the product with the highest CTR with columns (product, ctr).
Tables
users(user_id BIGINT, create_date TIMESTAMP)
transactions(transaction_id BIGINT, user_id BIGINT, transaction_time TIMESTAMP, product VARCHAR, amount_gbp NUMERIC(18,2), status VARCHAR)
activity(user_id BIGINT, event_time TIMESTAMP, product VARCHAR, event_type VARCHAR)
Hints
- Use an inner join from transactions to users and constrain transaction_time to [create_date, create_date + interval '7 days').
- Sum only completed crypto transactions and use HAVING SUM(amount_gbp) > 100.
Community answers
Answer by maggiew
**WITH crypto_7d AS (
SELECT
u.user_id,
SUM(t.amount_gbp) AS crypto_amount_7d_gbp
FROM users u
JOIN transactions t
ON u.user_id = t.user_id
WHERE t.product = 'crypto'
AND t.status = 'completed'
AND t.transaction_time >= u.create_date
AND t.transaction_time < u.create_date + INTERVAL '7' DAY
GROUP BY
u.user_id
HAVING SUM(t.amount_gbp) > 100
),
product_event_counts AS (
SELECT
product,
SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks,
SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS views
FROM activity
GROUP BY
product
HAVING SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) > 0
),
product_ctr AS (
SELECT
product,
clicks * 1.0 / views AS ctr
FROM product_event_counts
),
top_ctr_product AS (
SELECT
product,
ctr
FROM (
SELECT
product,
ctr,
ROW_NUMBER() OVER (
ORDER BY ctr DESC, product ASC
) AS rn
FROM product_ctr
) ranked
WHERE rn = 1
)
SELECT
'high_value_crypto_user' AS result_type,
user_id,
crypto_amount_7d_gbp,
CAST(NULL AS VARCHAR) AS product,
CAST(NULL AS NUMERIC(18, 6)) AS ctr
FROM crypto_7d
UNION ALL
SELECT
'top_ctr_product' AS result_type,
CAST(NULL AS BIGINT) AS user_id,
CAST(NULL AS NUMERIC(18, 2)) AS crypto_amount_7d_gbp,
product,
ctr
FROM top_ctr_product;
**