Calculate Total Spend and Identify Key User Metrics
Company: Yahoo
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Transactions
+----+---------+--------+------------+
| id | user_id | amount | date |
+----+---------+--------+------------+
| 1 | 101 | 23.50 | 2023-07-01 |
| 2 | 102 | 12.99 | 2023-07-01 |
| 3 | 101 | 50.00 | 2023-07-02 |
| 4 | 103 | 99.95 | 2023-07-02 |
| 5 | 101 | 9.99 | 2023-07-03 |
+----+---------+--------+------------+
##### Scenario
You have one table that stores all monetary transactions made by users of an e-commerce site.
##### Question
For every user, return total_spend rounded to two decimals. 2. Return the user_id(s) that have the second-highest total spend. 3. List users whose transaction_count exceeds the average transaction_count of all users. 4. For each calendar date, calculate revenue_drop_pct compared with the previous day and keep only dates where the drop is ≥10%. 5. For every user, return first_purchase_date together with amount spent on that first day.
##### Hints
Window functions, aggregation, self-joins, DATE arithmetic and ROUND will be useful.
Overview: This question evaluates a data scientist's proficiency in transactional data manipulation using SQL and Python, focusing on aggregation, ranking, joins, date arithmetic, and numeric rounding to produce user-level and time-series metrics.
Total spend per user
For every user, return their total_spend rounded to two decimals.
Tables
Transactions(id INTEGER, user_id INTEGER, amount DECIMAL(10,2), date DATE)
Hints
- Aggregate by user_id
- Use ROUND to two decimals
Second-highest total spenders
Return the user_id(s) that have the second-highest total spend across all transactions.
Tables
Transactions(id INTEGER, user_id INTEGER, amount DECIMAL(10,2), date DATE)
Hints
- First aggregate spend per user
- Use DENSE_RANK over total_spend DESC
Above-average transaction counts
List users whose transaction_count exceeds the average transaction_count computed across all users.
Tables
Transactions(id INTEGER, user_id INTEGER, amount DECIMAL(10,2), date DATE)
Hints
- Count transactions per user first
- Compare to the average of those counts
Daily revenue drop percent
For each calendar date, compute revenue_drop_pct compared with the previous day and return only dates where the drop is at least 10%.
Tables
Transactions(id INTEGER, user_id INTEGER, amount DECIMAL(10,2), date DATE)
Hints
- Aggregate daily revenue first
- Use LAG to access previous day
First purchase date spend
For every user, return the first_purchase_date and the total amount spent on that first day.
Tables
Transactions(id INTEGER, user_id INTEGER, amount DECIMAL(10,2), date DATE)
Hints
- Find MIN(date) per user
- Join back to sum amounts on that date