Coding questions · TikTok
Select max-discount product per category
This question evaluates proficiency in data manipulation and SQL querying—specifically use of window functions versus set-based alternatives, group-level ranking and tie-breaking, NULL-aware expressions, and numeric precision when computing discounts within the Data Manipulation (SQL/Python) domain.
You have a catalog of products. For each category, return exactly one product: the one with the largest absolute discount; if multiple products in the same category have the same discount, return the one with the smallest product_id. Define absolute discount as max(list_price - COALESCE(sale_price, list_price), 0). Return: category, product_id, list_price, sale_price, discount_amount. Write a single SQL query that works if sale_price can be NULL and never below 0, using window functions; also show a version without window functions. Schema and sample data:
Tables
- products(product_id INT PRIMARY KEY, category VARCHAR(50) NOT NULL, list_price DECIMAL(10,2) NOT NULL, sale_price DECIMAL(10,2) NULL)
Sample rows (products)
product_id | category | list_price | sale_price
101 | Phones | 799.00 | 699.00
102 | Phones | 999.00 | 749.00
103 | Phones | 999.00 | 749.00
201 | Laptops | 1299.00 | 1099.00
202 | Laptops | 1399.00 | 1199.00
301 | TVs | 899.00 | NULL
Expected result rows
category | product_id | list_price | sale_price | discount_amount
Phones | 102 | 999.00 | 749.00 | 250.00
Laptops | 201 | 1299.00 | 1099.00 | 200.00
TVs | 301 | 899.00 | NULL | 0.00
Sub-questions:
- Provide the window-function solution (ROW_NUMBER partitioned by category ordered by discount_amount DESC, product_id ASC).
- Provide a without-window solution (e.g., join to a per-category max-discount subquery with tie-break on product_id).
- Explain how your query avoids floating point precision issues and handles NULL sale_price.
Max-discount product per category using window functions
You have a catalog of products. For each category, return exactly one product: the one with the largest absolute discount. If multiple products in the same category have the same discount, return the one with the smallest product_id.
Define absolute discount as: discount_amount = MAX(list_price - COALESCE(sale_price, list_price), 0) (i.e., clamp negative values to 0).
Write a single SQL query that:
- Works even if sale_price is NULL.
- Assumes sale_price is never below 0.
- Uses a window function with ROW_NUMBER() PARTITION BY category ORDER BY discount_amount DESC, product_id ASC to pick the correct row per category.
- Also briefly (in a SQL comment) explain how your query avoids floating point precision issues and correctly handles NULL sale_price.
Return the following columns:
- category
- product_id
- list_price
- sale_price
- discount_amount
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| list_price | DECIMAL(10,2) | NOT NULL |
| sale_price | DECIMAL(10,2) |
Sample data:
| category | list_price | product_id | sale_price |
|---|---|---|---|
| Phones | 799 | 101 | 699 |
| Phones | 999 | 102 | 749 |
| Phones | 999 | 103 | 749 |
| Laptops | 1299 | 201 | 1099 |
| Laptops | 1399 | 202 | 1199 |
| TVs | 899 | 301 | NULL |
Expected output
One row per category, containing the product with the maximum absolute discount; ties on discount are broken by choosing the smallest product_id. The discount_amount is list_price - COALESCE(sale_price, list_price), clamped at 0.
| category | discount_amount | list_price | product_id | sale_price |
|---|---|---|---|---|
| Phones | 250 | 999 | 102 | 749 |
| Laptops | 200 | 1299 | 201 | 1099 |
| TVs | 0 | 899 | 301 | NULL |
Hints
- Compute discount_amount with a CASE expression to clamp negative discounts to 0.
- Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY discount_amount DESC, product_id ASC) and filter to rn = 1.
Max-discount product per category without window functions
Using the same products catalog, for each category return exactly one product: the one with the largest absolute discount. If multiple products in the same category have the same discount, return the one with the smallest product_id.
Absolute discount is defined as: discount_amount = MAX(list_price - COALESCE(sale_price, list_price), 0)
Write a single SQL query that:
- Works even if sale_price is NULL.
- Assumes sale_price is never below 0.
- Does NOT use any window functions (e.g., ROW_NUMBER, RANK, etc.).
- Correctly handles ties by discount_amount via the smallest product_id.
- Also briefly (in a SQL comment) explain how your query avoids floating point precision issues and handles NULL sale_price.
Return the following columns:
- category
- product_id
- list_price
- sale_price
- discount_amount
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| list_price | DECIMAL(10,2) | NOT NULL |
| sale_price | DECIMAL(10,2) |
Sample data:
| category | list_price | product_id | sale_price |
|---|---|---|---|
| Phones | 799 | 101 | 699 |
| Phones | 999 | 102 | 749 |
| Phones | 999 | 103 | 749 |
| Laptops | 1299 | 201 | 1099 |
| Laptops | 1399 | 202 | 1199 |
| TVs | 899 | 301 | NULL |
Expected output
Same as in Question 1: one row per category, with the product that has the maximum absolute discount; ties on discount are resolved by choosing the smallest product_id.
| category | discount_amount | list_price | product_id | sale_price |
|---|---|---|---|---|
| Phones | 250 | 999 | 102 | 749 |
| Laptops | 200 | 1299 | 201 | 1099 |
| TVs | 0 | 899 | 301 | NULL |
Hints
- First compute discount_amount per product in a subquery or CTE so you can reuse it.
- Join to a per-category MAX(discount_amount) result, then among those rows pick MIN(product_id) per category to break ties.