I have done my best to neatly format the meta recruiting question below. My current query that I'm trying to ascertain the ratio is this. Returning correct 2/3 values but the ratio is returning as all 1's. Any help on where I'm going wrong?

Update: After much help from

PROMPT:

• The CMO is interested in understanding how the sales of different
• product families are affected by promotional campaigns.
• To do so, for each of the available product families,
• show the total number of units sold,
• as well as the ratio of units sold that had a valid promotion
• to units sold without a promotion, -- ordered by increasing order of total units sold.

Solution:

SELECT product_family,
SUM(units_sold) as total_units_sold,
CAST(SUM(CASE WHEN s.promotion_id IS NOT 0 then units_sold * 1.0 ELSE 0 END) AS NUMERIC) / 
CAST(SUM(CASE WHEN s.promotion_id = 0 THEN units_sold * 1.0 ELSE 0 END) as NUMERIC) as ratio_units_sold_with_promo_to_sold_without_promo
FROM products p
JOIN product_classes c ON c.product_class_id = p.product_class_id
JOIN sales s ON s.product_id = p.product_id
GROUP BY product_family
ORDER BY total_units_sold ASC;

CodePudding user response：

This feels like what you're looking for (I added an extra column for clarity):

SELECT product_family,
      SUM(units_sold) as total_units_sold,
      SUM(CASE WHEN s.promotion_id > 0 then units_sold ELSE 0 END) as sold_when_promoted,
      SUM(CASE WHEN s.promotion_id > 0 then units_sold ELSE 0 END)::numeric /
        SUM(units_sold) AS ratio_units_sold_with_promo_to_sold_without_promo
FROM products p
JOIN product_classes c ON c.product_class_id = p.product_class_id
JOIN sales s ON s.product_id = p.product_id
GROUP BY product_family;

... or using a filter clause ...

SELECT product_family,
      SUM(units_sold) as total_units_sold,
      SUM(units_sold) filter(where s.promotion_id > 0) as sold_when_promoted,
      SUM(units_sold) filter(where s.promotion_id > 0)::numeric /
        SUM(units_sold) AS ratio_units_sold_with_promo_to_sold_without_promo
FROM products p
JOIN product_classes c ON c.product_class_id = p.product_class_id
JOIN sales s ON s.product_id = p.product_id
GROUP BY product_family;

I don't think you need to guard against division by zero, but if you had a sum of sales of zer for a product you'd need to handle that.

Edit: I think you don't need to reference the promotions table, actually.

CodePudding user response：

SELECT product_family,
SUM(units_sold) as total_units_sold,
CAST(SUM(CASE WHEN s.promotion_id IS NOT 0 then units_sold * 1.0 ELSE 0 END) AS NUMERIC) / 
CAST(SUM(CASE WHEN s.promotion_id = 0 THEN units_sold * 1.0 ELSE 0 END) as NUMERIC) as ratio_units_sold_with_promo_to_sold_without_promo
FROM products p
JOIN product_classes c ON c.product_class_id = p.product_class_id
JOIN sales s ON s.product_id = p.product_id
GROUP BY product_family
ORDER BY total_units_sold ASC;