Grouping rows by ID, calculate how groups satisfy various conditions

Question

I have data like below from a till system and I want to answer the following questions:

1. What % of sales containing food ordered at least as many drinks as food? Food is either “Food -> Breakfast” or “Food -> Lunch”. Orders A, B, and C are food orders and only order B satisfies the question so the answer is 33%.

2. What % of sales containing food had no drinks? Order C satisfies this, again 1 order out of the 3 containing food, so the answer is 33%.

   Order ID	Item	Category	Qty
   A	Eggs	Food -> Breakfast	1
   A	Avocado	Food -> Breakfast	2
   A	Coke	Drinks -> Cold drinks	1
   A	Flat White	Drinks -> Coffee	1
   B	Eggs	Food -> Breakfast	1
   B	Flat White	Drinks -> Coffee	1
   C	Eggs	Food -> Breakfast	1
   D	Latte	Drinks -> Coffee	1

I’m not sure how to get the data I need, I’m guessing a subquery or join is needed but can’t work out how to do it.

Answer 1

You can first calculate Food and Drinks quantity but in both question Food always needs to be present which means quantity of food should be greater than 0.

SELECT  order_id,
SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) AS food_qty,
SUM(CASE WHEN category LIKE 'Drinks%' THEN qty ELSE 0 END) AS drinks_qty
FROM test
GROUP BY order_id
HAVING SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) > 0

which gives

order_id	food_qty	drinks_qty
B	1	1
C	1	0
A	3	2

To calculate the percentages for as many drinks as food, find the count for drink_qty >= food_qty.

To calculate the percentages for food without drinks, find the count for drink_qty = 0

Both these metrics are then divided by the total number of valid orders(calculated in previous step) to calculate percentage.

Sample query

WITH all_orders AS (
SELECT  order_id,
SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) AS food_qty,
SUM(CASE WHEN category LIKE 'Drinks%' THEN qty ELSE 0 END) AS drink_qty
FROM test
GROUP BY order_id
HAVING SUM(CASE WHEN category LIKE 'Food%' THEN qty ELSE 0 END) > 0
)
SELECT 
CAST((SELECT COUNT(*) FROM all_orders WHERE drink_qty >= food_qty) * 100.0 / (SELECT COUNT(*) FROM all_orders) AS DECIMAL(5, 2)) AS pct_asmany_drinks_as_food,
CAST((SELECT COUNT(*) FROM all_orders WHERE drink_qty = 0) * 100.0 / (SELECT COUNT(*) FROM all_orders) AS DECIMAL(5, 2)) AS pct_no_drinks;

Outputs

Fiddle Demo

pct_asmany_drinks_as_food	pct_no_drinks
33.33	33.33