– In sql_store database, create a product frequency report
– with the following columns: product_id, product name, # orders, frequency
– Populate freqency column with “Once” if product was ordered only once.
– Populate frequency column with “Many times” if product was ordered more than once.
– Populate frequency column with “Never” if product was never ordered. This was
– not specified in the requirements but I thought it would be useful.
USE sql_store;
SELECT
p.product_id,
p.name as product,
(SELECT COUNT(*)
FROM order_items
WHERE product_id = p.product_id
) AS order_count,
IF (
(SELECT order_count) = 0, “Never”,
IF(
(SELECT order_count) = 1, “Once”,
“Many times”)
) AS frequency
FROM
products p
