Identify Top Discounts and Monitor Code Submissions
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
Products
+------------+----------+-------+----------+
| product_id | category | price | discount |
+------------+----------+-------+----------+
| 101 | phone | 699 | 50 |
| 102 | phone | 799 | 60 |
| 201 | laptop | 999 | 150 |
| 202 | laptop | 1099 | 150 |
| 301 | watch | 199 | 20 |
+------------+----------+-------+----------+
Submissions
+---------------+-------------+-----------+
| submission_id | employer_id | status |
+---------------+-------------+-----------+
| 1 | 555 | buggy |
| 2 | 555 | non-buggy |
| 3 | 777 | buggy |
| 4 | 777 | buggy |
| 5 | 777 | non-buggy |
+---------------+-------------+-----------+
##### Scenario
E-commerce analytics and code-evaluation dashboard: identify top-discounted products per category and monitor buggy vs non-buggy code submissions for each employer.
##### Question
For every product category, return the product that has the maximum discount; if several products share that discount, choose the one with the smallest product_id. For each employer_id, count how many submissions are buggy and how many are non-buggy.
##### Hints
Use GROUP BY with MAX discount and MIN product_id tie-breaker; use conditional aggregation COUNT(CASE WHEN status='buggy' THEN 1 END).
Quick Answer: This question evaluates proficiency in data manipulation and aggregation concepts, including grouped summaries, tie-breaking selection among rows, and conditional counting across relational records using SQL or equivalent Python data tools.