# 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. ### Constraints & Assumptions - Preserve the scope, facts, inputs, and requested outputs from the prompt above. - If the prompt leaves a detail unspecified, state a reasonable assumption before relying on it. - Keep the answer interview-ready: concise enough to present, but concrete enough to implement or evaluate. ### Clarifying Questions to Ask - Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling. - Define the grain of each intermediate result before aggregating. - State expected output columns and ordering explicitly. ### What a Strong Answer Covers - A query or pandas plan that matches the requested output grain. - Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates. - A brief explanation of why the result is correct and how it handles edge cases. - Performance notes, indexes/partitioning, and validation queries when relevant. ### Follow-up Questions - How would you test the query on a tiny hand-built dataset? - What changes if duplicate events or late-arriving data are present? - Which indexes, clustering, or partitions would help at production scale?