MockInterview Staff asked 7 years ago you have a table where you have date, user_id, song_id and count. It shows at the end of each day how many times in her history a user has listened to a given song. So count is cumulative sum.
You have to update this on a daily basis based on a second table that records in real time when a user listens to a given song. Basically, at the end of each day, you go to this second table and pull a count of each user/song combination and then add this count to the first table that has the lifetime count.
If it is the first time a user has listened to a given song, you won’t have this pair in the lifetime table, so you have to create the pair there and then add the count of the last day.
[Source: Facebook Data Science Interview; Glassdoor]
Bob Vance answered 5 years ago with latest_counts as (select user_id, song_id, max(count) as total_count from all_listens), with new_cumulative_counts as (select case when A.user_id is null then B.user_id else A.user_id end as user_id, case when A.song_id is null then B.song_id else A.song_id end as song_id, (case when A.total_count is null then 0 else A.total_count end) + (case when B.todays_count is null then 0 else B.todays_count end) as count, B.date from latest_counts A full outer join todays_counts B on A.user_id = B.user_id and A.song_id = B.song_id select user_id, song_id, date from latest_counts union select user_id, song_id, date from new_cumulative_counts
Amber Lin answered 4 years ago Update count Set a.count = a.count + b.count(play time) From count a Right Join (Select song, count(play time) From play Group by song having cast(playtime as date) = getdate() ) b On a.song_id = b.song