Write SQL for 30-day activity and D7 retention

Quick Overview

This question evaluates a candidate's skill in SQL-based data manipulation and product-analytics competencies—specifically aggregation, deduplication to avoid double-counting, date arithmetic for time-windowed queries, cohort retention calculation, and percentage metrics—in the Data Manipulation (SQL/Python) domain.

Company: Coinbase

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Technical Screen

You are given two tables. ### Table: `trades` - `user_id` (BIGINT) - `trade_time` (TIMESTAMP) — timestamp of the trade - `volume` (NUMERIC) - `asset` (TEXT) ### Table: `users` - `user_id` (BIGINT, PK) - `signup_date` (DATE) Assumptions: - `trade_time` is stored in UTC. - “Last 30 days” is relative to `CURRENT_DATE` (or `CURRENT_TIMESTAMP`) in UTC. - When counting users, avoid double-counting a user who has multiple trades. --- ## Question 1 — 30-day trading intensity Compute the **percentage of active traders** in the last 30 days who made **at least 3 trades** in the last 30 days. - Define the denominator as: users with **≥ 1** trade in the last 30 days. **Required output (single row):** - `active_users_30d` - `users_with_3plus_trades_30d` - `pct_users_with_3plus_trades_30d` --- ## Question 2 — 7-day retention (trading-based) Compute **D7 retention by signup cohort** where a user is considered **retained on D7** if they made **≥ 1 trade exactly on calendar day `signup_date + 7`**. - Only include cohorts where `signup_date <= CURRENT_DATE - 7` (so D7 is observable). **Required output (one row per `signup_date`):** - `signup_date` - `cohort_size` - `retained_users_d7` - `d7_retention_rate`

Overview: This question evaluates a candidate's skill in SQL-based data manipulation and product-analytics competencies—specifically aggregation, deduplication to avoid double-counting, date arithmetic for time-windowed queries, cohort retention calculation, and percentage metrics—in the Data Manipulation (SQL/Python) domain.

30-day heavy-trader share (>=3 trades)

You are given a table of crypto trades: trades(user_id, trade_time, volume, asset). Define the 30-day window as FROM 2025-05-03 TO 2025-06-01 (inclusive, by calendar date). An "active user" is any user who made at least 1 trade in this window. Write a SQL query to compute the proportion of active users who made at least 3 trades in this window. Return a single row with: - active_users - users_with_3plus_trades - proportion_3plus (users_with_3plus_trades / active_users as a decimal) Assume PostgreSQL syntax is acceptable.

Tables

trades(trade_id INT, user_id INT, trade_time TIMESTAMP, volume DECIMAL(18,2), asset VARCHAR(10))

Hints

First filter trades to the date window, then group by user_id to count trades.
Define the denominator as the number of distinct users who traded at least once in the window.

D7 retention based on trading on day 7 after signup

You are given: - users(user_id, signup_date) - trades(user_id, trade_time, volume, asset) Define D7 retention as: A user is "retained" if they make at least one trade on the calendar date equal to (signup_date + 7 days). Compute the D7 retention rate for the signup cohort FROM 2025-05-01 TO 2025-05-25 (inclusive). Return a single row with: - cohort_start_date - cohort_end_date - total_users - retained_users_d7 - d7_retention_rate Assume PostgreSQL syntax is acceptable.

Tables

users(user_id INT, signup_date DATE)

trades(trade_id INT, user_id INT, trade_time TIMESTAMP, volume DECIMAL(18,2), asset VARCHAR(10))

Hints

Be explicit about the D7 definition (trade date equals signup_date + 7 days).
A LEFT JOIN on the D7 date (or an EXISTS subquery) helps you flag retained users, then aggregate.