Find high-value crypto users and top CTR

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 .