Popular Posts - SQL Interview Query 21 | SQL Problem Level "MEDIUM"

[@vivekmishra5876]

Great break down and step wise build up to solution ❤️

[@madhustips8304]

great explanation.big fan of your sql stuff.really i got good command on sql only because of you toufiq.hats off to you

[@Anishcko13]

Appreciate the efforts and consistency. 👍

[@abhijitvernekar593]

you said it was hard , when I tried i felt it was the most easy one

[@Hsalz]

Loving this series

[@nirmalpatel3430]

nice query use of date and timestamp function

[@MdZeeshan-m9u]

Thank you so much for your efforts

Awesome!

[@fathimafarahna2633]

As always BEST 😊

[@ehsanshakeri620]

thank you

[@VinoyJosephVadakkel]

👌👌👌👍

[@monasanthosh9208]

MySQL solution for Freshers (Easy to understand) With CTE as (Select *,timediff(session_endtime,session_Starttime) as Total_Time from User_Sessions), CTE1 as (Select C.Session_id,C.User_id,C.Session_Starttime,C.Session_endtime,C.Platform,P.post_id,P.Perc_viewed,C.Total_time from CTE C join Post_views P on C.Session_id=P.Session_id), CTE2 as (Select *,time_to_sec(Total_Time) as Tot_Time_sec from CTE1), CTE3 as (Select *,perc_Viewed/100*Tot_Time_sec as PVT from CTE2), CTE4 as (Select Post_id,Sum(PVT) as SPVT from CTE3 group by Post_id) Select * from CTE4 where SPVT>5 order by SPVT;

[@DEwithDhairy]

PySpark 30 Days Challenge of these problems : kzbin.info/aero/PLqGLh1jt697xzk9LCLL_wFPDZi_xa0xR0

[@shivinmehta7368]

with cte as ( select session_id, cast(EXTRACT(EPOCH FROM session_endtime-session_starttime) as int) as duration from user_sessions) select post_id,sum(ts) as vt from ( select a.*,duration, perc_viewed* duration/100.00 as ts from post_views a left join cte b on a.session_id=b.session_id ) x group by 1 having sum(ts)>5 order by 1 desc

[@sammail96]

Oracle sql: 1st approach to get date diff in seconds: select session_id, (extract( hour from (session_endtime - session_starttime))*3600 + extract( minute from (session_endtime - session_starttime))*60 + extract( second from (session_endtime - session_starttime))) as total_duration from user_sessions; 2nd approach: SELECT session_id, (CAST(session_endtime AS DATE) - CAST(session_starttime AS DATE)) * 86400 AS total_duration FROM user_sessions

[@Yashaswini210]

When u start new batch

[@Satish_____Sharma]

Solution using mysql with cte as (SELECT p.*,time_to_sec(timediff(u.session_endtime,u.session_starttime)) as timediff, (perc_viewed/100)*time_to_sec(timediff(u.session_endtime,u.session_starttime)) as viewed_time FROM tfq.post_views p left join user_sessions u on p.session_id=u.session_id) select post_id,sum(viewed_time) as total_viewed from cte post_id group by post_id having sum(viewed_time) >5

[@blse2000]

SQL SERVER SOLUTION: select post_id,cast(sum(t) as numeric(10,1)) as total_viewtime from (select us.session_id, user_id, session_starttime, session_endtime, platform, post_id, perc_viewed, DATEDIFF(second,session_starttime,session_endtime) as diff_in_sec, (cast(pv.perc_viewed as float)/100)*(DATEDIFF(second,session_starttime,session_endtime)) as t from user_sessions us inner join post_views pv ON pv.session_id = us.session_id) x group by post_id having sum(t)> 5

[@chetanmaurya8557]

this problem was easy to solve with cte as ( select *,datediff(SECOND,session_starttime,session_endtime) total_diff, concat(datediff(SECOND,session_starttime,session_endtime)/60,' min',' ',datediff(SECOND,session_starttime,session_endtime)%60,' sec') as diff from user_sessions) select pv.post_id,sum((pv.perc_viewed/100.0)*c.total_diff) total_view_time from cte c join post_views pv on c.session_id=pv.session_id group by pv.post_id having sum((pv.perc_viewed/100.0)*c.total_diff)>=5

[@sapnasaini851]

MySQL Solution- with cte1 as ( select us.session_id, pv.post_id, pv.perc_viewed, time_to_sec(timediff(session_endtime , session_starttime)) as time from user_sessions us join post_views pv on us.session_id = pv.session_id ) select post_id, sum((time*perc_viewed)/100) as total_view_time from cte1 group by post_id having total_view_time > 5 order by 2

[@Alexpudow]

select post_id, sum(datediff(second,session_starttime,session_endtime)*1.0/100*perc_viewed) s from user_sessions u join post_views p on u.session_id=p.session_id group by post_id having sum(datediff(second,session_starttime,session_endtime)*1.0/100*perc_viewed)>5

[@Shubham-g199]

mysql with cte as( select s.session_id,post_id,perc_viewed, time_to_sec(timediff((session_endtime),(session_starttime))) as time_tkn, time_to_sec(timediff((session_endtime),(session_starttime)))*perc_viewed/100 as time_spend from user_sessions s join post_views p on s.session_id=p.session_id) select post_id, sum(time_spend) as time_spend_on_post from cte group by post_id having sum(time_spend)>5;

[@saiswaroop3570]

WITH CTE AS ( select U.SESSION_ID, U.SESSION_STARTTIME, P.POST_ID, U.SESSION_ENDTIME, U.SESSION_ENDTIME - U.SESSION_STARTTIME TIME_DIFFERENCE, EXTRACT(MINUTE FROM U.SESSION_ENDTIME - U.SESSION_STARTTIME)*60 + EXTRACT(SECOND FROM U.SESSION_ENDTIME - U.SESSION_STARTTIME) TOTAL_SECONDS, P.PERC_VIEWED from user_sessions U LEFT JOIN POST_VIEWS P ON U.SESSION_ID=P.SESSION_ID ) select post_id, sum((perc_viewed/100)*total_seconds) as per_seconds from cte group by post_id having sum((perc_viewed/100)*total_seconds)>5