Compute 30-day power users and 7-day retention

You are interviewing for a Product/Risk Data Scientist role at a crypto exchange. Assume all timestamps are in UTC. ### Tables ```sql trades( user_id BIGINT, trade_time TIMESTAMP, volume NUMERIC, asset VARCHAR ) users( user_id BIGINT, signup_date DATE ) ``` ### Task 1 — 30-day “power trader” share Define the last 30 days as `[CURRENT_DATE - INTERVAL '30 day', CURRENT_DATE)`. Compute the **percentage of active traders** in the last 30 days who made **at least 3 trades** in that same 30-day window. - **Active trader (denominator):** a user with **≥ 1 trade** in the last 30 days. - **Numerator:** users with **trade_count ≥ 3** in the last 30 days. Return one row with: - `active_traders_30d` - `power_traders_30d` - `pct_power_traders_30d` (as a decimal between 0 and 1) ### Task 2 — 7-day retention (trade-based) Define **7-day retention** as: a signed-up user makes **at least one trade on the 7th day after signup**, i.e. on the calendar date `signup_date + 7` (UTC date). For each `signup_date` cohort, compute: - `cohort_size` - `retained_users_d7` - `d7_retention_rate` Notes: - Include users even if they never trade. - A user counts as retained if they have ≥1 trade whose UTC date equals `signup_date + 7`. - Output should be grouped by `signup_date` and ordered by `signup_date` ascending.