Write SQL for lowest price with ratings

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.

Lowest qualifying purchase price per category (stars > 4), else 0

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.

Table products

column	type	constraints
product_id	INT	PRIMARY KEY, NOT NULL
product_name	VARCHAR(100)	NOT NULL
category	VARCHAR(50)	NOT NULL

Sample data:

category	product_id	product_name
Apple	1	iPhone 14
Apple	2	MacBook Air
Banana	3	Banana Phone
Pear	4	Pear X
Orange	5	Orange Z

Table purchase

column	type	constraints
purchase_id	INT	PRIMARY KEY, NOT NULL
product_id	INT	NOT NULL
price	DECIMAL(10,2)	NOT NULL
stars	INT

Sample data:

price	product_id	purchase_id	stars
999	1	10	5
1099	1	11	3
1299	2	12	4
49	3	13	5
39	3	14	2
199	4	15	4
179	4	16	NULL
299	5	17	NULL

Expected output

One row per category in products. price is the minimum purchase.price among purchases with stars > 4 for that category; otherwise 0. Sorted by category ASC.

category	price
Apple	999
Banana	49
Orange	0
Pear	0

Hints

1. Start from products to ensure every category appears in the output.
2. Use a LEFT JOIN and conditional aggregation (CASE inside MIN) to consider only stars > 4, then COALESCE to output 0 when none qualify.