Write SQL for 30-day activity and D7 retention
Company: Citi
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`
Quick Answer: 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.