MySQL interview questions

Table orders(id, customer_id, amount). amount can be NULL when the price has not been settled yet. For every customer that appears in the table return the number of orders they placed and the sum of their known amounts. An order with a NULL amount

Table reviews(id, product_id, rating) where rating is an integer from 1 to 5. Return the products that have at least 3 reviews and an average rating of 4 or higher. Columns: product_id, review_count, avg_rating (the average rounded to 2 decimal places).

What is the difference between WHERE and HAVING in a query that uses GROUP BY?

MySQL runs with ONLY_FULL_GROUP_BY enabled by default (the default since MySQL 5.7). What does this mode require of a query that uses GROUP BY, and what error do you get if you violate it?

Tables customers(id, name) and orders(id, customer_id, amount). Using a CTE for the per-customer totals, return the customers whose total order amount is strictly above the average of the per-customer totals. Customers with no orders take no part in the average and are never

What is a Common Table Expression (WITH ... AS (...)), and what readability problem does it solve compared to nesting subqueries?