Bytedance·Data Analyst·Technical Phone Screen·Senior

SeniorPrefer not to say

May 2026Remote

Summary

SQL-heavy data analyst interview at Bytedance, two meaty scenario questions back to back with no warmup. Both were open-ended enough that I kept second-guessing whether I was going too deep or not deep enough.

Questions Asked (2)

Revolut has just launched a new paid subscription tier called Ultra. There's no dedicated subscription table, so you have to infer adoption from transactions and activity data. How would you define a successful launch in the first month, and what SQL analyses would you build around it?

Product Analytics & MetricsA/B Testing & ExperimentationData Modeling

Author's notes

This one sprawled in a way I wasn't ready for.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying that success should be measured against the launch goals—adoption, engagement, and retention—using proxy signals from transactions and activity data. Then outline a structured SQL analysis plan that identifies Ultra subscribers, tracks their behavior over time, and compares them to non-subscribers to isolate the subscription impact.

Pro tip: Acknowledge the data limitation upfront and propose a validation step: cross-check inferred subscribers against any available ground truth (e.g., customer support tickets or payment gateway logs) to estimate false positives and refine your logic.

1. Define success metrics

Translate 'successful launch' into measurable KPIs: adoption rate (new Ultra subscribers), engagement (feature usage, transaction frequency), retention (renewal or continued activity), and revenue impact (ARPU lift).

2. Infer Ultra subscription from data

Identify proxy signals: recurring monthly charges of the Ultra price, access to Ultra-exclusive features, or a sudden change in transaction patterns. Build a SQL logic to flag likely Ultra users.

3. Build cohort and trend analyses

Use SQL to track daily/weekly adoption, cohort retention, and compare Ultra users to a control group (e.g., similar users who didn't subscribe) to measure incremental impact.

4. Validate and iterate

Cross-check inferred subscribers with any available ground truth, quantify uncertainty, and refine the inference logic. Present findings with caveats and recommend data instrumentation improvements.

Key Points to Mention

Proxy identification: recurring payments, exclusive feature usage, or transaction patterns
Cohort analysis: track Ultra users over time to measure retention and engagement
Control group comparison: match Ultra users with similar non-subscribers to isolate effect
SQL techniques: window functions, joins, CTEs, and date filtering for time-based analysis
Data limitations: acknowledge lack of subscription table and propose validation methods
Business impact: tie metrics to revenue, retention, and overall product health

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Using only the available tables, how would you build a rule-based SQL system to flag suspicious users or transactions for the fraud team? Walk through the rules you'd write, how you'd combine them into a risk score or review queue, and how you'd evaluate whether the system is actually working.

Product Analytics & MetricsSystem DesignData Modeling

Author's notes

Felt more comfortable here than on the Ultra question.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the available tables and key fields (users, transactions, timestamps, amounts, device/IP, etc.), then propose a layered rule-based system: simple threshold rules, behavioral rules, and network/linkage rules. Combine rules into a weighted risk score, define a review queue with prioritization, and outline an evaluation plan using precision/recall, backtesting, and feedback loops.

Pro tip: Frame the system as a decision-support tool for the fraud team, not a fully automated blocker, and emphasize starting with high-precision rules to avoid alert fatigue, then iterating with feedback.

1. Understand the data and define fraud

Identify the tables and fields available (e.g., users, transactions, devices, IPs) and clarify what 'suspicious' means for this business (e.g., payment fraud, account takeover, promo abuse).

2. Design individual rules

Write SQL rules that flag anomalies: velocity checks (many transactions in short time), amount outliers (z-score or IQR), mismatched geolocation/IP, shared devices among many accounts, and new account high-value transactions.

3. Combine rules into a risk score

Assign weights to each rule based on severity and combine into a composite risk score; use SQL CASE statements or a scoring table to compute scores and rank users/transactions.

4. Build a review queue

Create a prioritized queue by filtering on risk score thresholds, sorting by score and recency, and including key context (user ID, transaction details, triggered rules) for the fraud team.

5. Evaluate and iterate

Measure precision, recall, and false positive rate using labeled data or manual review outcomes; backtest on historical data, monitor alert volume, and refine rules/weights based on feedback.

Key Points to Mention

Use of window functions (e.g., ROW_NUMBER, LAG) for velocity and behavioral patterns
Handling imbalanced data and the importance of precision over recall for high-volume systems
Weighted scoring and threshold tuning to balance fraud capture vs. alert fatigue
Incorporating network/linkage analysis (shared devices, IPs, payment methods)
Backtesting and A/B testing frameworks for rule evaluation
Feedback loop with fraud team to label outcomes and retrain/refine rules

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.