Another way using joins: with cte as (select source_phone_nbr, min(call_start_date_time) as min_time, max(call_start_date_time) as max_time from phone_log group by source_phone_nbr) , filtered_cte as (select p.*, min_time, max_time from cte c left join phone_log p on c.source_phone_nbr = p.source_phone_nbr where c.min_time = call_start_date_time or c.max_time = call_start_date_time) ,result_cte as (select source_phone_nbr, max(case when call_start_date_time = min_time then destination_phone_nbr else null end) as first_number, max(case when call_start_date_time = max_time then destination_phone_nbr else null end) as last_number from filtered_cte group by source_phone_nbr) select source_phone_nbr, case when first_number = last_number then 'Y' else 'N' end as flag from result_cte ;

This is wrong solution ..they asked for least number of products which contribute to atleast 80 or more , so three products should come and you are getting 4 ..

with cte as (select *, datediff(lead(createdat) over(partition by userid order by createdat),createdat) as diff from Transactions_Ecomm) select distinct userid from cte where diff is not null and diff between 1 and 7 ;

Thank you

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 cte.source_phone_nbr,destination_phone_nbr FROM cte JOIN phone_log ON cte.source_phone_nbr=phone_log. source_phone_nbr and (cte.max1=phone_log.call_start_date_time or cte.min1=phone_log.call_start_date_time) ),cte2 as( select source_phone_nbr,COUNT(DISTINCT destination_phone_nbr) as x1 FROM cte1 GROUP BY source_phone_nbr ) select source_phone_nbr, case when x1=1 THEN 'Y' ELSE 'N' END AS flag FROM cte2;

WITH cte AS ( SELECT id, item, ROW_NUMBER() OVER (PARTITION BY item order by id) AS x1 FROM Items_Table ),cte1 as( select item,(id-x1) as result FROM cte ),cte2 as( select item,result,COUNT(*) AS C1 from cte1 GROUP BY item,result ) select DISTINCT(item) FROM cte2 where C1>=3;

with cte as (select *, sum(weight_kg) over(partition by lift_id order by weight_kg) as cumulative_weight from lift_passengers a left join lift b on a.lift_id = b.id) ,cte2 as (select * from cte where cumulative_weight <= capacity_kg) select lift_id, group_concat(passenger_name) as allowed_passengers from cte2 group by lift_id ;

how come class b subjects are coming in reverse order of insertion and class b subjects are coming in order of insertion?

with cte as (select *, row_number() over(order by(select null)) as rwn from reverse_pairs) select a.source, a.destination, a.distance from cte a join cte b on a.source = b.destination where a.rwn < b.rwn;

Nice explanation. Here is my solution with cte as (select name, sales, round((sum(sales) over(order by sales desc rows between unbounded preceding and current row)/ sum(sales) over())*100, 2) as sales_percent from walmart_sales) select * from cte where sales_percent <=90;

with cte as( select customer_id FROM customer_order GROUP BY customer_id HAVING COUNT(order_id)>5 ),cte1 as( select cte.customer_id,order_amount FROM cte JOIN customer_order ON cte.customer_id=customer_order.customer_id ) select customer_id,ROUND(AVG(order_amount),2) as avg_ord FROM cte1 GROUP BY customer_id;

with cte as( select firstname,lastname,phone,mail,MAX(id) as id FROM emp_loyee GROUP BY firstname,lastname,phone,mail ) select id,firstname,lastname,phone,mail FROM cte ORDER BY id;

with cte as( select Customer.*,ROW_NUMBER()OVER(PARTITION BY FirstName,LastName,Country) as x1 FROM Customer ) select ID,FirstName,LastName,Country FROM cte where x1=1 ORDER BY ID;

sir plese provide dml commands

with cte as( select Products.* FROM Products where change_date<= '2019-08-16' ),cte1 as( select product_id,new_price,change_date FROM(select product_id,new_price,change_date, ROW_NUMBER()OVER(PARTITION BY product_id ORDER BY change_date DESC) as x1 FROM cte) as es where es.x1=1 ) select product_id,new_price as price FROM cte1 UNION select product_id,10 FROM Products where product_id NOT IN (select product_id FROM cte1); leetcode accepted solution

with cte as( select vehicle.*,ROW_NUMBER()OVER() as x1 FROM vehicle ),cte1 as( select * FROM cte where company is not null ),cte2 as( select company,car,x1,COALESCE(LEAD(x1-1)OVER(),(select COUNT(*) FROM vehicle)) as x2 FROM cte1 ) select cte2.company,cte.car FROM cte JOIN cte2 ON cte.x1 between cte2.x1 and cte2.x2;

with cte as( select emp_id,name,CONCAT(salary,REPEAT(0,5-LENGTH(salary))) as x1 FROM employee_salary ),cte1 as( select AVG(x1) as accurate_average FROM cte ),cte2 as( select AVG(salary) AS received_average FROM employee_salary ) SELECT CEIL( (SELECT accurate_average FROM cte1)- (SELECT received_average FROM cte2) ) AS diff_average;

with cte as( select stadium.*,ROW_NUMBER()OVER() as c1,DAY(visit_date) as d1 FROM stadium where people>100 ),cte1 as( select id,visit_date,people,(d1-c1) as e FROM cte ),cte2 as( select e FROM cte1 GROUP BY e HAVING COUNT(id)>=3 ) select id,visit_date,people FROM cte1 JOIN cte2 ON cte1.e=cte2.e; is it correct sir?

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 KZbinr has this content on view on KZbin.

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 - kzbin.info/aero/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🙏