Define Ultra success and detect suspicious transactions

Assume the following relevant columns are available, and all timestamps are stored in UTC:

• users(user_id BIGINT PRIMARY KEY, create_date TIMESTAMP, country VARCHAR) : account creation time and registration country.
• transactions(transaction_id BIGINT PRIMARY KEY, user_id BIGINT, product VARCHAR, amount_gbp DECIMAL(12,2), status VARCHAR, transaction_time TIMESTAMP) : one row per transaction.
• activity(user_id BIGINT, product VARCHAR, event_type VARCHAR, event_time TIMESTAMP, ip_country VARCHAR) : one row per product interaction, including the most recent observed IP country.

Answer both scenario questions below.

1. Ultra subscription launch Revolut launches a new paid subscription plan called Ultra . There is no dedicated subscription table, so you must infer adoption from the existing tables, for example via product = 'ultra' in transactions and activity. Define what a successful launch means in the first month and describe the SQL analyses you would build. Your answer should include:
   • one primary success metric and several supporting metrics,
   • denominator choices such as all users vs active users vs new users, and the trade-offs of each,
   • segmentation by country and user tenure,
   • how to estimate Ultra DAU/WAU/MAU, adoption, penetration, and early retention,
   • how to avoid misleading conclusions caused by seasonality, country mix shifts, or self-selection.
2. Rule-based suspicious transaction detection You are partnering with the Fincrime team to build a rule-based SQL system that flags suspicious users or transactions for manual review. Using only the available tables, translate the vague concept of “suspicious” into executable rules. Consider behaviors such as:
   • unusually large amounts,
   • bursts of transactions in a short time window,
   • repeated small probing attempts,
   • sudden activity across many products,
   • mismatch between registration country and recent IP country.
   Define:
   • the alert table you would output,
   • example rules and thresholds,
   • how to combine rules into a risk score or review queue,
   • how to evaluate precision, recall, and false positives.