既然你在这个问题上提供了你想要的结果，我就用示例数据重新创建了你的案例。
为了达到最终的结果，我已经完成了你提到的所有步骤：数一数只喜欢一种饮料的人，数一数只喜欢一种食物的人，然后数一数喜欢这种饮料和食物的人。
下面是示例数据和我采取的步骤，

# sample data for food

WITH food AS(
SELECT 1 AS user_ids, "hamburguer" AS foods UNION ALL
SELECT 1 AS user_ids, "hamburguer" AS foods UNION ALL
SELECT 2 AS user_ids, "hamburguer" AS foods UNION ALL
SELECT 2 AS user_ids, "pizza" AS foods UNION ALL
SELECT 2 AS user_ids, "pizza" AS foods UNION ALL
SELECT 3 AS user_ids, "chicken" AS foods 
),

# sample data for drink

drink AS(
SELECT 1 AS user_ids, "coke" AS drinks UNION ALL
SELECT 2 AS user_ids, "coke" AS drinks UNION ALL
SELECT 2 AS user_ids, "coke" AS drinks UNION ALL
SELECT 4 AS user_ids, "coke" AS drinks UNION ALL
SELECT 5 AS user_ids, "coke" AS drinks 
),

# count how many people like each type of food

count_foods AS (
SELECT COUNT(foods) AS count_foods, foods FROM food GROUP BY foods
),

# count how many people like each type of drink

count_drinks AS(
SELECT COUNT(drinks) AS count_drinks, drinks FROM drink GROUP BY drinks
),

# making all the possible combinations between foods and drinks with CROSS JOIN

food_drink_only AS (
SELECT foods, drinks, count_foods, count_drinks FROM count_foods a CROSS JOIN count_drinks b
),

# people who like one food and a drink, for ex.: user_ids = 1 likes hamburguer and coke

like_both AS (
SELECT  COUNT(user_ids) AS count_both, foods, drinks FROM (SELECT DISTINCT user_ids, foods FROM food) 
INNER JOIN (SELECT DISTINCT user_ids, drinks FROM drink) USING(user_ids) GROUP BY  2,3
)

# Using left join with foods and drinks as primary keys because all the combinations (fodds, drinks) came from the CROSS JOIN

# and are in the left table

SELECT a.foods,a.drinks,a.count_foods,a.count_drinks, b.count_both FROM food_drink_only a
LEFT JOIN like_both b ON a.foods = b.foods AND a.drinks=b.drinks

以及输出，

Row foods   drinks  count_foods count_drinks    count_both
1   hamburguer  coke    3   5   2
2   pizza   coke    2   5   1
3   chicken coke    1   5   null

首先，请注意交叉连接为我们提供了 foods 以及 drinks . 因此，在创建最终输出时使用左连接。然后，注意 like_both 表中有2个用户喜欢可口可乐和汉堡，1个用户喜欢可口可乐和比萨饼，0个用户喜欢可口可乐和鸡肉。因此，田地 foods 以及 drinks 用作将此表与连接的主键 food_drink_only table。因此，数据被放置在正确的食物和饮料组合中。