'Meta Recruiting SQL -- Join Part 1

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 David Aldridge

I realized that SqlLite was the issue here and casting to numeric helped reach the final result with the query in the solution below.

BACKGROUND:

The following schema is a subset of a relational database of a grocery store chain. This chain sells many products of different product classes to its customers across its different stores. It also conducts many different promotion campaigns.

The relationship between the four tables we want to analyze is depicted below:

enter image description here

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.

enter image description here

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;

enter image description here

sqlsqlitemeta


Solution 1:[1]

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;

enter image description here

Solution 2:[2]

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.

Solution 3:[3]

select   avg(case when is_low_fat_flg=1 and is_recyclable_flg=1 then 1 else 0 end)*100  as pct_low_fat_and_recyclable
from products

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 phtaedrus
Solution 2
Solution 3 user2662006

Related Questions