Coding questions · Yahoo
Calculate Total Spend and Identify Key User Metrics
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.
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.
Total spend per user
For every user, return their total_spend rounded to two decimals.
Table Transactions
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| date | DATE | NOT NULL |
Sample data:
| amount | date | id | user_id |
|---|---|---|---|
| 23.5 | 2023-07-01 | 1 | 101 |
| 12.99 | 2023-07-01 | 2 | 102 |
| 50 | 2023-07-02 | 3 | 101 |
| 99.95 | 2023-07-02 | 4 | 103 |
| 9.99 | 2023-07-03 | 5 | 101 |
Expected output
Total spend per user computed from all their transactions, rounded to two decimals.
| total_spend | user_id |
|---|---|
| 83.49 | 101 |
| 12.99 | 102 |
| 99.95 | 103 |
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.
Table Transactions
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| date | DATE | NOT NULL |
Sample data:
| amount | date | id | user_id |
|---|---|---|---|
| 23.5 | 2023-07-01 | 1 | 101 |
| 12.99 | 2023-07-01 | 2 | 102 |
| 50 | 2023-07-02 | 3 | 101 |
| 99.95 | 2023-07-02 | 4 | 103 |
| 9.99 | 2023-07-03 | 5 | 101 |
Expected output
User IDs whose total spend ranks second when ordering totals descending. Includes all ties for second place if any.
| user_id |
|---|
| 101 |
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.
Table Transactions
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| date | DATE | NOT NULL |
Sample data:
| amount | date | id | user_id |
|---|---|---|---|
| 23.5 | 2023-07-01 | 1 | 101 |
| 12.99 | 2023-07-01 | 2 | 102 |
| 50 | 2023-07-02 | 3 | 101 |
| 99.95 | 2023-07-02 | 4 | 103 |
| 9.99 | 2023-07-03 | 5 | 101 |
Expected output
Users whose number of transactions is strictly greater than the average number of transactions per user.
| transaction_count | user_id |
|---|---|
| 3 | 101 |
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%.
Table Transactions
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| date | DATE | NOT NULL |
Sample data:
| amount | date | id | user_id |
|---|---|---|---|
| 23.5 | 2023-07-01 | 1 | 101 |
| 12.99 | 2023-07-01 | 2 | 102 |
| 50 | 2023-07-02 | 3 | 101 |
| 99.95 | 2023-07-02 | 4 | 103 |
| 9.99 | 2023-07-03 | 5 | 101 |
Expected output
Dates where day-over-day revenue decreased by at least 10%, with the percentage drop rounded to two decimals.
| date | revenue_drop_pct |
|---|---|
| 2023-07-03 | 93.34 |
Hints
- Aggregate daily revenue first
- Use LAG to access previous day
- Filter drops >= 10%
First purchase date spend
For every user, return the first_purchase_date and the total amount spent on that first day.
Table Transactions
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| date | DATE | NOT NULL |
Sample data:
| amount | date | id | user_id |
|---|---|---|---|
| 23.5 | 2023-07-01 | 1 | 101 |
| 12.99 | 2023-07-01 | 2 | 102 |
| 50 | 2023-07-02 | 3 | 101 |
| 99.95 | 2023-07-02 | 4 | 103 |
| 9.99 | 2023-07-03 | 5 | 101 |
Expected output
Each user's first transaction date along with the total amount they spent on that first day.
| amount_on_first_day | first_purchase_date | user_id |
|---|---|---|
| 23.5 | 2023-07-01 | 101 |
| 12.99 | 2023-07-01 | 102 |
| 99.95 | 2023-07-02 | 103 |
Hints
- Find MIN(date) per user
- Join back to sum amounts on that date