Katkat
Back

Choose Ranking Functions, Customer Value Metrics, and Predictive Models

ByteDanceData Scientist · Technical Screen
MediumData ScientistTechnical ScreenMachine Learning

Last updated

This problem tests the ability to implement SQL window functions for cohort analysis, define robust customer lifetime value metrics, and articulate the trade-offs between various machine learning algorithms in a production setting.

Part 1: SQL Ranking

You have a table transactions with columns user_id, purchase_date, and amount. Write a SQL query to identify the top 3 purchasers for every month in 2023. If there is a tie in the total amount spent, handle it by assigning the same rank to tied users but ensure the output list for each month does not exceed 3 ranks.

Part 2: Defining High-Value Customers

Your stakeholders want to define 'High-Value Customers' (HVCs) to focus marketing efforts.

  1. Propose a metric to define an HVC. Why is a simple 'top 10% by revenue' potentially flawed?
  2. How would you incorporate churn risk or acquisition cost into this definition?

Part 3: Model Selection

You are tasked with predicting whether a user will subscribe to a premium service within 30 days of registration. You have a dataset with 50 features including demographics, clickstream behavior, and initial engagement metrics.

Compare the following models for this use case, specifically addressing trade-offs in interpretability, training speed, and performance on non-linear feature interactions:

  • Logistic Regression
  • Random Forest
  • Gradient Boosted Trees (e.g., XGBoost/LightGBM)

Constraints & Context

  • Assume the transactions table has 100M+ rows.
  • For Part 3, assume the class distribution is imbalanced (only 2% of users convert).

Hints

Hint 1

For the SQL part, consider using `DENSE_RANK()` or `RANK()` to handle ties, and wrap the query in a CTE or subquery to filter by the rank.

Hint 2

When defining HVCs, think about long-term sustainability: consider metrics like Customer Lifetime Value (CLV) or Recency-Frequency-Monetary (RFM) analysis instead of static snapshots.

Hint 3

For model selection, remember that while Gradient Boosted Trees often provide the highest accuracy, they require more hyperparameter tuning and are 'black-box' compared to Logistic Regression.

Follow-up questions

  • How would you address the data leakage if you accidentally included 'total lifetime spend' in your 30-day prediction model?
  • If you decide to use a Gradient Boosted model, what specific techniques would you use to handle the 2% conversion class imbalance?
  • How would you monitor the performance of your chosen model in production to detect data drift or concept drift?