Linkedin Data Scientist Phone Interview

同问，现在秋招就开始了吗

感谢楼主分享！请问楼主用R怎么写啊？

现在的Phone interview都要写代码的吗……直接把代码读给对方？

SQL management studio

with A as (
select date, member_id, count(distinct channel) as dist_channel
from table1
group by date, member_id),

B as (
select a.date, a.member_id, t.channel, t.spend, a.dist_channel from
(select * from table1)  as t
left join
(select * from A) as a
on t.date = a.date and t.member_id = a.member_id),

C as (
select date, case when dist_channel = 2 then 'both' else channel end as channel, spend
from B )

select date, channel, sum(spend) as total_spend, count(*) as total_member from C
group by date, channel
order by date, channel

Ryohka 发表于 2018-7-15 09:48
现在的Phone interview都要写代码的吗……直接把代码读给对方？

有网页codepad，一边打电话一边写代码

hyper8866 发表于 2018-7-16 12:32
有网页codepad，一边打电话一边写代码

哦这样还好……
我前一阵接了个电面让我口述代码我直接懵了……

先用self join找出每天both的users：
with both_users as
(select *
. 1point 3 acresfrom table t1 join table t2
on t1.member_id= t2.member_id
and t1.date= t2.date
and t1.channel='mobile' and t2.channel='desktop'),

both_sum as
(select date, 'both' as channel,  sum(t1.spend+ t2.spend) as total_spend, count(distinct t1.member_id) as total_members
from both_users
group by date)

select date, channel,  sum(spend) as total_spend, sum(distinct member_id) as total_members
from table
where (date, member_id) not in
(select t1.date, t1.member_id from both_users)
group by date
union
select *
from both_sum

不确定对不对，求大神指点0.0

Select
        date,
        case when b.channel_cnt>1 then ‘both’ else channel end as channel,
        sum(spend) as total_spend,
        count(distinct a.member_id) as total_members
From spending a
Join        
(Select
        memer_id,
        date,
        count(distinct channel) as channel_cnt
From spending
Group by 1,2) b
On a.date = b.date and a.member_id = b.member_id
Group by 1,2

SELECT date, channel, COALESCE(SUM(spend),0) AS total_spend, COUNT(DISTINCT member_id) AS total_members
FROM
(
SELECT s1.member_id,
       s1.date,
       CASE WHEN s2.channel IS NULL THEN s1.channel
            ELSE 'both' END AS channel,
       s1.spend
  FROM spending s1
  LEFT JOIN spending s2
    ON s1.member_id = s2.member_id
   AND s1.date = s2.date
   AND s1.channel != s2.channel
) a
GROUP BY 1, 2
ORDER BY 1;

有一个小疑问，output里面，1/2/2018的‘both'情况没有记录，不知道怎么实现呈现这条为0的结果。

要求的output里貌似需要每天都输出3个channel，即使没有数据。如果这是硬性要求的话，应该再加一个left join列出所有日期和channel的组合：

SELECT d.date, c.channel, coalesce(total_spend, 0) as total_spend, coalesce(total_members, 0) as total_members
FROM
(select distinct date from spending) d
cross join
(select 'both' as channel union select 'mobile' union select 'desktop') c
left join
(SELECT a.date, CASE WHEN chn_count = 2 then 'both' ELSE channel END as channel,
       sum(spend) as total_spend, count(distinct a.member_id) as total_members
FROM spending a
inner join
(SELECT member_id, date, count(distinct channel) as chn_count
FROM spending
group by member_id, date) b
using (member_id, date)
group by date, CASE WHEN chn_count = 2 then 'both' ELSE channel END) data
using (date, channel)

抛砖引玉，求指点