Thanks for sharing such questions ❤ Please try do more such videos. 👍

Its multiply by 7 % not just multiply by 7 . 10 lakh multiply by 7 is 70 lakhs not 70 k.

with cte as( select *, lag(free,1) over (order by seat_id) as prev_seat, lead(free, 1, free) over (order by seat_id) as next_seat from cinema ) select seat_id from cte where free = 1 and ( prev_seat = 1 and next_seat = 1) or (prev_seat = 1 and next_seat= 0) or (prev_seat = 0 and next_seat = 1)

Assignment solution , used flag to cancel out the public holiday falling on weekend , so that we can only count genuine holidays to minus it out. with infoCte as (select *, case when datepart(weekday,holiday_date) in (1,7) then 0 else case when holiday_date is null then 0 else 1 end end as realHolidayFlag from tickets left join holidays on holiday_date between create_date and resolved_date), finalCte as (select ticket_id,create_date,resolved_date,sum(realHolidayFlag) as holidays from infoCte group by ticket_id,create_date,resolved_date) select ticket_id, create_date, resolved_date, datediff(day,create_date,resolved_date) as total_days, 2*datediff(week,create_date,resolved_date) as totalWeekend, holidays, datediff(day,create_date,resolved_date)-2*datediff(week,create_date,resolved_date)-holidays as TotalBusinessDays from finalCte

My approach: with cte as ( select * ,lag(end_date,1) over(partition by hall_id order by start_date) as prev_end_date, case when start_date <=lag(end_date,1) over(partition by hall_id order by start_date) then 0 else 1 end as flg from hall_events ) , cte2 as ( select * ,sum(flg) over(partition by hall_id order by start_date) as grp_id from cte ) select hall_id, min(start_date) as start_date, max(end_date) as end_date from cte2 group by hall_id,grp_id order by hall_id

Bhai video mai clarify rakho naa mere just specs waale ko stress aata hai

Coudl you please add the DDL for the question

Where can i find the answer to that question ?

My approach: with cte as ( select section, number, rank() over(partition by section order by number desc) as sec_rnk, max(number) over(partition by section) as sec_max from section_data ) select top 4 section, number from cte where sec_rnk <=2 order by sum(number) over(partition by section) desc, sec_max desc

Thank you very much, please make more such videos, very very helpful!

with a as ( select t.ticket_id, t.create_date, t.resolved_date, count(h.holiday_date) as number_of_hol from tickets t left join holidays h on h.holiday_date between t.create_date and t.resolved_date group by t.ticket_id, t.create_date, t.resolved_date) select *, datediff(resolved_date,create_date)-number_of_hol+week(resolved_date)- week(create_date) as bday from a;

with busy_year as( select *,row_number() over (partition by city_id order by business_date) as bus_date from business_city) ,busyy_year as (select *,count(1) from busy_year where bus_date=1 group by business_date) select year(business_date) as year, count(1) as no_of_cities from busyy_year group by year(business_date);

with cte as ( select origin,destination,sum(case when oneway_round='O' then ticket_count else ticket_count*2 end) as tickets_sold from tickets group by origin,destination) select origin,destination,tickets_sold from cte where tickets_sold=(select max(tickets_sold) from cte) . Thank you Ankit .

with cte as ( select *, (lag(free,1) over( order by seat_id)*free) as lde, (lead(free,1) over( order by seat_id)*free) as rwn from cinema) Select * from cte where lde =1 or rwn =1

This was definately an amazing video. Althought, I had an experience of solving similar approach thru your videos, but the test cases added bit more challenges and extra steps added to it, but it was easy to follow later steps as we alreaady built the main query and it was all about adjusting as per test cases. my final query: With Seniors_Selection as ( Select *, sum(salary) over(order by salary asc rows between unbounded preceding and current row) as Running_Salary_Sum from candidates where positions = 'Senior' ) , Seniors_Hiring_Budget as ( Select count(*) as Seniors, sum(salary) as Amount_used, case when Sum(salary) is null then 50000 else 50000-sum(salary) end as Amount_Remaining from Seniors_Selection where Running_Salary_Sum <= 50000 ), juniors as ( Select *, sum(salary) over(order by salary asc rows between unbounded preceding and current row) as Running_Sum from candidates where positions = 'junior') , Juniors_Hired as ( Select count(*) as Juniors_Count from juniors where Running_Sum <= (Select Amount_Remaining from Seniors_Hiring_Budget) ) Select j.Juniors_Count as Juniors, S.Seniors from Seniors_Hiring_Budget s join Juniors_Hired j on 1=1;

Hey Ankit, Can you please help me with how to create this dimension table in mysql I am done with all the script following your video but I got stuck at last point when we need to create the physical table. This query is not working in mysql....please help. SELECT row_number() over(order by cal_date asc) as id , * into calendar_dimension from cte ; SET SESSION cte_max_recursion_depth = 1000000; Thanks in advance

with cte as ( select *, lag(cases) over(partition by city order by days) as lag_cases, (case when lag(cases) over(partition by city order by days) >= cases then city end) as city_new from covid ) select * from covid where city not in (select city_new from cte where city_new is not null)

First and last method is really clever. kudos to you for solving this problem!

WITH CTE AS (SELECT user_id, game_id, max(case when interaction_type is not null then 1 else 0 end) as flag1, sum(case when interaction_type = 'custom_typed' then 1 else 0 end) as flag2 FROM game_actions GROUP BY user_id, game_id), cte2 as (SELECT game_id, case when sum(flag1) = 0 and sum(flag2) = 0 THEN 'No Social Interaction' when sum(flag1) = 1 and sum(flag2) = 0 or sum(flag1) = 1 and sum(flag2) = 1 THEN 'One sided interaction' when sum(flag1) = 2 and sum(flag2) = 0 THEN 'Both sided Interaction without custom' when sum(flag1) = 2 and sum(flag2) = 1 THEN 'Both sided Interaction with custom' END as int_flag , count(1)over() as tot_games FROM CTE GROUP BY game_id) SELECT int_flag, ROUND((COUNT(int_flag) * 100.0 / tot_games), 2) as dist FROM cte2 GROUP by int_flag, tot_games;

with cte as(select *, case when (cases - lag(cases,1,0) over(partition by city order by days asc)) > 0 then 1 else 0 end as flag from covid) select city from cte group by city having count(*) = sum(flag)

Hi Ankit I did like this with cte as ( select * , first_value(order_date) over(partition by cust_id) as first_order_date, last_value(order_date) over(partition by cust_id) as last_order_date from transactions ), cte2 as ( select month(order_date) as months, sum(case when (order_date < first_order_date) then 1 else 0 end ) as l1, sum(case when (order_date > first_order_date) then 1 else 0 end ) as l2 from cte group by month(order_date) ) select c2.months,(c2.l1+c2.l2) as cx from cte2 c2 join cte2 c3 on c2.months = c3.months

Here is my approach using correlated subquery- select seat_id from cinema a where exists (select 1 from cinema b where a.free=1 and b.free=1 and (a.seat_id+1=b.seat_id or a.seat_id-1=b.seat_id)) another simpler method- with cte as ( select seat_id from cinema where free=1 ) select seat_id from cte where seat_id-1 in (select seat_id from cte) or seat_id+1 in (select seat_id from cte);

very good session sir

SELECT customer_name, case when diff = 0 then customer_name else SUBSTRING(customer_name,1,first_space-1) end as first_name, case when second_space = 0 then NULL else SUBSTRING(customer_name,first_space+1,abs(second_space - first_space - 1)) end as middle_name, case when first_space = 0 then NULL WHEN second_space = 0 and first_space > 0 then SUBSTRING(customer_name,first_space+1,abs(LEN(customer_name) - first_space)) ELSE SUBSTRING(customer_name,second_space+1,abs(LEN(customer_name) - second_space)) END as last_name FROM (SELECT customer_name, REPLACE(customer_name,' ','') as space, LEN(customer_name) - len(REPLACE(customer_name,' ','')) as diff, charindex(' ',customer_name) as first_space, charindex(' ',customer_name,charindex(' ',customer_name)+1) as second_space FROM customers)a

Nice question ankit😃. Here is my solution: with cte as( select distinct company_id, user_id from company_users group by company_id,user_id having count(user_id)>=2 and sum( case when language in ('English','German') then 1 else 0 end )=2 ) select company_id from cte group by company_id having count(user_id)>=2

Hi Ankit vey interesting question Here is my solution for this with cte as (select free , seat_id , lag(seat_id)over() , seat_id - lag(seat_id)over() as diff from cinema where free <> 0) --select * from cte , gte as (select * , lead(diff)over() as diff_2 from cte) --select * from gte select seat_id from gte where diff <> 2 or diff_2 <> 2

Easiest solution select student_id from ( select student_id,marks,count(*) as cnt from exams group by student_id,marks having count(*) =2 )a

Hi Ankit thanks for uploading videos ..Need same way datascience videos it's helpful

Thanks, Ankit for this brainstorming question, MY QUERY SELECT order_date, Count(CASE WHEN rnk = 1 THEN cnt END) AS "New Customer", Count(CASE WHEN rnk > 1 THEN cnt END) AS "Old Customer" FROM ( SELECT order_date, customer_id, DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS rnk, COUNT(*) OVER (PARTITION BY customer_id, order_date) AS cnt FROM customer_orders1 ) A GROUP BY order_date;

Good 👍👍👍👍❤❤

Hey Ankit, what YOE was this question asked for? If u have any idea

Hi Ankit thanks for the question and your approaches! This is my take at the problem: with mycte as ( select *, case when (free = 1 and prev_free = 1) or (free = 1 and next_free = 1) then 'Yes' else 'No' end as seat_status from ( select *, lag(free,1) over(order by seat_id) as prev_free, lead(free,1) over(order by seat_id) as next_free from cinema ) as x ) select seat_id from mycte where seat_status = 'Yes'

Do you think this roadmap its works 2024?

As of today, it is no longer free. It requires subscription :(

@ankitbansal6 sir please paste the script in the description or comments or provide a link.. so that it would be easy for the learners to practice. thank you

Here is my solution: with cte as ( select * , sum(salary) over(partition by experience order by salary) as rolling_salary from candidates ) , cte2 as ( select *, sum(salary) over( order by (case when experience='Senior' then 0 else 1 end), rolling_salary) as rolling_salary2 from cte where rolling_salary <=70000 ) select emp_id, experience, salary from cte2 where rolling_salary2<=70000 order by emp_id

Thanks sir 🙏

Hello Ankit I solved this question using different approach, but may not be easiest way 😁😁 : with cte as (select userid,count(distinct purchasedate)cnt from purchase_history group by userid,purchasedate), cte1 as ( select userid from cte group by userid having sum(cnt)>=2),cte2 as( select p.userid,p.productid,p.purchasedate from cte1 c join purchase_history p on c.userid = p.userid),cte3 as (select userid from cte2 group by userid,productid having count(*) = 2) select distinct userid from cte2 where userid not in (select * from cte3)

Thanks for sharing this question, Here is my approach : with cte as (select *,row_number() over() as rn, seat_id-row_number() over() as grp from cinema where free=1) select seat_id from cte where grp in (select grp from cte group by grp having count(1)>=2);

LAST QUESTION: with cte as ( select user_id,event_name,event_date, lag(event_date) over(partition by user_id order by user_id) as next_date from activity ) select event_date , sum(case when date_1 = event_date then 1 else 0 end) as count_users from ( select event_date, case when date_sub(event_date, interval 1 day)=next_date then event_date else 0 end as date_1 from cte ) a group by event_date

At 12:17 using c2.seat_id - c1.seat_id = 1 also gives the same output

does someone know from where to get all these questions by anki bansal in pdf?

WITH CTE AS( SELECT seat_id, free, LAG(free) OVER(ORDER BY seat_id) AS prev_seat , LEAD(free) OVER(ORDER BY seat_id) AS next_seat FROM cinema) SELECT seat_id FROM CTE WHERE free=next_seat OR free=prev_seat; EASY approach

Actual ultimate thing is providing the create table and insert data in the description.... Huge thanks to Guruji.

This can be solved with self join, row number method, lead/lag and also advanced aggregation

SELECT GOLD AS PLAYERNAME, COUNT(1) AS NO_OF_GOLDMEDALS FROM EVENTS GROUP BY GOLD HAVING GOLD NOT IN ( SELECT SILVER FROM EVENTS UNION ALL SELECT BRONZE FROM EVENTS)

Thanks for posting such useful questions bro . Here is my approach in Postgres : WITH CTE AS (SELECT * ,ROW_NUMBER()OVER(ORDER BY SEAT_ID) AS RN FROM CINEMA WHERE FREE=1) SELECT SEAT_ID FROM CTE WHERE SEAT_ID -RN=1