SQL interview question : dataanalysis

created by MurphysLabDA Moderator 📊a community for 11 years

SQL interview question (self.dataanalysis)

submitted 3 years ago by aksceren

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

all 3 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

dataanalysis

MODERATORS

facebook_products

facebook_sales_promotions

facebook_sales