SQL query to get what percentage of product categories have never been sold

Question

I have the below 4 tables with relationship

# sales                                # products
        +------------------+---------+         +---------------------+---------+
        | product_id       | INTEGER |>--------| product_id          | INTEGER |
        | store_id         | INTEGER |    +---<| product_class_id    | INTEGER |
        | customer_id      | INTEGER |    |    | brand_name          | VARCHAR |
   +---<| promotion_id     | INTEGER |    |    | product_name        | VARCHAR |
   |    | store_sales      | DECIMAL |    |    | is_low_fat_flg      | TINYINT |
   |    | store_cost       | DECIMAL |    |    | is_recyclable_flg   | TINYINT |
   |    | units_sold       | DECIMAL |    |    | gross_weight        | DECIMAL |
   |    | transaction_date | DATE    |    |    | net_weight          | DECIMAL |
   |    +------------------+---------+    |    +---------------------+---------+
   |                                      |
   |    # promotions                      |    # product_classes
   |    +------------------+---------+    |    +---------------------+---------+
   +----| promotion_id     | INTEGER |    +----| product_class_id    | INTEGER |
        | promotion_name   | VARCHAR |         | product_subcategory | VARCHAR |
        | media_type       | VARCHAR |         | product_category    | VARCHAR |
        | cost             | DECIMAL |         | product_department  | VARCHAR |
        | start_date       | DATE    |         | product_family      | VARCHAR |
        | end_date         | DATE    |         +---------------------+---------+
        +------------------+---------+

I want to get what percentage of product categories have never been sold.

I have tried this :

select 100.0* sum(case when spid is null OR 0 then 1.0 end)/count(ppid) as pct from 
(
Select distinct product_category, s.product_id as spid, p.product_id as ppid from products p 
LEFT JOIN Sales s on s.product_id = p.product_id
  LEFT JOIN product_classes pc ON pc.product_class_id = p.product_class_id
 LEFT JOIN Promotions pr on pr.promotion_id=s.promotion_id
)t

which gives 20.7

Expected answer is 13.8888

Answer 1

I think you can get it by dividing the number of product classes by the number of classes which don't have any sales.

You don't need to join promotions table at all.

SELECT (
           SELECT COUNT(*)
           FROM product_classes c
           WHERE EXISTS(
                         SELECT 1
                         FROM products p,
                              sales s
                         WHERE p.product_id = s.product_id
                           AND p.product_class_id = c.product_class_id
                         LIMIT 1
                     )
       ) / (SELECT COUNT(*) FROM product_classes c) * 100;