Very well done! Often, you want to create reports where you count the number of objects in two different groups, based on your classification. Look at the following query:
SELECT
customer_id,
COUNT(CASE
WHEN shipped_date IS NOT NULL
THEN order_id
END) AS orders_shipped,
COUNT(CASE
WHEN shipped_date IS NULL
THEN order_id
END) AS orders_pending
FROM orders
GROUP BY customer_id;
The report above shows each customer_id alongside the number of orders already shipped to that customer and the number of orders that have not yet been shipped. Note that we use COUNT() with CASE WHEN twice to count the number of objects in the two different groups. Both metrics, however, refer to the same business object (the same customer).
For each category, show the number of products in stock (i.e., products where units_in_stock > 0) and the number of products not in stock. The report should contain three columns:
category_nameproducts_in_stockproducts_not_in_stockUse the CASE WHEN construction to check if a given product has units_in_stock > 0.
