Quick Overview

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.

Select max-discount product per category

Company: TikTok

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Online Assessment

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.

Overview: 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.

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

Tables

products(product_id INT, category VARCHAR(50), list_price DECIMAL(10,2), sale_price DECIMAL(10,2))

Hints

  1. Compute discount_amount with a CASE expression to clamp negative discounts to 0.
  2. 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

Tables

products(product_id INT, category VARCHAR(50), list_price DECIMAL(10,2), sale_price DECIMAL(10,2))

Hints

  1. First compute discount_amount per product in a subquery or CTE so you can reuse it.
  2. Join to a per-category MAX(discount_amount) result, then among those rows pick MIN(product_id) per category to break ties.

Loading coding console...