Linkedin Data Scientist Phone Interview

Problem Statement:  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.
T

您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

案吧...




补充内容 (2018-7-12 12:37):
在code pad写了这么多R后。。面试官跟我说 她不会用R...能不能写一个SQL版本TAT 我于是直接一个剪切把R code都删了。现在想想 应该留着....给有缘人看@@

MySQL 答案
select date,chn,count(spend) as total_spend, count(distinct member_id)
from
(
select member_id,date,spend,
case when cnt=1 then channel
     else 'both' end as chn
from
(
select member_id,date, count(distinct channel) as cnt
from spending
group by member_id, date) as s1
inner join spending as s2
using(member_id,date)
) as t
group by date, chn
order by date

SELECT s1.date AS date,
(CAESE WHEN COUNT(DISTINCT s2.channel) = 1 THEN s2.channel ELSE "both" END) AS channel,
(SUM(s2.spend)) AS total_spend,
(COUNT(distinct member_id) AS total_members
FROM Spending s1 LEFT JOIN Spending s2
ON s1.member_id = s2.member_id AND s1.date = s2.date
GROUP BY date
ORDER BY date;

SELECT s1.date AS date,
(CAESE WHEN COUNT(DISTINCT s2.channel) = 1 THEN s2.channel ELSE "both" END) AS channel,
(SUM(s2.spend)) AS total_spend,
(COUNT(distinct member_id) AS total_members
FROM Spending s1 LEFT JOIN Spending s2
ON s1.member_id = s2.member_id AND s1.date = s2.date
GROUP BY date
ORDER BY date;

问题有一处不清楚，假如一个member在第一天买了desktop第二天买了mobile，那么有两种可能
1.两天都算both
2.第一天是desktop第二天是Mobile。

后面一种稍微难写一点点， 下面是code。如果按第一种的话同样思路稍微改改就行。

with desktop_only as
(
        select cast(member_id as varchar) || cast(date as varchar)
        from spending
        group by member_id, date
        having sum(if(channel = 'mobile', 1, 0)) = 0
),
mobile_only as
(
        select cast(member_id as varchar) || cast(date as varchar)
        from spending
        group by member_id, date
        having sum(if(channel = 'desktop', 1, 0)) = 0
),
both as
(
        select cast(member_id as varchar) || cast(date as varchar)
        from spending
        group by member_id, date
        having sum(if(channel = 'mobile', 1, 0)) > 0
        and sum(if(channel = 'mobile', 1, 0)) > 0
)
select date, channel, sum(spend) as total_spend, count(distinct member_id) as total_members
from
(
        select *,
        case when cast(member_id as varchar) || cast(date as varchar) in (select * from desktop_only) then 'desktop'
                 when cast(member_id as varchar) || cast(date as varchar) in (select * from mobile_only) then 'mobile'
                 else 'both'
        end as channel
        from spending  
)
group by date, channel
order by date, channel



补充内容 (2018-7-12 07:47):
with里面没必要写both的subquery 多余了

L家DS秋招这么早？

抱歉第一行应该是sum(spend)

miaozuoyu 发表于 2018-7-12 08:49
L家DS秋招这么早？

不知道呀 感觉SQL写起来是真麻烦...

cixian1110 发表于 2018-7-12 07:46
问题有一处不清楚，假如一个member在第一天买了desktop第二天买了mobile，那么有两种可能
1.两天都算both
...

我觉得是第二种，我感觉核心就在于count=2是both...这要是我们公司数据库五行就出来了

哈哈替广大网友谢谢你的答案....

请问lz是自己申请的吗？L家有data scientist职位吗 只看到senior的。。