apple DS 店面

求大米
求大米
求大米。 大家面试都不容易求大米
*page_events*

* user_id
* device_type
* page_id
* visit_ts

*playback_events*

* user_id
* device_type
* video_id
* play_ts


*Questions:*

* Number of users who are cross-platform viewers (have pla

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

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

where true
and rank = 1)

select count(distinct user_id)  
from t2
where play_ts between '2021-04-01' and '2021-04-30'  --fisr time play in apirl 2021

感谢分享

感谢楼主分享！

求大米啊

感谢楼主分享！

1. Q1: Number of users who are cross-platform viewers (have plays on at least two types of platforms)
   
2. 
   
3. SELECT COUNT(DISTINCT user_id) AS Cross_Platform_Viewers
   
4. FROM (
   
5.   SELECT user_id
   
6.   FROM playback_events
   . From 1point 3acres bbs
7.   GROUP BY user_id
   
8.   HAVING COUNT(DISTINCT device_type) >= 2
   
9. ) AS subquery;
   
10. 
    
11. 
    
12. Q2: Number of users who visited the pages but didn’t play
    
13. 
    
14. select count(distinct p.user_id) as users_only_visit
    
15. from page_events p
    
16. left join playback_events pb
    
17. on p.user_id = pb.user_id
    
18. where pb.play_ts is NULL;
    
19. 
    
20. 
    
21. Q3: Number of users whose first play is in April 2021
    
22. 
    
23. SELECT COUNT(DISTINCT user_id) AS cnt
    
24. FROM (
    
25.   SELECT pb.user_id, MIN(pb.play_ts) AS first_play
    
26.   FROM page_events p
    
27.   JOIN playback_events pb
    
28.   ON p.user_id = pb.user_id
    
29.   GROUP BY pb.user_id
    
30. ) t1
    
31. WHERE YEAR(t1.first_play) = 2021 AND MONTH(t1.first_play) = 4;
    

复制代码