Top Data Analyst SQL Interview Question by A Startup

In this video we will discuss a very interesting SQL interview question asked by a startup for a data analyst position. This problem will test you advanced SQL skills like CTE , Window functions and data aggregation.
Solve the question here : www.namastesql...
script:
create table events
(userid int ,
event_type varchar(20),
event_time datetime);
insert into events VALUES (1, 'click', '2023-09-10 09:00:00');
insert into events VALUES (1, 'click', '2023-09-10 10:00:00');
insert into events VALUES (1, 'scroll', '2023-09-10 10:20:00');
insert into events VALUES (1, 'click', '2023-09-10 10:50:00');
insert into events VALUES (1, 'scroll', '2023-09-10 11:40:00');
insert into events VALUES (1, 'click', '2023-09-10 12:40:00');
insert into events VALUES (1, 'scroll', '2023-09-10 12:50:00');
insert into events VALUES (2, 'click', '2023-09-10 09:00:00');
insert into events VALUES (2, 'scroll', '2023-09-10 09:20:00');
insert into events VALUES (2, 'click', '2023-09-10 10:30:00');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@ankitbansal6]

Solve the question in SQL and pandas here for free: www.namastesql.com/coding-problem/118-user-session-activity

[@varunraste3538]

Ankit bhai you explain things in a so simple and stepwise manner ! Thanks ! If someone looks at the end query he might not understand what is going on but After listening to your explanation it feels so easy !

[@ankitbansal6]

So nice of you😊

[@Datapassenger_prashant]

Wow what an amazing milestone for me.. as I completed this Question. All 77 questions of this playlist are completed.

[@ankitbansal6]

Well done

[@Datapassenger_prashant]

@@ankitbansal6 ❤️

[@sumeetpatil4785]

session group by using SUM is so interesting..loved it..!!

[@hairavyadav6579]

Nice explanation sir, Big thanks to you 🙏

[@sahilummat8555]

Awesome Solution Sir

[@gouravkumar9011]

amazing solution

[@mohdtoufique7446]

Hi Ankit! Thanks for the content My approach with time_diff_flag AS( SELECT *, LAG(event_time,1,event_time) OVER(PARTITION BY userid ORDER BY event_time ASC) AS pre_event_time, DATEDIFF(MINUTE,LAG(event_time,1,event_time) OVER(PARTITION BY userid ORDER BY event_time ASC),event_time) AS time_diff, CASE WHEN (DATEDIFF(MINUTE,LAG(event_time,1,event_time) OVER(PARTITION BY userid ORDER BY event_time ASC),event_time)) >30 THEN 1 ELSE 0 END AS new_session_flag FROM events), session_group AS( SELECT *,SUM(new_session_flag)OVER(PARTITION BY userid ORDER BY EVENT_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_group FROM time_diff_flag) SELECT userid,min(event_time) AS session_start_time,max(event_time) AS session_end_time,count(event_type) AS no_sessions FROM session_group GROUP BY userid,session_group ORDER BY userid

[@kedarwalavalkar6861]

with cte as ( select * ,lag(event_time,1,date_add(event_time, interval 40 minute)) over(partition by userid order by event_time) as last_event_time from events ) ,cte2 as ( select * ,sum(case when abs(timestampdiff(minute,event_time,last_event_time)) > 30 then 1 else 0 end) over(partition by userid order by event_time) as run_flg from cte ) select userid ,run_flg as session_id ,min(event_time) as start_session ,max(event_time) as end_session ,timestampdiff(minute,min(event_time),max(event_time)) as session_duration ,count(run_flg) as event_count from cte2 group by userid ,run_flg;

[@Hkumar_new]

9:30 how it partion sir becase every event time are different...?

[@mrpam4672]

Partition by userid

[@Hkumar_new]

@mrpam4672 but it little bit confusing me ok that by user id but how it able to describe the sessions partitions

[@utsavkumar71]

I also applied for Seekho and solved the same question. But , I think in the question there was some mistake . So he output number of events is not as same as expected. Here is my solution: with session as( select *, lag(event_time) over(partition by user_id order by event_time) as prev_event, case when lag(event_time) over(partition by user_id order by event_time)

[@sumeetpatil4785]

i dont think if the first session has to end with same time as starttime... its just a start time

[@rohanpatil7364]

Hello Ankit sir I have been practising SQL from leetcode and I am quite confident in it but I am not able to solve problems, in the Online Test. Do you have any specific recommendations for me? Thank you

[@VishalKumar-xl2xd]

Bro can you help me with this? Question 1174. Immediate food delivery II I'm getting an error for case 2 WITH first_date AS (SELECT customer_id, MIN(order_date) AS FIRST, customer_pref_delivery_date FROM Delivery GROUP BY customer_id), percentage AS (SELECT CASE WHEN FIRST = customer_pref_delivery_date THEN 'immediate' ELSE 'scheduled' END AS immediate_scheduled FROM first_date) SELECT ROUND(SUM(CASE WHEN immediate_scheduled = 'immediate' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),2) AS immediate_percentage FROM percentage

[@karangupta_DE]

with cte as ( select userid, event_type, event_time, lag(event_time, 1, event_time)over(partition by userid order by event_time) as previous_event_time from events ), cte1 as ( select userid, event_type, previous_event_time, event_time, datediff('minutes', previous_event_time, event_time) as difference from cte ), cte2 as ( select userid, event_type, event_time, difference, sum(case when difference 30 then 0 else difference end) as session_minutes from cte2 group by userid, group_key;

[@ehabelnady4960]

Is this for a fresher?

[@nitinrautela1070]

Hi ankit bhai.. agar possible ho toh videos hiEnglish mai bana sakte ho.. jishe non english wale students bhi ache se connect kar sake.🙏🙏

[@ankitbansal6]

I think it's time to launch a Hindi channel.

[@nitinrautela1070]

@@ankitbansal6 agar aisa hoga toh bhut badiya hoga sir.

[@ayushkawale2017]

very hard

[@AjayKumar-y7z1l]

Sir aapne itna expensive course kr rakha hai

[@VishalKumar-xl2xd]

Hi Ankit sir, I have been learning SQL by you and other sources as well. I have stuck on a leet code problem, if you have time or you don't mind. Could you please help me with this. Question 1174. Immediate food delivery II I'm getting an error for case 2 WITH first_date AS (SELECT customer_id, MIN(order_date) AS FIRST, customer_pref_delivery_date FROM Delivery GROUP BY customer_id), percentage AS (SELECT CASE WHEN FIRST = customer_pref_delivery_date THEN 'immediate' ELSE 'scheduled' END AS immediate_scheduled FROM first_date) SELECT ROUND(SUM(CASE WHEN immediate_scheduled = 'immediate' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),2) AS immediate_percentage FROM percentage

[@rajeshkumardash611]

@ankitbansal6 Thanks Ankit,I have take your course zero to hero After that I can see the change the way i approach the problem .I remeber getting this problem in some other org and I struggled at that time Here is my solution after trying for some time WITH cte1 AS ( SELECT *, LAG(event_time, 1) OVER (PARTITION BY userid ORDER BY event_time ASC) AS prev FROM events ), cte2 AS ( SELECT userid, event_type, event_time, CASE WHEN DATEDIFF(MINUTE, prev, event_time)

[@ankitbansal6]

Looks good 😊

[@arjundev4908]

WITH CTE AS(SELECT *, LAG(event_time,1,event_time)OVER(partition by USERID ORDER BY event_time) AS NEXTDATE FROM EVENTS),V1 AS( SELECT *, timestampdiff(MINUTE,NEXTDATE,event_time) AS DIFF, SUM(CASE WHEN timestampdiff(MINUTE,NEXTDATE,event_time)

[@PradipChavan-oz5dc]

Sab samaj gaya lekin session id ka group or sum katna hai yaha tak soch ati hi nahi... Har ek complex problem me end part khud nahi soch pata hu... Dekh ke kar leta hu lekin usme maja nahi hai

[@ankitbansal6]

Samay k saath solve hone lagenge. Dherya rakho aur parishram karte raho. Jai shree Ram ♈