第五课：BI developer 和 Data Scientist
给大家看一道笔试题吧，多伦多温哥华的行情
能做出SQL解法的，可以进入初级BI开发人员的面试，起薪7万加刀
能做出Python和R的解法，可以进入初级Data Scientist的面试，起薪10万加刀
如果都能做出来，可以考虑应聘Data Engineer Level I，起薪11万加刀
Problem: Member can make purchase via either mobile or desktop platform. Using the following data table to determine the total number of member and revenue for mobile-only, desktop_only and mobile_desktop.
The input spending table is
member_id date channel spend
1001 1/1/2018 mobile 100
1001 1/1/2018 desktop 100
1002 1/1/2018 mobile 100
1002 1/2/2018 mobile 100
1003 1/1/2018 desktop 100
1003 1/2/2018 desktop 100
The output data is
date channel total_spend total_members
1/1/2018 desktop 100 1
1/1/2018 mobile 100 1
1/1/2018 both 200 1
1/2/2018 desktop 100 1
1/2/2018 mobile 100 1
1/2/2018 both 0 0
SQL solution:
CREATE VIEW member_spend AS
SELECT
date,
member_id,
SUM(CASE WHEN channel == ‘mobile’ THEN spend ELSE 0 END) AS mobile_spend,
SUM(CASE WHEN channel == ‘desktop’ THEN spend ELSE 0 END) AS desktop_spend
FROM
spending
GROUP BY date, member_id;
SELECT
date,
CASE WHEN mobile_spend > 0 AND desktop_spend = 0 THEN ‘mobile’
WHEN mobile_spend = 0 AND desktop_spend > 0 THEN ‘desktop’
WHEN mobile_spend > 0 AND desktop_spend > 0 THEN ‘both’
END AS channel,
SUM(mobile_spend + desktop_spend) AS total_spend,
COUNT(*) AS total_members
FROM member_spend
GROUP BY
date,
CASE WHEN mobile_spend > 0 AND desktop_spend = 0 THEN ‘mobile’
WHEN mobile_spend = 0 AND desktop_spend > 0 THEN ‘desktop’
WHEN mobile_spend > 0 AND desktop_spend > 0 THEN ‘both’
END;
Python pandas:
spending[‘mobile_spend’] = spending[spending.channel == ‘mobile’].spend
spending[‘desktop_spend’] = spending[spending.channel == ‘desktop’].spend
member_spend = spending.group_by([‘date’, ‘member_id’]).sum([‘mobile_spend’, ‘desktop_spend’]).to_frame([‘mobile_spend’, ‘desktop_spend’].reset_index()
SQL
SELECT
date，
CASE WHEN mobile_spend > 0 AND desktop_spend = 0 THEN ‘mobile’
WHEN mobile_spend = 0 AND desktop_spend > 0 THEN ‘desktop’
WHEN mobile_spend > 0 AND desktop_spend > 0 THEN ‘both’
END AS channel,
SUM(mobile_spend + desktop_spend) AS total_spend,
COUNT(*) AS total_members
FROM member_spend
GROUP BY
date,
CASE WHEN mobile_spend > 0 AND desktop_spend = 0 THEN ‘mobile’
WHEN mobile_spend = 0 AND desktop_spend > 0 THEN ‘desktop’
WHEN mobile_spend > 0 AND desktop_spend > 0 THEN ‘both’
END;
Python pandas
member_spend[member_spend.mobile_spend>0 & member_spend.desktop_spend==0], ‘channel’] = ‘mobile’
member_spend[member_spend.mobile_spend==0 & member_spend.desktop_spend>0], ‘channel’] = ‘desktop’
member_spend[member_spend.mobile_spend>0 & member_spend.desktop_spend>0], ‘channel’] = ‘both’
tot_members = member_spend.groupby([‘date’, ‘channel’]).size().to_frame(‘tot_members’).reset_index()
tot_spend = member_spend.groupby([‘date’, ‘channel’].agg({‘mobile_spend’:sum, ‘desktop_spend’:sum}).to_frame([‘mobile_spend’, ‘desktop_spend’])
tot_spend[‘tot_spend’] = tot_spend[‘mobile_spend’] + tot_spend[‘desktop_spend’]
output = tot_members.concat(tot_spend[‘tot_spend’])
R dplyr:
output <- spending %>%
group_by(member_id, date)%>%
summarise(mobile_spend = sum(spend[channel == 'mobile']),
desktop_spend = sum(spend[channel == 'desktop'])) %>%
mutate(channel = ifelse((mobile_spend>0 & desktop_spend>0), 'both',
ifelse(mobile_spend==0, 'desktop', 'mobile'))) %>%
group_by(date, channel)%>%
summarise(total_spend = mobile_spend + desktop_spend,
total_member = n())
