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.

1. 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.