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