In this video we will discuss a Spotify case study. This case study has 5 questions and with each question difficulty level will go up.
Video Content:
00:00 Understand the problem statement
01:52 Daily active users
03:31 Weekly active users
04:39 same day install and purchase
09:10 country wise paid users
15:11 Last question and assignment
Most Asked Join Based Interview Question:
https://youtu.be/xR87ctOgpAE
Top 10 SQL interview Questions:
https://youtu.be/Iv9qBz-cyVA
Interview Question based on FULL OUTER JOIN:
https://youtu.be/KQfWd6V3IB8
Playlist to master SQL :
https://youtube.com/playlist?list=PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb
Rand, Dense_Rank and Row_Number:
https://youtu.be/xMWEVFC4FOk
Script to create and insert data:
CREATE table activity
(
user_id varchar(20),
event_name varchar(20),
event_date date,
country varchar(20)
);
delete from activity;
insert into activity values (1,'app-installed','2022-01-01','India')
,(1,'app-purchase','2022-01-02','India')
,(2,'app-installed','2022-01-01','USA')
,(3,'app-installed','2022-01-01','USA')
,(3,'app-purchase','2022-01-03','USA')
,(4,'app-installed','2022-01-03','India')
,(4,'app-purchase','2022-01-03','India')
,(5,'app-installed','2022-01-03','SL')
,(5,'app-purchase','2022-01-03','SL')
,(6,'app-installed','2022-01-04','Pakistan')
,(6,'app-purchase','2022-01-04','Pakistan');
#sql #dataanalyst #spotify #casestudy
