Problem Description

You are provided with two tables: trades and users.

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 within that period.

• The denominator for this percentage should be defined as users with at least 1 trade in the last 30 days.

Required output (single row):

• active_users_30d (Count of users with >= 1 trade in the last 30 days)
• users_with_3plus_trades_30d (Count of users with >= 3 trades in the last 30 days)
• pct_users_with_3plus_trades_30d (Percentage of users with >= 3 trades among active users)

Question 2: 7-day Retention (Trading-Based)

Compute D7 retention by signup cohort. A user is considered retained on D7 if they made at least one trade exactly on calendar day signup_date + 7.

• Only include cohorts where signup_date <= CURRENT_DATE - 7 to ensure D7 is observable.

Required output (one row per signup_date):

• signup_date
• cohort_size (Total users who signed up on signup_date)
• retained_users_d7 (Count of users from signup_date cohort who traded on signup_date + 7)
• d7_retention_rate (Percentage of retained_users_d7 out of cohort_size)