Write SQL for lowest price with ratings
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You have two tables.
Schema:
- products(product_id INT PRIMARY KEY, product_name TEXT, category TEXT)
- purchase(purchase_id INT PRIMARY KEY, product_id INT, price DECIMAL(10,2), stars INT NULL)
Goal: For every category present in products, return the lowest price among purchases for products in that category that have at least one rating (stars) > 4. If a category has no qualifying purchase (no rows with stars > 4), output 0 for its price. Null stars do not qualify. Return columns: category, price. One row per category. Deterministic sorting by category ASC.
Sample data (ASCII):
products
product_id | product_name | category
1 | iPhone 14 | Apple
2 | MacBook Air | Apple
3 | Banana Phone | Banana
4 | Pear X | Pear
5 | Orange Z | Orange
purchase
purchase_id | product_id | price | stars
10 | 1 | 999.00 | 5
11 | 1 | 1099.00| 3
12 | 2 | 1299.00| 4
13 | 3 | 49.00 | 5
14 | 3 | 39.00 | 2
15 | 4 | 199.00 | 4
16 | 4 | 179.00 | NULL
17 | 5 | 299.00 | NULL
Expected output on the sample:
category | price
Apple | 999.00
Banana | 49.00
Orange | 0
Pear | 0
Write a single SQL query (PostgreSQL or MySQL compatible) that produces the required output on all inputs, handling: categories with no purchases at all; multiple qualifying purchases per category; and ensuring numeric 0 (not NULL) when no stars > 4 exist.
Overview: This question evaluates SQL data-manipulation skills including aggregation, conditional filtering, NULL handling, grouping, and deterministic ordering, and sits in the Data Manipulation (SQL/Python) domain with an emphasis on practical application of query writing rather than purely conceptual design.
You are given two tables: products and purchase.
Return one row for every distinct category present in products. For each category, find the lowest purchase price among purchases for products in that category where the purchase has a rating (stars) strictly greater than 4. NULL stars do not qualify.
If a category has no qualifying purchase (i.e., no purchase rows with stars > 4 for any product in the category, including categories with no purchases at all), output a numeric 0 for the price.
Return columns:
- category
- price
Order results deterministically by category ascending.
Tables
products(product_id INT, product_name VARCHAR(100), category VARCHAR(50))
purchase(purchase_id INT, product_id INT, price DECIMAL(10,2), stars INT)
Hints
- Start from products to ensure every category appears in the output.
- Use a LEFT JOIN and conditional aggregation (CASE inside MIN) to consider only stars > 4, then COALESCE to output 0 when none qualify.