WITH categorySummary AS (
SELECT products.Category as "Product Category"
, products.Name as "Product Name"
, products.Launch::date as "Launch Date"
, COUNT(DISTINCT orders.id) as "Total Orders"
, sum(orders.total) as "Total Revenue ($)"
, COUNT(DISTINCT orders.customerId) as "Total Customers"
FROM products
JOIN order_items
ON products.SKU = order_items.SKU
JOIN orders
ON order_items.orderId = orders.id
GROUP BY 1, 2, 3
), categorySummaryResults AS (
SELECT *
, row_number() OVER (PARTITION BY "Product Category" ORDER BY "Launch Date" ASC) as "Launch Order"
FROM categorySummary
)
SELECT *
FROM categorySummaryResults
WHERE "Launch Order" = 1