Thanks @Ayushi. You are a champ and your support means a lot. Keep doing great things. I recommend everyone to check the Ayushi channel. She has amazing content and you will be mesmerized by her consistency and quality.
I came across your work on LinkedIn, and i reached here. I am an aspiring Data Engineer and this is really helpful when it comes to pave a path for switching to a product based company. I got to learn alot from this video.
with cte as (select *,row_number() over(order by timestamp) as rn from tab) select t1.actor,t1.director from cte t1,cte t2, cte t3 on t1.actor=t2.actor and t2.actor=t3.actor and t1.director=t2.director and t2.director=t3.director and t1.rn+1=t2.rn and t2.rn+1=t3.rn
This is really helpful for aspiring data engineers.. could you please keep uploading more such content.. May be next rounds of interview.. thanks again :)
For the first query the solution what he provided is not correct i guess. In that case the output is going to be printed based on count(*) value irrespective of common occurrence. @TheBigDataShow could you plz provide the correct query for it
It will be one day @Shweta. I just have to keep working hard, learn the stuff and keep helping others in whatever capacity I can. Definitely, one day it will do wonders. I am confident about that 😀. Thank you for the kind words and keep learning and please share video with your friends who are into Data Engineering.
Great work!, this is very insightful and informative for aspiring data engineers. Can you please also do mock interviews for advanced rounds like designing data pipelines.
@ankur I'm not sure how the lead solution works for actordirector problem because in the solution that was provided by interviewee is comparing only weights and not consecutiveness but this query will work only for those 3 records. here is the sample data and query that gives the wrong result. with weights as ( select 1 as id, 73 as wt union all select 3 as id, 73 as wt union all select 4 as id, 73 as wt union all select 5 as id, 80 as wt union all select 6 as id, 80 as wt union all select 7 as id, 80 as wt ), weights_modified as ( select * , lead(wt,1) over(order by id) as next_1, lead(wt,2) over(order by id) as next_2 from weights ) select wt from weights_modified where wt = next_1 and next_1 = next_2
i think below solution will work SELECT wt FROM ( SELECT id, wt, LEAD(wt,1) OVER(ORDER BY id) as nxt1, LEAD(wt,2) OVER(ORDER BY id) as nxt2, LAG(wt,2) OVER(ORDER BY id) as prv1, LAG(wt,2) OVER(ORDER BY id) as prv2 FROM Stadium ) t WHERE (wt = nxt1 AND nxt1 = nxt2) OR (wt = nxt1 AND nxt1 = prv1) OR (wt = prv1 AND prv1 = prv2)
No the question is we want wt in consecutive manner as three time We cant just use count function otherwise it will give count of all the wt which is present in the dataset for eg here 73 is 3 times what if there is another id 10 and wt 73 so the count function considers id 10 also
