Find Lowest Prices for Highly Rated Categories

You are given two tables containing product catalog information and purchase records.

Tables

products

Column	Type	Description
id	INT	Primary key for the product.
category	VARCHAR	Product category, such as apple, orange, or banana.
product_name	VARCHAR	Name of the product.

purchases

Column	Type	Description
id	INT	Primary key for the purchase record.
product_id	INT	Foreign key referencing products.id.
price	DECIMAL(10,2)	Purchase price of the product.
stars	DECIMAL(2,1)	Customer rating for the purchase.

Task

For each product category in products, return the lowest purchase price in that category only if the category has at least one purchase with stars > 4.

If a category does not have any purchase with stars > 4, return 0 as the price for that category.

Assumptions

• Include every category that appears in products.
• A category may contain multiple products and each product may have multiple purchase records.
• If a category has at least one purchase with stars > 4, compute the minimum price across purchases in that category.
• If a category has no purchases, return 0.
• There is no time filter for this question, so timezone handling is not applicable.

Required Output

Return one row per category.

Column	Type	Description
category	VARCHAR	Product category.
price	DECIMAL(10,2) or INT	Lowest price for qualifying categories; otherwise 0.

Example output format:

category	price
apple	0

Find Lowest Prices for Highly Rated Categories

Table products

column	type	constraints
id	INT	PRIMARY KEY
category	VARCHAR
product_name	VARCHAR

Sample data:

category	id	product_name
apple	1	Apple A
apple	2	Apple B
orange	3	Orange O1
orange	4	Orange O2
banana	5	Banana B1

Table purchases

column	type	constraints
id	INT	PRIMARY KEY
product_id	INT	FOREIGN KEY REFERENCES products(id)
price	DECIMAL(10,2)
stars	DECIMAL(2,1)

Sample data:

id	price	product_id	stars
101	3.5	1	3
102	2.75	2	4
103	5	3	4.5
104	4	3	3.5
105	6	4	5

Expected output

One row per distinct category from products. If the category has at least one purchase with stars > 4, price is the minimum of all purchase prices in that category; otherwise price is 0. Categories with no purchases also return 0.

category	price
apple	0
banana	0
orange	4

Hints

1. Start from the list of categories in products (SELECT DISTINCT).
2. LEFT JOIN purchases to include categories with no purchases.
3. Use aggregation per category: MIN(price) for the overall lowest price, and a flag like MAX(CASE WHEN stars > 4 THEN 1 ELSE 0 END) to detect high-rated purchases.
4. Return 0 when the high-rated flag is not present (or when there are no purchases).