Data Analyst Spotify Case Study | SQL Interview Questions

[@sateeshkumard8369]

Great Job Bro...keep it up..My Approach for Last Question.
with prev_data as
(select *,
lag(event_date,1) over(partition by user_id order by event_date) as prev_event_date,
lag(event_name,1) over(partition by user_id order by event_date) as prev_event_name
from activity)
select event_date,
count(case when event_name='app-purchase' and prev_event_name='app-installed' and datediff(day,prev_event_date,event_date)=1 then user_id else null end) as user_cnt
from prev_data
group by event_date

[@ashpoghosh7645]

Question 5 Soln
with cte as
(
select user_id,event_name,event_date,
lag(event_date,1) over(partition by user_id order by event_date) as prev_date,
lag(event_name,1) over(partition by user_id order by event_date) as prev_event
from activity1
)
select event_date,
sum(case when event_date=prev_date+1 and cte.event_name='app-purchase' and cte.prev_event='app-installed'
then 1
else 0 end ) as cnt
from cte
group by event_date

[@ankitbansal6]

Thanks for posting. Looks good 😊

[@hairavyadav6579]

Nice questions sir
Last question solution
with cte as (select user_id,event_name,event_date,lag(event_date) over(partition by user_id order by event_date) as pur_date,lag(event_name)
over(partition by user_id order by event_date) as pur_event from activity)
select event_date, count( distinct case when event_name="app-purchase" and pur_event="app-installed" and datediff(event_date,pur_date)=1 then user_id else null end)
as cnt_users from cte group by event_date;

[@DeepanshuPal-f6u]

Great Video ...Last question using self join
select event_date,count(user_id1) as count from (
select a.*,
b.event_date as event_date1,
b.user_id as user_id1
from activity a left join activity b
on a.user_id=b.user_id and DATEDIFF(day,b.event_date,a.event_date)=1)a
group by event_date

[@mrshubhamthakur007]

Thanks for wonderful questions
My approach for last question:
Select Z.event_date, Count(c.user_id) as Users_Count from activity as Z left join(
Select b.event_date, B.user_id from activity as A
join activity as B on
A.user_id=B.user_id
where
(A.event_name='app-installed' and B.event_name='app-purchase')
and B.event_date=DATEADD(Day,1,A.event_date))C
on Z.event_date=C.event_date
group by Z.event_date

[@ankitbansal6]

Thanks for posting 👏

[@saurabhsoni1998]

nice approch :)

[@anshubhatia6532]

My approach:
select a2.event_date, count(a1.user_id) from activity as a1
inner join activity as a2
on a1.user_id = a2.user_id and DATE(a2.event_date) = DATE(a1.event_date) + INTERVAL 1 DAY
group by a2.event_date

[@arthurmorgan9010]

For part4:
with cte as
(
select user_id,min(event_date) as mindate,max(event_date) as maxdate, case when
DATEDIFF(day,min(event_date),max(event_date)) = 1 then 1 else 0 end as new
from activity
group by user_id
)
select distinct maxdate,new as 'nextdaybye' from cte

[@swethathiruppathy9973]

Hi Sir,
I 'm gaining Confident after solving problems posted in your channel..Thank you for all your videos..Keep posting and help us learn SQL.
Hereby sharing the end query Q5.
with prev_data as (
select *,
lag(event_name, 1) over (partition by user_id order by event_date) as prev_event_name,
lag(event_date, 1) over (partition by user_id order by event_date) as prev_event_date
from activity)
select event_date ,
sum(case when event_name='app-purchase' and prev_event_name='app-installed' and datediff(day,prev_event_date, event_date) =1
then user_id else 0 end) as cnt_users
from prev_data
group by event_date;

[@chenchuladileep7410]

Great video sir ..last question i made using right join
with cte as(
select *, lag(event_date,1) over(partition by user_id order by event_date) as new_date
from activity),cte1 as (
select * from cte a where datediff(day,a.new_date,event_date)=1)
select a.event_date, count(distinct c.user_id) as no_of_users
from cte1 c
right join activity a on a.event_date=c.event_date
group by a.event_date

[@ChittaTarunSaiBalaGopal]

My approach for the last question:
with history as
(select *,
lag(event_name,1) over(partition by user_id order by event_date) as prev_event,
lag(event_date,1) over(partition by user_id order by event_date) as prev_date
from activity)
select event_date,
count(distinct case when event_name = 'app-purchase' and prev_event = 'app-installed' and DATEDIFF(event_date, prev_date) = 1 then user_id else null end) as user_count
from history
group by 1;

[@chenchuladileep7410]

Great video sir, my try for 3rd question
with cte as (
select *, lead(event_date,1) over (partition by user_id order by event_date desc) as lead_date
from activity),cte2 as (
select * from cte where lead_date is not null and event_date=lead_date)
select a.event_date, count(distinct c.user_id) as no_of_users
from cte2 c
right join activity a on a.event_date=c.event_date
group by a.event_date

[@Babug-m7o]

5th question --------------------->
-----------who installed app on a given day and purchased very next day--------------------
select event_dt,sum(flag) as cnt_users from (
select *,case when datediff(day,lead(event_dt) over(order by user_id),event_dt)= 1 then 1 else 0 end as flag
from t16_activity)a group by event_dt

[@Babug-m7o]

4th question answer
-----------------percentage of india ,usa and others------------------
select distinct k as Country,count(1) over(partition by k)*100/count(1) over() as percentage_users from(
select *,case when country = 'India' or country ='USA' then country else 'Others' end as k
from t16_activity where event_name = 'app-purchase')a;

[@shekharagarwal1004]

Thanks Ankit for such insightful content .
/* Question 5 :- Among all users who installed the app on given day, how many did app purchased very next day */
Select count(a2.user_id) as tot_cnt
,case when a1.event_date = dateadd(day,-1,a2.event_date) then a2.event_date else a1.event_date end as eventdate
from activity a1 left outer join activity a2
on a1.user_id=a2.user_id and a1.event_name='app-installed' and a2.event_name='app-purchase'
and a1.event_date = dateadd(day,-1,a2.event_date)
group by case when a1.event_date = dateadd(day,-1,a2.event_date) then a2.event_date else a1.event_date end
---- approach 2 , with single table
with cte1 as
(select *
,lag(event_name,1) over ( partition by user_id order by event_date) as prev_event_name
,lag(event_date,1) over ( partition by user_id order by event_date) as prev_event_date
from activity)
select sum(case when event_date = dateadd(day,1,prev_event_date) then 1 else 0 end) as total_cnt ,event_date
from cte1
group by event_date

[@ankitbansal6]

Good one

[@Damon-007]

select a.event_date, coalesce (count(b.user_id),0) from activity a
left join
activity b
on
datediff(day,b.event_date, a.event_date)=1
and a.event_name b.event_name
and a.user_id=b.user_id
group by a.event_date

[@ujjwalvarshney3188]

Q5-
select a.event_date , nvl(count( case when a.event_name = 'app-purchase' and b.event_name = 'app-installed' then a.user_id else null end ),0) as total_purchase
from activity a left join activity b on a.user_id = b.user_id and date_add('day',1,b.event_date) =a.event_date
group by 1 order by 1

[@nijamuddinshaik9130]

for oracle 5 th answer
select a.event_date,
sum(case when a.event_name='app-purchase' and b.event_name='app-installed' then 1 else 0 end) as cv
from activity a left join activity b
on a.user_id=b.user_id and a.event_date=b.event_date+1
group by a.event_date order by a.event_date;

[@shitanshuk1752]

with t1 as
(select country,user_id,event_name,
lead(event_name) over(partition by user_id order by user_id) as next_event from activity_19),
t2 as
(select
case when country in('india','USA') then country else 'others' end as country,
case when event_name='app-installed' and next_event='app-purchase' then 1 else 0 end as ref
from t1 )
select country,sum(ref)*1.0/sum(sum(ref)) over() *100 as paid_users_per
from t2 group by country

[@sagarsaini5447]

another solution
with cte as(
select *, DATEADD(day,1,event_date) as app_purchased_date
from activity
where event_name='app-installed')
select t1.event_date,count(distinct c1.user_id) as cnt_users
from activity t1
left join cte c1 on t1.user_id=c1.user_id and t1.event_date=c1.app_purchased_date and t1.event_namec1.event_name
group by t1.event_date

[@LearnYouAndMe]

Great learning, here is my solution for 5th or last one using JOIN
with set1 as
(
select user_id,dateadd(day,1,event_date) as edt,'app-purchase' as en
from activity where event_name='app-installed'
)
select t.event_date,
sum(iif(jt1.user_id is null,0,1)) as userCnt
from activity as t
left join set1 as jt1 on jt1.user_id=t.user_id and jt1.edt=t.event_date and jt1.en=t.event_name
group by t.event_date

[@hustlerguy6091]

For Part 4 my solution: (A little complex though but short)
select
CASE WHEN country NOT IN ('India' , 'USA') THEN 'Other' ELSE country END as country,
100.0*COUNT(DISTINCT CASE WHEN event_name='app-purchase' THEN user_id ELSE NULL END)/(SELECT COUNT(DISTINCT user_id) from activity where event_name='app-purchase')
from activity
group by 1
order by 1

[@2412_Sujoy_Das]

Hi, I think there might be a slight issue with the code..... It is generating duplicates of "others" with its correct value. Thanks

[@sandeepanand3834]

solution for q5.
select a1.event_date, count(a2.user_id) cnt_users
from activity a1 left join activity a2
on a1.user_id = a2.user_id and
a1.event_date = adddate(a2.event_date, interval 1 day)
and a2.event_name = 'app-installed' and a1.event_name = 'app-purchase'
group by a1.event_date
:)

[@AjithShetty-u7e]

For 4 th question my solution
with cte1 as
(Select *,case when country='India' or country='USA' then country else 'Others' end as cty
from activity where event_name='app-purchase')
Select cty, 1.0*count(cty)/(select count(*) from cte1)*100 as perc
from cte1
group by cty

[@ayushsrivastav8220]

Part 3 -
with cte1 as(select user_id,event_date,count(distinct event_name) as cnt from activity
group by user_id,event_date)
select event_date, sum(case when cnt < 2 then 0 else 1 end) as users
from cte1 group by event_date

[@aamirsohail2576]

Q-4
with cte as(
select country,event_name
,sum(case when event_name = 'app-purchase' then 1 else 0 end) as cnttt
from activity
group by country,event_name),ab as(
select *,(cnttt*1.0/sum(cnttt) over(partition by event_name order by event_name))*100 as cpp,
case when country in('USA','India') then country else 'others' end as country1
from cte
where event_name = 'app-purchase')
select country1,sum(cpp) as total_percentage
from ab
group by country1,cpp
order by country1

[@saurabhsoni1998]

--3
select a1.event_date, count(distinct a2.user_id) as no_of_active_users_count from activity a1
left join activity a2
on a1.user_id = a2.user_id
and a1.event_date = a2.event_date
and a1.event_name a2.event_name
group by a1.event_date

[@kailashpatro5768]

with cte as (
select *,lag(event_name,1,event_name) over(partition by event_date order by event_date ) as rnk from activity)
select event_date , count(distinct cnt ) as no_of_user from (
select *, case when event_name rnk then user_id else null end as cnt from cte ) a
group by event_date

[@prajwalns1416]

Hi Ankit, thanks for the video.
my solution for the last part in postgreSQL:
WITH CTE AS (SELECT EVENT_DATE,
CASE WHEN EVENT_NAME = 'APP-PURCHASE'
AND LAG(EVENT_NAME) OVER(PARTITION BY USER_ID ORDER BY EVENT_DATE) = 'APP-INSTALLED'
AND LAG(EVENT_DATE) OVER(PARTITION BY USER_ID ORDER BY EVENT_DATE)+1 = EVENT_DATE THEN 1 ELSE 0 END AS CNT_USERS
FROM ACTIVITY)
SELECT EVENT_DATE, CNT_USERS
FROM CTE
GROUP BY 1, 2
ORDER BY 1

[@vaibhavverma1340]

Question -5 * (simple approach)
select a2.event_date,
sum(case when datediff(day,a1.event_date, a2.event_date) = 1 then 1 else 0 end)cnt_users
from Activity a1
join activity a2 on a1.user_id = a2.user_id and a1.event_name a2.event_name
group by a2.event_date

[@hustlerguy6091]

For Part 5 my solution:
select a1.event_date, count(distinct a2.user_id) cnt_users
from activity a1 LEFT JOIN activity a2 ON a1.user_id=a2.user_id AND a1.event_date-1=a2.event_date
group by 1
order by 1

[@ayushipatra9976]

Thank You for sharing this case study!
I completed the 5th question as suggested using PostgreSQL:
select event_date ,
count(case when event_name = 'app-purchase'
and prev_name = 'app-installed'
and extract(day from event_date::timestamp - prev_date::timestamp) = 1
then event_date else null end ) as cnt_users
from ( select * ,
lag(event_name,1) over(partition by user_id order by event_date) as prev_name,
lag(event_date,1) over(partition by user_id order by event_date) as prev_date
from activity ) temp
group by event_date
order by event_date
Please check and let me know if it looks good. Also, if possible please do share Data Engineer Interview Python Questions. Will truly appreciate it!

[@ankitbansal6]

Looks good. Thanks for posting 👏

[@shivanshgupta9732]

alternative solution to Q4:
with cte_a as
(select case when country in ("India","USA") then country else "others" end as cou,count(distinct user_id) as co
from activity
where event_name= "app-purchase"
group by 1
)
select cou , co/SUM(co) over()*100 from cte_a

[@apurvasaraf5828]

with cte as (select *,case when country='India' then 'India'
when country='USA' then 'USA'
else 'Other' end as c
from activity where event_name='app-purchase')
select c ,count(*)*100/(select count(*) from cte) from cte group by c

[@ujjwalvarshney3188]

Q-4create temp table uj24 as
(
select case when country ='India' then 'India' when country ='USA' then 'USA' else 'Others' End as Country ,
count(distinct case when event_name = 'app-purchase' then user_id else null end )::numeric(38,2) as cust
from activity group by 1
);

select country ,(cust/sum(cust )over())*100 from uj24

[@vinayghildiyal2727]

My solution for last question:
with cte as
(
select *,day(event_date) as days,
lag(day(event_date)) over(partition by user_id) as no_days,
day(event_date) - lag(day(event_date)) over(partition by user_id) as diff
from activity
)
select event_date, sum(case when diff=1 then 1 else 0 end) as cnt_users
from cte
group by event_date
order by event_date asc;

[@ankitachatterjee8769]

My approach for the 5th one:
select b.event_date,count(case when datediff(day,b.event_date,a.event_date)=1 then a.user_id else null end) as cnt_users
from activity a inner join activity b on a.user_id=b.user_id
group by b.event_date order by b.event_date

[@AjithShetty-u7e]

Hi @ankitachatterjee8769
The correct solution is
select b.event_date,count(case when datediff(day,a.event_date,b.event_date)=1 then a.user_id else null end) as cnt_users
from activity a inner join activity b on a.user_id=b.user_id
group by b.event_date order by b.event_date

[@sandeepnigam757]

Ans 3rd question -
with my_cte as (
select user_id, min(event_date) as fv from activity group by user_id)
select *
from my_cte inner join activity on my_cte.user_id = activity.user_id where
(case when
datediff(day, event_date, fv) = 0 and event_name = 'app-purchase' then 1 else 0 end) = 1

[@ank_kumar12]

question 5-
select event_date,
sum(case when subs = 1 then 1 else 0 end) as cnt_users
from(
select *,day(event_date) as day_no,
lag(day(event_date)) over(partition by user_id) as lag1,
day(event_date) - lag(day(event_date)) over(partition by user_id) as subs
from activity
) t
group by event_date;

[@vikhyatjalota2213]

Ans: 5
with cte_1 as (
select *,
case
when lag(event_date) over(partition by user_id order by event_date) = event_date -1
and event_name = 'app-purchase' then 1 else 0 end as flag
from activity )
select event_Date, sum(flag) as cnt from cte_1
group by 1

[@ShivamGupta-ek4rd]

Also another solution for 5th problem is :
select y.event_date, case when sum(diff) = 1 then 1 else 0 end as diff from (
select x.*, coalesce(date_part('Day', event_date) - date_part('Day', previous_date),0) as diff from (
select *, lag(event_date)over(partition by user_id order by event_date asc, event_name) as previous_date
from spotify
) as x
) as y group by y.event_date
order by y.event_date

[@Babug-m7o]

No_of_users_same_day_purchase:
3rd question answer-------->
with cte as (
select *,case when user_id = LEAD(user_id) over(order by user_id) and
event_dt = LEAD(event_dt) over(order by user_id) then 1 else 0 end as flag from t16_activity)
select event_dt,sum(flag) as no_of_users_same_day_purchase from cte group by event_dt

[@PriyaYadav-jh5wj]

My solutions from q3 onwards:
Q3
select distinct b.event_date,
coalesce(cnt,0) from
(select event_date,count(distinct user_id) as cnt
from
(select user_id,event_date,
lead(event_date) over (partition by user_id)
from activity) a
where lead is not null
and event_date = lead
group by event_date) a
right join activity b
on a.event_date=b.event_date
order by event_date
Q4
with t1 as
(select * ,
case when country = 'USA' then 'USA'
when country = 'India' then 'India'
else 'others' end as ctry
from activity where event_name = 'app-purchase') ,
t2 as(
select distinct
ctry,
count(*) over (partition by ctry) as ctry_cnt,
count(*) over() as ttl_cnt
from t1)
select ctry,
round(1.0*ctry_cnt/ttl_cnt*100,0) as pct
from t2
Q5
select distinct b.event_date,
coalesce(cnt,0) from
(select event_date,count(distinct user_id) as cnt
from
(select user_id,event_date,
lag(event_date) over (partition by user_id)
from activity) a
where lag is not null
and event_date = lag+1
group by event_date) a
right join activity b
on a.event_date=b.event_date
order by event_date

[@ayushtyagi4240]

4th Ques :
with temp as (select
case
when country not in ("India" , "USA") then "others" else country end as countries
, count(user_id) buyers , sum(count(user_id)) over() as total_user
from activity
where event_name = "app-purchase"
group by 1 )
select countries , cast(buyers / total_user *100 as SIGNED) percentage from temp

[@ajaxaj8470]

select a.user_id from (select a.user_id,a.event_date as install_date,b.event_date as purchase_date from activity a
inner join activity b
on a.[user_id]=b.[user_id]
where a.event_name='app-installed' and b.event_name='app-purchase')a
where install_date=purchase_date
select case when country='India' then 'India'
when country='USA' then 'USA'
else 'others' end as Country,
(count(case when event_name='app-purchase' then 1 else null end)*1.0/count(*))*100 as percen
from activity
group by case when country='India' then 'India'
when country='USA' then 'USA'
else 'others' end

[@ankitbansal6]

Thanks for posting 👏

[@shitanshuk1752]

with t1 as
(select *,
lead(event_name) over(partition by user_id order by event_date) as next_event,
lead(event_date)over(partition by user_id order by event_date)as next_event_date from activity_19)

select event_date,
sum(case when event_name= 'app-installed' and next_event='app-purchase' and datediff(day,event_date,next_event_date)=1
then 1 else 0 end) as no_of_users from t1 group by event_date

[@AbhishekKumar-eh1ed]

My solution for Q5:
with cte as(
select event_date, sum(date_diff) as cnt from (
select *, lead(event_date,1,event_date)over(partition by country order by user_id) as lead1,
datediff(day, event_date, lead(event_date,1,event_date)over(partition by country order by user_id)) as date_diff
from activity where event_name = 'app-purchase' )t
group by event_date)
select distinct A.event_date,coalesce(B.cnt,0) as cnt_users from activity A left join cte B
on A.event_date = B.event_date

[@ujjwalvarshney3188]

for Q3 my solution
create temp table uj23 as
(
select a.event_date,count( distinct a.user_id) as cust
from activity a left join activity b on a.event_date = b.event_date
where a.event_name = 'app-installed' and b.event_name ='app-purchase'
group by 1 order by 1
);
select a.event_date, nvl(cust,0) as cust
from activity a left join uj23 b
on a.event_date = b.event_date
group by 1,2 order by 1

[@arindamsaha1333]

for part 3
-----------------
with re_tab as
(select dated,case when count(dated) = 2 then 1 end as no_of_users from
(select user_id,event_name,event_date dated from activity where event_name='app-installed'
union all
select user_id,event_name,event_date dated from activity where event_name='app-purchase')A
group by dated, user_id)
select dated, count(no_of_users) as num_of_users from re_tab
group by dated

[@aryakaagnihotri3210]

my solution for 5th one
select event_date ,
sum(case when datediff(event_date,r)=1 then 1 else 0 end ) user_nextday
from (select * ,
lag(event_date) over (partition by user_id) r
from activity) A
group by event_date ;

[@kumarjyotiraditya2966]

with installed_purchase as
(
select a.*
from activity as a
inner join activity as b
on a.user_id = b.user_id
and a.event_date = b.event_date
and a.event_name > b.event_name
)
select a.event_date,count(distinct b.user_id ) as total_users
from activity as a left join
installed_purchase as b
on a.user_id = b.user_id
group by a.event_date

[@shitanshuk1752]

with t1 as
(select *,
lead(event_name) over(partition by user_id order by event_date) as next_event ,
lead(event_date) over(partition by user_id order by event_date) as next_date from activity_19)
select event_date,
sum(case when event_name='app-installed' and next_event='app-purchase' and event_date=next_date then 1 else 0 end)
as purchase_on_same_dayornot
from t1 group by event_date

[@ankitbansal6]

Good effort

[@parth_pm16]

Sloution for laste part using "CASE" :
with prev_data as (select *
, lag(event_name,1) over(partition by user_id order by event_date) as prev_event_name
, lag(event_date,1) over(partition by user_id order by event_date) as prev_event_date
from activity_SP)
select event_date
, count(case when event_name = 'app-purchase' and prev_event_name = 'app-installed' and datediff(day,prev_event_date,event_date)=1 then (user_id) else null end) as cnt_user
from prev_data
group by event_date

[@anshumansrivastava2801]

1. select event_date,count (distinct user_id) from activity
group by event_date
order by 1;
2. select date_part('week',event_date) as week_num,count(distinct user_id) from activity
group by week_num
order by 1;
3. select a.event_date,sum(case when a.event_name b.event_name and a.event_name='app-installed' then 1 else 0 end) as case_total
from activity a inner join activity b
on a.user_id=b.user_id
and a.event_date=b.event_date
group by a.event_date;
4.
select a.country_flag,round(cast(active_user as decimal)/total_user,2) from
(
(select country_flag,count(1) as active_user from(
select *,case when country='India' then 'India' when country='USA' then 'USA' else 'Others' end as country_flag
from activity
where event_name='app-purchase')b
group by country_flag) a
inner join
(select country_flag,count(1) as total_user from(
select *,case when country='India' then 'India' when country='USA' then 'USA' else 'Others' end as country_flag
from activity)b
group by country_flag) b
on a.country_flag=b.country_flag)
order by 1
5. select a.event_date,count(b.user_id) as next_day_purchase
from activity a left join activity b
on a.user_id=b.user_id
and a.event_date-b.event_date=1
group by a.event_date
order by 1

[@ankitbansal6]

Great effort 😊

[@divyanshisharma4054]

Fantastic Ankit. Thank you so much for this video. I am currently preparing for my DA interview and this is proving to be very helpful :)

[@ankitbansal6]

All the best

[@enisertem9738]

with base as (select *, lag(event_date,1) over(partition by user_id order by user_id) next_activity_date from activity)
select event_date, sum(ctn) cnt_users from (
select 1 ctn , event_date from base where event_date - next_activity_date =1 and event_name = 'app-purchase'
union all
select distinct 0 ctn, event_date from base
) A group by 1 order by 1

[@kishorkishu1676]

with cte as (
select
*,
lag(event_date,1) over(partition by user_id order by event_date) prev_day,
lag(event_name,1) over(partition by user_id order by event_date) prev_name
from activity )
select event_date,
count(case when event_name = 'app-purchase' and prev_name='app-installed' and DATEDIFF(day,prev_day,event_date)=1 then user_id else null end) as cnt_users from cte
group by event_date

[@ayushgupta-gz1zr]

with cte1 as(
select * from activity where event_name='app-installed'
),
cte2 as (select * from activity where event_name='app-purchase'
),
cte3 as(
select * from cte1 left join cte2 on DATE_PART('DAY',cast(cte2.event_date as timestamp)-cast(cte1.event_date as timestamp))=1
and cte1.user_id=cte2.user_id
)
select * from cte3;

[@dfkgjdflkg]

your videos are so short and good. We thank you for your help. I will study of your examples, I learnt a lot from them. Thanks

[@adityakishan1]

I think below soln also explains how we can use subquery in select clause
select case when country not in ('India','USA') then 'others' else country end country,
convert(int,(count(distinct user_id)*1.0/(select count(1) from activity where event_name='app-purchase'))*100) as paid_users_frac
from activity
where event_name = 'app-purchase'
group by case when country not in ('India','USA') then 'others' else country end

[@apurvasaraf5828]

with cte as (select case when country in ('USA','India') then country else 'others' end as c
,count(*) as d from activity
where event_name='app-purchase'
group by case when country in ('USA','India') then country else 'others' end)
select c,d*100/(select sum(d) from cte)
from cte

[@aaravkumarsingh4018]

Q3. My approach
with cte as(
select a1.user_id, a1.event_name as event1,a2.event_name as event2,a1.event_date
from activity a1 left join activity a2
on a1.user_id=a2.user_id and a1.event_date=a2.event_date and a1.event_name>a2.event_name and a1.event_namea2.event_name
)
select event_date,count(case when event1 is not null and event2 is not null then 1 else null end) as active_users
from cte
group by event_date;
Q5. with cte as(
select * from(
select *,lead(event_date) over(partition by user_id order by event_date ) as next_day
from activity
order by event_date)a
where datediff(next_day,event_date)=1
)
select event_date ,
count(case when user_id in (select user_id from cte ) and event_date in(select next_day from cte) then 1 else null end) as cnt_users
from activity group by event_date;

[@msd7451]

Question:5
select
a1.event_date,count(distinct a2.user_id) as no_of_users_same_day_purchase
from activity as a1
left join activity as a2
on (a1.user_id=a2.user_id) and (a2.event_name='app-installed' and a1.event_name='app-purchase')
and (a1.event_date=date_add(a2.event_date,INTERVAL 1 DAY))
group by a1.event_date

[@harshSingh-if4zb]

select a.event_date
,sum(case when datediff(b.event_date,b.lg_date) =1 and lg_activity='app-installed' then 1 else 0 end) as user_count
from (select distinct event_date from app) a left join
(select event_name,event_date,
lag(event_date) over(partition by user_id order by event_date ) as lg_date,
lag(event_name) over(partition by user_id order by event_date ) as lg_activity
from app) b
on a.event_date = b.event_date
group by a.event_date

[@ankitbansal6]

Thanks for posting 👏

[@ashpoghosh7645]

Question 3 Soln
with temp as(
select event_date,user_id, count(distinct event_name) as cnt
from activity1 group by event_date,user_id having cnt=2
)
select a.event_date,ifnull(count(distinct t.user_id),0) as no_users_same_day_purchase
from activity1 a left join temp t on a.user_id=t.user_id
group by a.event_date

[@arpanscreations6954]

Question 5 answer:
select
a.event_date,
count(aa.user_id) as cnt_users
from activity a
left join
(
select a1.user_id,
a2.event_date
from
activity a1
inner join activity a2
on a1.user_id = a2.user_id
where a1.event_name = 'app-installed' and a2.event_name = 'app-purchase' and a1.event_date = dateadd(day, -1 , a2.event_date)
) aa
on
a.event_date = aa.event_date
group by a.event_date

[@shivkumarpatel7940]

country wise paid_user
with cte as (
select distinct user_id as useridd ,event_name , country
from activity
where event_name = 'app-purchase' ) ,
cte2 as (
select
(CASE WHEN COUNTRY='india' or COUNTRY='usa' then COUNTRY else 'others' end) as new_country
, count(CASE WHEN COUNTRY='india' or COUNTRY='usa' then COUNTRY else 'others' end) as ppaid_user
from cte
group by (CASE WHEN COUNTRY='india' or COUNTRY='usa' then COUNTRY else 'others' end) )
,
cte3 as (
select sum(ppaid_user) as total_user from cte2
)
select new_country , ppaid_user*1.0/total_user*100 as perc_users from cte2,cte3

[@gauravgupta5530]

QUESTION 3:
with cte as (select *,
LAG(event_name) OVER (partition by event_date,user_id order by event_date, user_id) as previous_event
from activity order by event_date, user_id),
cte2 as (
select *, CASE WHEN previous_event is null then 0 ELSE 1 END as active from cte)
select event_date, sum(active) from cte2 group by event_date;

[@GautamKumar-ci4rz]

Last example ans i n oracle:-
with e1 as (
SELECT
USER_ID, EVENT_NAME, EVENT_DATE,
lag(EVENT_NAME,1)over(partition by user_id order by event_date) prev_event,
lag(EVENT_DATE,1)over(partition by user_id order by event_date) prev_EVENT_DATE
FROM
activity),
e2 as (
select
user_id,
event_date,
case when event_date -prev_EVENT_DATE =1 then 1 else 0 end cnt
from
e1
where event_name ='app-purchase' and prev_event ='app-installed')
select event_date , sum(cnt) from e2 group by event_date;

[@enisertem9738]

with ins as (select user_id,event_date from activity where event_name ='app-installed' )
, pur as (select user_id,event_date from activity where event_name ='app-purchase' )
select i.event_date, count(coalesce (p.user_id::int,0)) total from ins i left outer join pur p on i.user_id = p.user_id and i.event_date= p.event_date group by 1

[@sahilummat8555]

Hi Ankit , My solution for the complex problem .
;with cte as (
select * ,
case when USER_ID=lag(user_id)over(order by user_id,event_date) and event_name!=lag(event_name)over(order by user_id,event_date) and
datediff(day,lag(event_date)over(order by user_id,event_date),event_date)=1 then 1 else 0 end as flag
from activity)
select event_date,sum(flag) from cte
group by event_date

[@AbhishekPandey-tk1it]

5.SELECT event_date, SUM(diff) as cnt_users from
(select user_id,MAX(event_date) as event_date,
CASE WHEN DATEDIFF(day,min(event_date),max(event_date))=1 then 1 else 0
END as diff
from activity
group by user_id )t1
group by event_date

[@ashutoshsharma3396]

Hi Ankit: can you please make video on your Amazon interview experience? Also were DSA questions asked in Amazon interview?

[@ankitbansal6]

Sure. No I was not asked DSA.

[@MixedUploader]

problem 5 assignment solution:
with cte as(select *,
lag(event_date,1,'9999-12-31') over(partition by user_id order by event_date) as previous_event_date
from activity)
select event_date,count(distinct case when datediff(day,previous_event_date,event_date)=1 then user_id else null end) as users_count
from cte
group by event_date

[@prakashkrishnan7132]

Hi Ankit, please clarify the below
create table cricket(ball_cnt integer , run varchar2(100));
insert into cricket values(1,4);
insert into cricket values(2,0);
insert into cricket values(3,2);
insert into cricket values(4,1);
insert into cricket values(5,4);
insert into cricket values(6,3);
insert into cricket values(7,6);
insert into cricket values(8,4);
insert into cricket values(9,4);
insert into cricket values(10,0);
insert into cricket values(11,0);
insert into cricket values(12,0);
Have tried and got the answer by using a case statement.
with src as
(
select '1stover' which_over, CASE when ball_cnt > 0 and ball_cnt < 7 then run end as over_rate from cricket
union all
select '2ndover' which_over, CASE when ball_cnt > 6 and ball_cnt < 13 then run end as over_rate from cricket
)
select which_over,sum(over_rate) from src
where over_rate is not null
group by which_over
But this is a static way and we need to.present in a dynamic way. Do we have any solutions? Kindly help us

[@2412_Sujoy_Das]

My approach for Q4:
WITH CTE_1 AS (
Select B.event_date, A.user_id
FROM (Select * FROM activity
WHERE event_name like ('app-installed'))A
JOIN
(Select * from activity
WHERE event_name like ('app-purchase')) B
ON A.user_id = B.user_id AND B.event_date = DATEADD(DAY,1,A.event_date))
Select A.event_date, count(B.user_id) as net_count
FROM activity A
LEFT JOIN
CTE_1 B
ON A.event_date = B.event_date
GROUP BY A.event_date;

[@vijaygupta7059]

with cte as
(
Select *
, lag(event_name,1)over(partition by user_id order by event_date) as prev_event_name
, lag(event_date,1)over(partition by user_id order by event_date) as prev_event_date
from activity
)
Select event_date
,count(case when datediff(day,prev_event_date,event_date)=1 and event_name='app-purchase' and prev_event_name='app-installed' then 1 else null end ) cnt_users
from cte
group by event_date

[@FootballWithAnkit]

sol for last qn
select event_date, sum(case when l=1 then 1 else 0 end) as re from(
select user_id, event_date,
datediff(day,(lag(event_date,1,event_date) over(partition by user_id order by event_date)),event_date) as l
from activity) as t1
group by event_date;

[@aditya-k]

Thanks for the SQL Problem Discussion, Ankit!

[@ashutoshgoyal3383]

Q4 APPROACH
;with cte as(select user_id,min(event_date) mindate,max(event_date) maxdate from activity
group by user_id having count(user_id)=2 ),
cte2 as(select distinct(event_date) date from activity),
cte3 as(select mindate,count(*) count from cte where mindate=maxdate group by mindate)
select cte2.date,cte3.count from cte2 left join cte3 on cte2.date=cte3.mindate

[@Satish_____Sharma]

with cte as (SELECT *,lag(event_name) over (partition by user_id order by event_name asc) as pre_event,
lag(event_date) over (partition by user_id order by event_name asc) as pre_date
FROM activity)
select event_date,
count(case when event_name='app-purchase' and pre_event='app-installed' and datediff(event_date,pre_date)=1 then user_id else null end) as total_user
from cte
group by event_date

[@suriyas6338]

Hi Ankit,
For 5, Please find my solution :)
select event_date, count(total_users) as total_users from (
select *,
lag(event_name,1,'UNK') over(partition by user_id order by event_date) as prev_event_Name,
lag(event_date,1, '9999-12-31') over(partition by user_id order by event_date) as prev_event_date,
case when
event_name = 'app-purchase' and
lag(event_name,1,'UNK') over(partition by user_id order by event_date) = 'app-installed' and
DATEDIFF(DAY, lag(event_date,1, '9999-12-31') over(partition by user_id order by event_date) ,event_date) = 1
then user_id else null end as total_users
from activity
)a
group by event_date

[@Reacher1998]

Part - 5 in MySql ->
with cte as(
select *, lag(event_name,1) over (partition by user_id order by event_date) as prev_event_name,
lag(event_date,1) over (partition by user_id order by event_date) as prev_event_date
from activity)
select event_date,
count(case when event_name='app-purchase' and prev_event_name='app-installed' and datediff(event_date,prev_event_date)=1 then 1 else null end)
as user_cnt
from cte
group by 1
order by 1;

[@gauravgupta5530]

QUESTION 4:
with cte as (select count(*) as total_purchase
from activity
where event_name = 'app-purchase'),
cte3 as (
select country, user_id, CASE WHEN country='India' or country='USA'then country else 'others' END as updated_country
from activity where event_name = 'app-purchase'
),
cte2 as (select updated_country, (count(distinct user_id)*100)/c.total_purchase as count_purchase
from cte3 a CROSS JOIN cte c
group by updated_country, c.total_purchase)
select * from cte2;

[@apurvasaraf5828]

with cte as(select * ,lead(event_date) over(partition by user_id order by event_date) as l
from activity)
select event_date, sum(case when l=event_date then 1 else 0 end) as f
from cte
group by event_date

[@simardeepsingh]

with prev_data as(
select *
,lag(event_name,1) over(partition by user_id order by event_date) as prev_event_name
,lag(event_date,1) over(partition by user_id order by event_date) as prev_event_date
from activity
)
select event_date,
COUNT(CASE WHEN event_name = 'app-purchase' and prev_event_name = 'app-installed' and DATEDIFF(day,prev_event_date, event_date) =1 then 1 else NULL END)
from prev_data
GROUP BY event_date

[@shalinimeena7863]

last solution:
with cte as (select * from (select user_id,event_name,lag(event_date,1) over (partition by user_id) as prev,event_date,
datediff(event_date,lag(event_date,1) over (partition by user_id) ) as diff from activity)aa)
select event_date,(case when diff=1 then count(user_id) else 0 end) as c1 from cte
group by event_date ;

[@mohammadabdullahansari6314]

My query for case 4:
with master_data as
(select
*,
lag(event_name,1) over (partition by user_id order by event_date) as prev_event_name,
lag(event_date,1) over (partition by user_id order by event_date) as prev_event_date
from
spotify_activity)
select
event_date,
count(case when datediff(event_date, prev_event_date) = 1 and prev_event_name = 'app-installed' then user_id else null end)
from
master_data
group by 1

[@aditya-k]

Solution for the last question in MySQL:
WITH prev_data AS (
SELECT *, LAG(event_name, 1) OVER (PARTITION BY user_id ORDER BY event_date) as prev_event_name,
LAG(event_date, 1) OVER (PARTITION BY user_id ORDER BY event_date) as prev_event_date
FROM activity)
SELECT event_date,
(CASE WHEN event_name = 'app-purchase' and prev_event_name = 'app-installed' AND DATEDIFF(event_date, prev_event_date) = 1 THEN COUNT(DISTINCT user_id)
ELSE 0 END) AS User_Count
FROM prev_data
GROUP BY event_date;

[@ankitbansal6]

Looks good. I think count should be on top of full case statement.

[@aditya-k]

@@ankitbansal6 I just wrote and tested the query based on the given output 😅

[@prakashkathait927]

5th Qs Modification
with prev_data as(
SELECT *,
LAG(event_name,1) OVER(PARTITION BY user_id ORDER BY event_date) as prev_event_name,
LAG(event_date,1) OVER(PARTITION BY user_id ORDER BY event_date) as prev_event_date
FROM activity)
SELECT event_date,
count(CASE WHEN event_name ='app-purchase' AND prev_event_name ='app-installed' AND TIMESTAMPDIFF(day,prev_event_date,event_date)=1
THEN user_id ELSE NULL END) as cnt_users FROM prev_data
GROUP BY event_date;

[@PraveenSinghRathore-df3td]

Solution for query 5:
with cte as (select user_id, event_name, event_date from activity order by 1,2,3),
cte2 as (select *, lag(event_date) over(partition by user_id order by user_id, event_name, event_date) as lg,
adddate(event_date,-1) as yesterday from cte)
select event_date, (case when lg = yesterday then count(distinct user_id) else 0 end) as customers from cte2
group by 1 order by 1;

[@dileep3809]

Great job brother....few days ago..one of your video was on my feed....it was good later I went and saw most of your videos..they are really great...u come up with unique content...most does only SQL tutorial..u come up with different use cases...keep it up brother
..keep going...u are really doing great help

[@ankitbansal6]

Thanku Chintu. Means a lot to me. Please do share videos with your friends.

[@ramchavali5825]

For Part 5:
with lag_cte
as
(select user_id, event_name, lag(event_name) over (partition by user_id order by event_date) as lag_event_name,
event_date, lag(event_date) over (partition by user_id order by event_date) as lag_event_date
from
activity)
select event_date, sum(case when ((event_name = 'app-purchase' and lag_event_name = 'app-installed')
and (lag_event_date is not null)
and (event_date = lag_event_date + 1)) then 1
else 0 end) as next_day_purchase
from
lag_cte
group by event_date
order by event_date

[@enisertem9738]

with base as (
select sum(1) over(order by 1) total_ctn, count(1) over (partition by
case when country in ('USA','India') then country else 'others' end order by 1 ) nn, case when country in ('USA','India') then country else 'others' end
n_country from activity where event_name = 'app-purchase'
)
select n_country as country , (nn * 1.0 / ctn)*100 perc_users from (
select n_country, max(nn) nn , max(total_ctn) ctn from base group by 1 ) A group by 1,(nn * 1.0 / ctn)*100

[@Chathur732]

LAST QUESTION:
with cte as
(
select user_id,event_name,event_date,
lag(event_date) over(partition by user_id order by user_id) as next_date
from activity
)
select event_date , sum(case when date_1 = event_date then 1 else 0 end) as count_users
from
(
select event_date, case when date_sub(event_date, interval 1 day)=next_date then event_date else 0 end as date_1
from cte
) a
group by event_date

[@ritusantra8641]

with cte as (
select *,
lag(event_name,1) over(partition by user_id order by event_name) as prev_event_name,
lag(event_date,1) over(partition by user_id order by event_date) as prev_date
from activity)
select
event_date,
count(case when prev_event_name = 'app-installed'
and datediff(day,prev_date,event_date) = 1 then user_id end) as cnt_users
from cte
group by event_date;

[@ashutoshverma1418]

Q.5
with installed as (
select user_id, event_name, event_date from activity
where event_name="app-installed"),
purchased as(
select user_id, event_name, event_date from activity
where event_name="app-purchase")
select i.*, p.event_name,
case when date_add(i.event_date, interval 1 day)=p.event_date then 1 else 0 end as number_of_customers
from installed i, purchased p
where i.user_id=p.user_id;

[@shekharraghuvanshi2267]

My solution for the 5th part but without the day level data.
with cte as (
select
user_id
,min(case when event_name='app-installed' then event_date else '9999-12-31' end) as installed_date
,min(case when event_name='app-purchase' then event_date else '9999-12-31' end) as purchased_date
from activity19
group by user_id)
select purchased_date,count(*) as cnt
from cte
where datediff(purchased_date,installed_date)=1
group by purchased_date;

[@BengaluruGuy08]

Thanks for such a great content always....

[@ankitbansal6]

Thank you 😊

[@mohdtoufique7446]

Hi Ankit.. Thanks for the content..An alternative solution for the last question.
WITH CTE AS(
SELECT *,event_date AS purchase_date FROM activity a
WHERE event_name='app-purchase'),
CTE2 AS(
SELECT CTE.user_id,CTE.purchase_date,a.event_date,coalesce(DATEDIFF(DAY,a.event_date,purchase_date),0) AS nxt_day FROM CTE
RIGHT JOIN activity a
ON a.user_id=CTE.user_id
AND a.event_name='app-installed')
SELECT event_date,SUM(nxt_day) AS cnt_users
FROM CTE2
WHERE nxt_day

[@ankitbansal6]

Thanks for posting 👏

[@akshaygupta6321]

My Solution in Postresql:
select a2.event_date,
sum(case when a1.event_name='app-installed' and a2.event_name='app-purchase'
and a2.event_date-a1.event_date=1 then 1 else 0 end)
from activity a1
inner join activity a2
on a1.user_id = a2.user_id
group by 1
order by 1;

[@ithamarhardy3055]

For the last question :
``with pd as (select *
,lag(event_name,1) over(partition by user_id order by event_date) prev_event_name
,lag(event_date,1) over(partition by user_id order by event_date) prev_event_date
from activity)
select event_date, sum(case when datediff(day,prev_event_date,event_date)=1 then 1 else 0 end ) nb_user from pd
group by event_date``

[@rohantemgire9970]

with cte as (
select event_date,count(1) cnt from (
select *, lag(event_date) over(partition by user_id) prev_date from activity
)A
where datediff(event_date,prev_date) = 1 and event_name = 'app-purchase'
group by event_date)
select distinct a.event_date, count(cnt) cnt_users from activity a
left join cte on cte.event_date = a.event_date
group by a.event_date;