Write SQL for lowest price with ratings
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 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.