Identify Top Discounts and Monitor Code Submissions
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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).
Overview: 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.
Top Discount Per Category
For each product category, return the single product that has the maximum discount; if multiple products share the maximum discount, choose the one with the smallest product_id. Output category, product_id, price, and discount.
Tables
Products(product_id INTEGER, category VARCHAR(20), price DECIMAL(10,2), discount INTEGER)
Submissions(submission_id INTEGER, employer_id INTEGER, status VARCHAR(10))
Hints
- Use a window function (ROW_NUMBER) to rank products within each category.
- Order by discount descending and product_id ascending to implement the tie-breaker.
Buggy vs Non-buggy Counts
For each employer_id, count how many submissions are buggy and how many are non-buggy. Return employer_id, buggy_count, and non_buggy_count.
Tables
Products(product_id INTEGER, category VARCHAR(20), price DECIMAL(10,2), discount INTEGER)
Submissions(submission_id INTEGER, employer_id INTEGER, status VARCHAR(10))
Hints
- Use conditional aggregation with CASE expressions or filtered COUNTs.
- Group by employer_id and aggregate buggy vs non-buggy in separate columns.