[脸书数据科学面经] Facebook Data Scientist面试经验

好奇lz说的etl思想是啥呀

cimynfine 发表于 2019-10-17 01:38
好奇lz说的etl思想是啥呀

就是pipeline思路。你可以跑一天的数据。然后，每天都在前一天的基础上update用户status.

.--
楼主，请问这个题目这样写思路对吗？
select case when a.userid is null then b.userid else a.userid end as userid,  
case when a.userid is null and b.userid is not null then ‘churn’
when a.userid is not null and b.userid is null then ‘new’
when a.userid is not null and b.userid is not null then ‘retained’
when c.userid is not null and b.userid is null then ‘resurrect’ end as state，
case when a.ds is not null then a.ds else when b.ds is not null then b.ds+1 end as ds
from dau a full join dau b on a.userid = b.userid and a.ds = b.ds+1
join dau c on a.userid = c.userid and a.ds > c.ds+2;

谢谢

with initial_status as (
select userid, date, "new" as status from T where date in (select min(date) from T)
),
recursive cte as (
select userid, date, status from initial_status
union all
select IFNULL(a.userid, b.userid) as user_id,
ifnull(b.date, dateadd(day, 1, a.date) as date,
case when a.status in ('new', 'retained', 'resurrect') and b.user_id is not null then 'retained'
when a.status in ('new', 'retained', 'resurrect')  and b.user_id is null then 'churn'
when a.status = 'churn' and b.user_id is not null then 'resurrect'
when a.status = 'churn' and b.user_id is null then 'churn'
when a.userid is null and b.user_id is not null then 'new'
end as status
from initial_status a full outer join T b on DATEDIFF(b.date, a.date) = 1
)
select * from cte;

不知道对不对，请指教

补充内容 (2020-10-30 10:27):
from initial_status a full outer join T b on DATEDIFF(b.date, a.date) = 1
这一行应该是from cte a full outer join T b on DATEDIFF(b.date, a.date) = 1

跪求楼主背景。。这个星期内推google ds/pa直接被简历拒了。。

好奇宝宝 发表于 2019-10-11 19:09
回复一下给我大米同学的最优解法套路：我觉得应该是用今天的DAU的去update昨天的growth_accounting table.  ...

楼主，考古到这里，很好奇如何用ETL的思路，无奈加不了大米，看不到你的最优解。有什么方式可以和你请教下吗？