Please Create more videos .

with cte as ( select * ,seat_no - row_number() over(partition by is_empty order by seat_no) as groups from bms where is_empty = 'Y') select seat_no from (select *, count(seat_no) over(partition by groups) as cnt from cte) t where cnt >=3

tq so much sir, i understand joins

with movie_cte as( select *, ROW_NUMBER() over (partition by left(seat,1) order by left(seat,1)) serial_no from movie ), diff_cte as( select * ,row_number() over (partition by left(seat,1) order by left(seat,1)) rn , serial_no - row_number() over (partition by left(seat,1) order by left(seat,1)) diff from movie_cte where occupancy = 0 ), group_cte as( select left(seat,1) seat, diff, COUNT(1) as free_seat from diff_cte group by left(seat,1), diff having COUNT(1) >= 4) select seat from diff_cte where diff in (select diff from group_cte) and LEFT(seat, 1) in (select seat from group_cte)

select year, COUNT(1) new_cities from ( select YEAR(business_date) year, city_id ,ROW_NUMBER() over(partition by city_id order by YEAR(business_date)) rn from business_city) A where rn = 1 group by year

with cte as (select *, row_number()over(partition by salary, dept_id) as rn from emp_salary) select * from emp_salary where (salary, dept_id) in (select salary, dept_id from cte where rn>1)

with emp_salary as( select * ,DENSE_RANK() over (partition by dep_id order by salary desc) drnk_high ,COUNT(1) over (partition by dep_id) as dept_wise_cnt from emp) select emp_id, emp_name, salary, dep_id, dep_name from emp_salary where drnk_high in (case when dept_wise_cnt > 2 then 3 else dept_wise_cnt end)

I have one doubt that when we are doing partition by hacker id then all hackerid from each should be in one group but outout is still showing partition by date, pls pls clear me

I have only orderdate, productname and price only one table ....hw to achieve all u mentioned?

I solved this problem in very un-optimized way(using subquery) but it was the first approach that came into mind!! with cte1 as ( select *,RANK() over(order by order_date) as date_rank from NamasteSQL.customer_orders ),cte2 as ( select *,case when customer_id in (select customer_id from cte1 where date_rank < c.date_rank) then 1 else 0 end as repeat_flag from cte1 c ) select order_date, sum(case when repeat_flag=1 then 1 else 0 end) as repeated_customers, sum(case when repeat_flag=0 then 1 else 0 end) as new_customers from cte2 group by order_date

WITH CTE AS ( SELECT CUSTOMER_ID, ORDER_DATE, ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE) AS RN FROM CUSTOMER_ORDERS ), CTE2 AS ( SELECT ORDER_DATE, CASE WHEN RN = 1 THEN 1 ELSE 0 END AS NO_OF_NEW_CUST, CASE WHEN RN > 1 THEN 1 ELSE 0 END AS NO_OF_OLD_CUST FROM CTE ) SELECT ORDER_DATE, SUM(NO_OF_NEW_CUST) AS TOTAL_NEW_CUSTOMERS, SUM(NO_OF_OLD_CUST) AS TOTAL_OLD_CUSTOMERS FROM CTE2 GROUP BY ORDER_DATE ORDER BY ORDER_DATE;

with skill_count as (select student_id,count(skill) as cnt from students group by student_id) , desired_skill as (select student_id as idd from students where student_id not in (select student_id as id from students where skill not in ('SQL','Python'))) select distinct student_id from skill_count join desired_skill on skill_count.student_id = desired_skill.idd and cnt=2

select h.emp_id from hospital h join (select emp_id, max(time) as latest_time from hospital group by emp_id) t on h.time=t.latest_time and h.emp_id=t.emp_id where h.action = 'in'

select ticket_id, create_date, resolved_date, ((resolved_date-create_date)-2*(EXTRACT(WEEK FROM resolved_date) - EXTRACT(WEEK FROM create_date)))-holidays as working_days --(resolved_date-create_date) as actual_day --(EXTRACT(WEEK FROM resolved_date) - EXTRACT(WEEK FROM create_date)) as week_diff from (select ticket_id, create_date, resolved_date, count(holiday_date) as holidays from tickets left join holidays on holiday_date BETWEEN create_date and resolved_date and extract(day from holiday_date) not in (0,6) group by ticket_id,create_date,resolved_date) t

with cte as( select *, row_number() over( order by hall_id ,start_Date) as event_id from hall_events ), rcte as( select hall_id, start_Date, end_Date , event_id,1 as flag from cte where event_id=1 union ALL select cte.hall_id , cte.start_date, cte.end_date , cte.event_id, case when (cte.hall_id=rcte.hall_id ) and (cte.start_Date between rcte.start_Date and rcte.end_date or rcte.start_Date between cte.start_Date and cte.end_date) then 0 else 1 end + flag as flag from rcte join cte on rcte.event_id + 1 = cte.event_id ) select hall_id ,flag, min(start_Date) as starting, max(end_date) as ending from rcte group by hall_id, flag

Thanks for all the effort you have put in. It is helping thousands of people! Much appreciated. <3 with grp as (select *,dt-rn as grp from (select *,day(date_value) as dt, row_number() over(partition by state order by date_value) as rn from tasks order by 1)a) select distinct state,first_value(date_value)over(partition by grp) as start_dt ,last_value(date_value)over(partition by grp) as start_dt from grp order by 2;

very much challenged to do on notebook or word file

New think

with cte1 as ( select emp_name, bill_date, lead(bill_Date) over(partition by emp_name order by bill_date) as range, bill_rate from billings) select c.emp_name, SUM(c.bill_rate * h.bill_hrs) as bill from cte1 as c inner join hoursworked h on c.emp_name = h.emp_name where (h.work_date>= c.bill_Date and h.work_date < c.range) or (h.work_date>= c.bill_Date and c.range IS NULL) group by c.emp_name

I came up with this solution on my own, it is working on the test case given in the video, if someone watches my comment and has leetcode premium, can you please verify if this solution gets accepted or not, thanks in advance: with winner_player as ( select match_id, case when first_score > second_score then first_player when second_score > first_score then second_player when second_score = first_score then (case when first_player < second_player then first_player else second_player end ) end as winner_player_id, case when first_score > second_score then first_score when second_score > first_score then second_score when second_score = first_score then (case when first_player < second_player then first_score else second_score end ) end as winner_player_score from matches m ), final_cte as ( select w.winner_player_score, p.player_id, p.group_id, dense_rank() over(partition by p.group_id order by w.winner_player_score desc, p.player_id) as player_rank from winner_player w inner join players p on w.winner_player_id = p.player_id ) select player_id, group_id from final_cte where player_rank = 1

select company_id from( select company_id, user_id from company_users where language in ('English','German') group by company_id, user_id having COUNT(distinct language) = 2)a group by company_id having COUNT(1) > 1

with cte as ( select *,LAG(cases, 1, 0) over (partition by city order by days) prev_cases ,cases - LAG(cases, 1, 0) over (partition by city order by days) case_diff from covid ) select distinct city from covid where city not in (select city from cte where case_diff <= 0)

select student_id from exams group by student_id, marks having COUNT(subject) > 1

My Solution using CTE: with cte as (Select *, case when coalesce(lag(revenue) over (partition by company order by year),0) < revenue then 'Increase' else 'Decrease' end AS status from company_revenue) Select distinct company from cte where company not in (Select company from cte where status = 'Decrease')

One of the best SQL questions to get window function concepts clear. Thanks Ankit!

with cte as( select emp_id, action, rank() over (partition by emp_id order by time desc) as rnk from hospital) select emp_id from cte where rnk=1 and action='in'

with cte1 as ( select *, row_number() over(partition by username order by enddate desc) as rn from userActivity ), cte2 as( select * , max(rn) over(partition by username ) as max_rn from cte1 ) select username, activity, startdate, enddate from cte2 where max_rn = 1 union select username, activity, startdate, enddate from cte2 where rn =2

Yup, Life gets easier!

Hi Ankit, I'm requesting you to check my approach. --Seller and buyer identification With seller_buyer as ( Select *, Case when rn%2=0 then 'buyer' else 'seller' end as seller_buyer_flag From ( Select *, Rank() over (partition by date order by transaction_id asc) as rn From table ) ) ,fraud_detection as ( Select distinct customer_id From seller_buyer Where seller_buyer_flag='buyer' and Customer_id in (select distinct customer_id from seller_buyer where seller_buyer_flag='seller') ) ,final as ( Select * From seller_buyer Where Customer_id not in (select distinct customer_id from fraud_detection) ) ,final_selection as ( Select seller_buyer, Rank() over (partition by seller_buyer order by count(*) desc) as rn From ( Select Concat(S.customer_id,concat(",", b.customer_id) as seller_buyer, s.amount, b.customer_id as buyer From final s Left join final b on s.amount=b.amount and s.date=b.date and s.transaction_id=b.transaction_id-1 and b.seller_buyer_flag='buyer' ) Where buyer is not null Group by 1 ) Select seller_buyer From final_selection Where rn=1 -- Completed

Solution:1 with cte as( Select b.* , segment from booking_table b join user_table u on b.user_id=u.user_id where line_of_business='Flight' and booking_date like '2022-04%'), cte2 as( select segment, count (distinct user_id) as flight2022 from cte group by segment), cte3 as( select segment ,count(*) as totaluser from user_table group by segment ) select c1.segment , totaluser,flight2022 from cte3 c1 join cte2 c2 ON c1.segment=c2.segment Solution:2 with cte as( select * ,rank() over(partition by user_id order by booking_date) as rn from booking_table) select * from cte where rn=1 and line_of_business='Hotel' Solution:3 with cte as( select * ,rank() over(partition by user_id order by booking_date) as rn1, rank() over(partition by user_id order by booking_date desc ) as rn2 from booking_table), cte2 as( select c1.user_id, c1.booking_date as firstdate, c2.booking_date as lastdate from cte c1 join cte c2 on c1.user_id =c2.user_id and c1.rn1=1 and c2.rn2=1) select *, DATEDIFF(day, firstdate,lastdate) as no_of_days from cte2 Solution 4:- with cte as( select b.* ,u.segment from booking_table b join user_table u on b.user_id=u.user_id where booking_date like '2022-%') select segment, sum(case when line_of_business='Flight' then 1 else 0 end )as flight_booking, sum(case when line_of_business='Hotel' then 1 else 0 end )as Hotel_booking from cte group by segment

select month(order_date) as months, count(previous_month) as repeated_customer from (select cust_id, order_date, lag(order_date) over(partition by cust_id order by order_date) as previous_month from transactions ) t where datediff(month, previous_month, order_date) =1 or datediff(month, previous_month, order_date) IS NULL group by month(order_date)

Nice questions.. My approach for last question select a1.event_date , COUNT(a2.user_id) as cnt_users from activity a1 left join activity a2 on a1.user_id = a2.user_id and DATEDIFF(DAY, a2.event_date, a1.event_date) = 1 group by a1.event_date

with cte as(select * from adobe where customer_id in (select customer_id from adobe where product="photoshop") and product !="photoshop") select customer_id,sum(revenue) as total from cte group by customer_id; with cte1 as(select * from adobe where customer_id in (select customer_id from adobe where product="photoshop")) select customer_id,sum(case when product = "photoshop" then 0 else revenue end) as total_rev from cte1 group by customer_id;

Clear & best explanation.

Hey! I really love your videos. Your SQL videos helped me secure a 34 LPA job.

alt solution SELECT experience ,SUM(CASE WHEN COALESCE(sql,100) = 100 AND COALESCE(bug_fixing,100) = 100 AND COALESCE(algo,100) = 100 THEN 1 ELSE 0 END) AS max_score_student ,COUNT(*) AS total_students FROM assessments GROUP BY experience ORDER BY experience;

solution without join WITH batter_tbl AS (SELECT matchid ,batter AS player , "ba" AS p_type FROM cricket_match GROUP BY matchid ,batter) , bowler_tbl AS (SELECT matchid ,bowler AS player , "bo" AS p_type FROM cricket_match GROUP BY matchid ,bowler) ,total_tbl AS ( SELECT * FROM batter_tbl UNION ALL SELECT * FROM bowler_tbl ) SELECT player ,COUNT(DISTINCT matchid) AS total_matches_played ,SUM(IF(p_type="ba",1,0)) AS batting_count ,SUM(IF(p_type="ba",0,1)) AS bowling_count FROM total_tbl GROUP BY player;

select cust_id, sum(revenue) from customer_revenue where product != 'Photoshop' group by cust_id order by cust_id;

df=pd.DataFrame(emp_list,columns=['Name','Salary']) df[df['Salary']>(df['Salary'].mean())]

Hi sir, We are using all function to consider all. The rows in such case in summarize function also while summing the total sales why it's not consolidating all rows as we are using all in the function. I am bit confused here why all working for next line query where calculating avg but not in summarize

I WISH I cuuld have watched this video before interview.

with cte as ( SELECT emp_id,dept_id,name,salary, Dense_rank() over (partition by dept_id ORDER by salary) as rnk , count(*)over (partition by salary) as cnt FROM emp_salary ) select emp_id,dept_id,name,salary from cte where cnt> 1 and rnk = 1