Find high-value crypto users and top-CTR product

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).