Problem Description

You are interviewing for a Product/Risk Data Scientist role at a crypto exchange. All timestamps are assumed to be in UTC.

Data Schema

We have two primary tables:

• trades: Records individual trade events.
  • user_id: Unique identifier for the user.
  • trade_time: Timestamp of the trade.
  • volume: Volume of the trade.
  • asset: The asset traded (e.g., BTC, ETH).
• users: Contains user registration information.
  • user_id: Unique identifier for the user.
  • signup_date: The calendar date when the user signed up.

Task 1: 30-day Power Trader Share

Define the "last 30 days" as the period from CURRENT_DATE - INTERVAL '30 day' up to, but not including, CURRENT_DATE.

Calculate the percentage of active traders within this 30-day window who qualify as "power traders."

• Active Trader (Denominator): A user who has made at least one trade within the last 30 days.
• Power Trader (Numerator): A user who has made at least three trades within the last 30 days.

Your output should be a single row containing:

• active_traders_30d: The total count of active traders.
• power_traders_30d: The total count of power traders.
• pct_power_traders_30d: The percentage of power traders among active traders, expressed as a decimal between 0 and 1.

Task 2: 7-day Retention (Trade-Based)

Define "7-day retention" for a user as making at least one trade on the specific calendar date that is exactly seven days after their signup_date. That is, a trade must occur on signup_date + 7 (UTC date).

For each signup_date cohort, compute the following metrics:

• cohort_size: The total number of users who signed up on that signup_date.
• retained_users_d7: The number of users from that cohort who exhibited 7-day retention.
• d7_retention_rate: The retention rate for the cohort, expressed as a decimal.

Notes:

• All users, including those who never trade, should be considered when determining cohort_size.
• A user is considered retained if they have at least one trade whose UTC date matches signup_date + 7.
• The output should be grouped by signup_date and ordered by signup_date in ascending order.