Facebook SQL questions - DE practice
Anonymous User
1373
 The relationship between the four tables we want to analyze is depicted below:
 
        # 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    |         +---------------------+---------+
        +------------------+---------+

How to get what percentage of product categories have never been sold ?

Answer required is

+-----------------------------------+
| pct_product_categories_never_sold |
+-----------------------------------+
| 13.8888888888888889 |
+-----------------------------------+


HINTS
Can we use the percentage of product categories that did sell to help us compute this?

I tried this SQL but its wrong :

select sum(case when sid is NULL then 1.0 else 0 end)/ (SELECT COUNT(*) FROM product_classes c)*100.0 as pct
from
(
select distinct p.product_class_id,p.product_id as pid,s.product_id as sid from products p LEFT JOIN sales s on p.product_id=s.product_id
  )t
Comments (4)