You work with two tables.
Table: products
| Column | Type |
|---|---|
id | INT |
product_type | VARCHAR(50) |
price | DECIMAL(10,2) |
Table: orders
| Column | Type |
|---|---|
id | INT |
customer_id | INT |
order_date | DATE |
total | DECIMAL(10,2) |
Task
WHERE and HAVING.product_type for products priced above 100.2025-01-01.Found a mistake in this question?
Report a problemA basic answer first, then the optimized one.
GROUP BY. It can use aggregates such as SUM and COUNT.FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.WHERE. Fewer rows reach the grouping step and the filter can use an index.-- WHERE: filter rows before groupingSELECT product_type, AVG(price) AS avg_priceFROM productsWHERE price > 100GROUP BY product_type; -- HAVING: filter groups after aggregationSELECT customer_id, SUM(total) AS total_spentFROM ordersGROUP BY customer_idHAVING SUM(total) > 1000;-- Covering index: the filter column first, the grouped column next, the summed column includedCREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id) INCLUDE (total); SELECT customer_id, SUM(total) AS total_spentFROM ordersWHERE order_date >= DATE '2025-01-01' -- row filter: uses the index, shrinks the inputGROUP BY customer_idHAVING SUM(total) > 1000; -- group filter: only what WHERE cannot doLog in to mark questions completed and keep your streak.
Log inSpotted a mistake in these answers?
Report a problem