Write SQL for 30-day activity and D7 retention

Q: Write SQL for 30-day activity and D7 retention

This is a Data Manipulation (SQL/Python) interview question from Citi for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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

Write SQL for 30-day activity and D7 retention

Table: trades

Table: users

Question 1 — 30-day trading intensity

Question 2 — 7-day retention (trading-based)

Comments (0)

Table: `trades`

Table: `users`