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.
Quick Answer: 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.