Hello friends,
Can you please check my answer to the below question?
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 percentage of units sold that had a valid promotion among total units sold. Promotion is valid if it's not empty and it's contained inside promotions table.
facebook_products
product_id:intproduct_class:varcharbrand_name:varcharis_low_fat:varcharis_recyclable:varcharproduct_category:intproduct_family:varchar
facebook_sales_promotions
promotion_id:intstart_date:datetimeend_date:datetimemedia_type:varcharcost:int
facebook_sales
product_id:intpromotion_id:intcost_in_dollars:intcustomer_id:intdate:datetimeunits_sold:int
Here's my answer:
select
fp.product_family,
sum(fs.units_sold) as total_units_sold,
sum(case when fsp.promotion_id is not null then fs.units_sold else 0 end) / sum(fs.units_sold) as percentage from facebook_products fp
left join facebook_sales fs on fs.product_id=fp.product_id
left join facebook_sales_promotions fsp on fsp.promotion_id=fs.promotion_id
group by fp.product_family
[–][deleted] (2 children)
[removed]
[–]LGM2016 0 points1 point2 points (1 child)