Видео

with cte as( select exam_id,MAX(score) as max_scored,MIN(score) as min_scored FROM exam GROUP BY exam_id ),cte1 as( select exam.*,max_scored,min_scored FROM cte JOIN exam ON cte.exam_id=exam.exam_id ),cte2 as( select student_id, COUNT(CASE when score!=max_scored and score!=min_scored THEN 1 ELSE NULL END) AS X1 FROM cte1 GROUP BY student_id HAVING x1>0 ) select cte2.student_id,student_name FROM cte2 JOIN student ON cte2.student_id=student.student_id;

with cte as( select * FROM fb_friend_requests where request_action="sent" ),cte1 as( select cte.*,fb_friend_requests.request_action as responce FROM cte LEFT JOIN fb_friend_requests ON cte.user_id_sender=fb_friend_requests.user_id_sender and cte.user_id_receiver=fb_friend_requests.user_id_receiver and fb_friend_requests.request_action= 'accepted' ORDER BY request_date ),cte2 as( select request_date,count(*) as t, COUNT(CASE when responce='accepted' THEN 1 ELSE NULL END) as x1 FROM cte1 GROUP BY request_date ) select request_date,(x1/t)*100 as acceptance_rate FROM cte2;

with cte as( select user_id1 FROM friends where user_id2=1 union select user_id2 FROM friends where user_id1=1 ),cte2 as( select page_id FROM cte JOIN likes ON cte.user_id1=likes.users_id where page_id not IN(select page_id FROM likes where users_id=1) ) select DISTINCT(page_id) FROM cte2;

with cte as( select * FROM flipkart_transactions ORDER BY user_id,created_at ),cte1 as( select cte.*,LAG(created_at)OVER(PARTITION BY user_id ORDER BY created_at) as c1 FROM cte ),cte2 as( select user_id,DATEDIFF(created_at,c1) as day1 FROM cte1 ) select user_id FROM cte2 where day1<=7 GROUP BY user_id HAVING COUNT(*)>=1;

with cte as( select hacker_id,COUNT(DISTINCT challenge_id) as c1 FROM challenges GROUP BY hacker_id ),cte1 as( select c1 FROM cte GROUP BY c1 HAVING COUNT(*)>1 and c1!=(select MAX(c1) FROM cte) ),cte2 as( select * FROM cte where cte.c1 NOT IN(select c1 FROM cte1) ) select cte2.hacker_id,c1,name FROM cte2 JOIN hackers on cte2.hacker_id=hackers.hacker_id ORDER BY c1 DESC;

with cte as( select tasks.*,DAY(end_date) as date1,ROW_NUMBER()OVER() as date2 FROM tasks ),cte1 as( select task_id,start_date,end_date,(date1-date2) as d1 FROM cte ),cte2 as( select d1,COUNT(*) AS no_of_days,MIN(start_date) as start_date,MAX(end_date) as end_date FROM cte1 GROUP BY d1 ) select start_date,ROW_NUMBER()OVER() as row_1,end_date,no_of_days FROM cte2;

with cte as( select company_name, COUNT(CASE when year=2023 THEN 1 ELSE NULL END) AS 2023_sale, COUNT(CASE when year=2024 THEN 1 ELSE NULL END) AS 2024_sale FROM car_launches GROUP BY company_name ) select company_name, CASE when 2024_sale>2023_sale THEN CONCAT(company_name,' ',"launched",' ',ABS(2024_sale-2023_sale),' ',"more car in the current") when 2023_sale>2024_sale THEN CONCAT(company_name,' ',"launched",' ',ABS(2024_sale-2023_sale),' ',"less car in the current") END AS result FROM cte;

with cte as( select customer_id FROM order_tab GROUP BY customer_id HAVING COUNT(order_id)=1 ) select MAX(customer_id) as max1 FROM cte;

with cte as( select voter,(1/COUNT(DISTINCT candidate)) as t FROM voters GROUP BY voter ),cte1 as( select cte.*,candidate FROM cte JOIN voters ON cte.voter=voters.voter ),cte2 as( select candidate,SUM(t) as sum1 FROM cte1 GROUP BY candidate HAVING candidate is not null ) select candidate FROM cte2 ORDER BY sum1 DESC limit 1;

with cte as( select source_phone_nbr,MAX(call_start_date_time) as max1,MIN(call_start_date_time) as min1 FROM phone_log GROUP BY source_phone_nbr ),cte1 as( select phone_log.* FROM phone_log JOIN cte ON phone_log.source_phone_nbr=cte.source_phone_nbr and ((phone_log.call_start_date_time=cte.max1) or (phone_log.call_start_date_time=cte.min1)) ),cte2 as( select cte1.*,LEAD(destination_phone_nbr)OVER(PARTITION BY source_phone_nbr) as x1 FROM cte1 ORDER BY source_phone_nbr ),cte3 as( select source_phone_nbr, case when destination_phone_nbr=x1 THEN 'Y' when destination_phone_nbr!=x1 THEN 'N' END AS flag FROM cte2 ) select source_phone_nbr,flag FROM cte3 where flag is not null;

with cte as( select tab_sequence.*,ROW_NUMBER()OVER(PARTITION BY group1) as x1 FROM tab_sequence ),cte1 as( select cte.*,(sequence-x1) as x2 FROM cte ) select group1,MAX(sequence) as max1,MIN(sequence) as min1 FROM cte1 GROUP BY group1,x2;

with cte as( select ride_id,HOUR(ride_date) as h1,ride_distance,ride_fare FROM rides where HOUR(ride_date) BETWEEN 7 and 10 or HOUR(ride_date) BETWEEN 17 and 20 ),cte1 as( select 'peak hour' as hour_type,COUNT(*) as total_rides,AVG(ride_fare) as avg_fare FROM cte ),cte2 as( select ride_id,ride_fare FROM rides where ride_id NOT in (select ride_id FROM cte) ),cte3 as( select 'non_peak hour' as hour_type,COUNT(*) AS total_rides,AVG(ride_fare) as avg_fare1 FROM cte2 ) select * FROM cte1 union select * FROM cte3;

with cte as( select YEAR(sale_date) as y1,MONTH(sale_date) as m1,product_id,quantity from product_sales where YEAR(sale_date)=2099 ),cte1 as( select product_id,m1,SUM(quantity) as sum1 FROM cte GROUP BY product_id,m1 ORDER BY product_id,m1 ),cte2 as( select product_id,m1,sum1,COALESCE(LAG(sum1)over(PARTITION BY product_id ORDER BY m1),0) as prev_sale FROM cte1 ) select product_id,m1,sum1,((sum1-prev_sale)/prev_sale)*100 as x1 FROM cte2;

SELECT Lift_Id, Passenger_Name, Weight_Kg, SUM(Weight_Kg) OVER (ORDER BY Lift_Id) AS x1 FROM lift_passengers;

Bro bring a complete video around 1 hr. Why view is required. Types of view in SQL server. Why view is required in power bi while importing data from SQL server. Limitations of view. Other used cases of view. I will be waiting for this content. This is a suggestion from my side. No RUclipsr has this content on view on RUclips.

U are the best

Superb explanation 👌 👏 👍

what if you have 10 subject . are you going to hardcode it. That means will you write 10 times case statement. Please bring a different approach.

Superb explanation 👌 👏 👍

Bro, I am not getting same output, After executing select statement inside cte1. I am getting B English 1 For your B English 5

Is this solution correct we have with cte as ( select *, COUNT(company) over (order by car_id)as aa from vehicle ) select *,FIRST_VALUE(company) over (partition by aa order by aa) from cte

SQL Interview Bootcamp: Questions & Practice Exercises - ruclips.net/p/PLRj6ORzjeGQ531TsgG_twzlY0796sVq1Z&si=DQ0Oci3r6qieUHgM

Hi @Data Project Hub what about this code could tell me is this approch is right with cte as( select *,sum(weight_kg) over(partition by lift_id order by weight_kg) as total_sum from l) select lift_id,STRING_AGG(passenger_name,',')as passeneger_list from cte where lift_id=1 and total_sum <300 or lift_id =2 and total_sum <350 group by lift_id

check this out! select lift_id, STRING_AGG(passenger_name, ', ' ORDER BY weight_kg ) AS aggregated_names_2 from ( select * from ( select *, sum(weight_kg) over(partition by lift_id order by weight_kg) as total_W from ( Select * from lift_passengers as L1 join lift as L2 on L1.Lift_id = L2.id)x)y where capacity_kg > total_W) z group by lift_id 😉

Very good question.👍👍👍👍

Thank you for sharing! here's my attempt on SQL server: ================================== with max_min as (select * from (select *, MAX(score) over(partition by exam_id order by exam_id) [Max score], MIN(score) over(partition by exam_id order by exam_id) [Min score] from exam) s1 where case when score > [Min score] and score < [Max score] then 'Y' else 'N' end = 'Y'), exam_no as (select student_id, COUNT(student_id) [num exams] from exam group by student_id) select distinct s.student_id, student_name from max_min m join exam_no e on m.student_id = e.student_id join student s on s.student_id = m.student_id where [num exams] >= 1 ==================================

;WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (partition by customer_id order by order_date) rn ,LAG(status,1) OVER(partition by customer_id order by order_date) lg_id ,LAG(order_date,1) OVER(partition by customer_id order by order_date) lg_id1 FROM customer_orders ) SELECT customer_id,DATEDIFF(DAY,lg_id1,order_date) as DaysTook FROM CTE WHERE lg_id IS NOT NULL and lg_id1 is not null and ((status='Delivered' and lg_id='Shipped')) and lg_id1<order_date

Very good question. Keep it up👍👍👍👍👍👍

My Solution. WITH CTE AS (SELECT c.customer_id, c.customer_name, o.order_id, o.order_date, o.order_amount, MIN(order_date) OVER(PARTITION BY o.customer_id ORDER BY c.customer_id) AS first_order_date FROM customers c JOIN order_details o ON c.customer_id = o.customer_id) SELECT TOP 3 customer_id, customer_name, SUM(order_amount) AS total_amount FROM CTE WHERE month(order_date) = month(first_order_date) GROUP BY customer_id, customer_name ORDER BY total_amount DESC

Thank for this example

Thanks for sharing sir🙏

select distinct item from (select * , dense_rank() over (partition by item order by id ) as dn from items_table) x where dn >=3 i am getting the same output, is this correct method? please let me know

Also it can be done in cte only..u have used so many...

You make it really easy to understand, Thank you 🙏

You takes very good scenario based questions

Nice

Thanks for the excellent explanation. I solved the same using this query: with cte as (select *, day(CreatedAt) as DAY_NO, coalesce(lag(day(CreatedAt)) over(partition by userId order by CreatedAt),0) as prev_DAY from Transactions_Ecomm), cte1 as (select *, case when (DAY_NO-prev_DAY)<=7 AND prev_DAY <> 0 AND DAY_NO <> prev_DAY then userId else null end as USERID_DIFFERENCE_IN_DAYS from cte) select DISTINCT userId from cte1 where userId=USERID_DIFFERENCE_IN_DAYS;

Thanks for making this video

If I'm not wrong, isn't this a simpler way to solve this: WITH cte as (select *,LEAST(English,Maths,Science,Geography,History,Sanskrit) as LEAST_MARKS from exam) select *, (English+Maths+Science+Geography+History+Sanskrit-LEAST_MARKS)/5 as Output_Marks from cte;

Superb explanation 👌 👏 👍

with cte as( select user_id_send,user_id_receiver_id , max(case when action='sent' then date end )as sent_date , max(case when action='recevied' then date end) as received_date from fb_friend_requests group by user_id_send,user_id_receiver_id) select sent_date ,count(sent_date) ,count(received_date )*1.0 / count(sent_date) *100 as percent_rate from cte group by sent_date

Hey buddy Thanks for the videos Appreciating your honest efforts 🎉

Thanks for sharing, really good stuff

Agr bich bichme hindi mevbhi smjhado to jyada easy rhega or dekhne me bhi excitement rhegi 3:13

Superb explanation 👌 👏 👍

Thank you for the interview questions, And THANK YOU FROM BOTTOM OF MY HEART for providing the create table n insert table in the description

Sir, seeing such scenarios, am getting scared of SQL. how to learn all these advanced concepts from basics? :(

you opened by eyes.. where can i get such interview questions...

Waiting for next video