Hello Ankit sir , I saw this full video , it was awesome and clear the fundamentals of solving SQL questions . Thank you so much for making this video and made easy for all aspirants who are watching this video and start practicing the SQL questions onwards by seeing this video . Thank you so much . I would request you kindly make some more videos like this so that we can learn , understand and easy to solving any interview level of SQL questions. Thank you sir !

You are great 🙏 I started learning very late in my life journey but yes you the reason sir i do believe i will achieve it 🎯 You are "awesome" sir 🫡

Without CTE select order_date, sum( case when order_date=first_new_cust then 1 else 0 end )as new_customer_count, sum(case when order_date!=first_new_cust then 1 else 0 end) as repeat_customer_count from( select co.*,first_visit_cust.first_new_cust from customer_orders co join ( select co.customer_id,min(co.order_date) as first_new_cust from customer_orders co group by co.customer_id ) first_visit_cust on co.customer_id=first_visit_cust.customer_id order by co.order_id) group by order_date order by order_date;

Good one as always! Here's my approach: ================================== with cust_count as (select *, COUNT(customer_id) over(partition by customer_id order by order_date) [flag] from customer_orders) select order_date, COUNT(case when [flag] = 1 then 1 end) [New customers], COUNT(case when [flag] > 1 then 1 end) [Repeat customers] from cust_count group by order_date ==================================

My approach : WITH ALL_DATA AS( SELECT *,DENSE_RANK()OVER(PARTITION BY user_id ORDER BY created_at ASC) AS no_of_purchases, DENSE_RANK()OVER(PARTITION BY user_id,product_id ORDER BY created_at ASC) AS product_type FROM marketing_campaign), user_level AS( SELECT user_id,max(no_of_purchases) AS Total_purchases,max(product_type) AS Total_Product FROM ALL_DATA GROUP BY user_id) SELECT user_id FROM user_level WHERE Total_purchases>1 AND Total_Product=1

Great video.Kindly give dataset in csv format

with cte as (select *,max(salary) over(partition by dep_id ) as maxi, min(salary) over(partition by dep_id ) as mini from salary) select dep_id, max(case when salary=maxi then emp_name else null end) as emp_name_max_sal, min(case when salary=mini then emp_name else null end) as emp_name_min_sal from cte group by dep_id

with cte as( select customer_id,order_date,row_number() over(partition by customer_id order by order_date) as rn from customer_orders) select order_date, sum(case when rn=1 then 1 else 0 end) as new_customer_count, sum(case when rn>1 then 1 else 0 end) as repeated_count from cte group by 1 my approach

Hi Ankit Sir, Please evaluate this solution, Instead of using a dummy date, i have used filter conditions as it can be possible that we didn't update the values of end date in real life scenario with total_billings as ( select *, dateadd(day,-1,lead(bill_date) over(partition by emp_name order by bill_date)) as last_date from billings) select h.emp_name, sum(bill_rate*bill_hrs) as totalCharges from total_billings t inner join HoursWorked h on t.emp_name=h.emp_name where (h.work_date between bill_date and last_date) or (last_date is null and bill_date<work_date) group by h.emp_name; Will this query cover all the test cases or am i missing something. Kindly let me know?

My Approach: SELECT *, row_number() OVER(ORDER BY company asc, salary asc) as seq_id INTO #temp_tbl FROM employee ORDER BY company, salary; WITH company_median AS ( SELECT company, CEILING(AVG(CAST(emp_id AS DECIMAL(10, 1)))) AS median_row1, FLOOR(AVG(CAST(emp_id AS DECIMAL(10, 1)))) AS median_row2 FROM #temp_tbl GROUP BY company ) SELECT company, avg(salary) as median_salary FROM #temp_tbl WHERE seq_id IN ( SELECT median_row1 FROM company_median UNION SELECT median_row2 FROM company_median ) group by company;

what if there is no clear job description. so how can we make a resume ATS friendly in this situation?

I don't think anything would happen if we use Union as even if there is any duplicate union wilk keep one instance exclude other and if there's none it will only be in gold here it doesn't matter if ther is union or union all well get same results

Plz do it for phython as well Bcoz the way of explanation you have given with exceptional clarity we can't get it from anywhere

Thank you sir i have been waiting of complete sql video for a very long time. 👍👍❤️❤️🙏🙏

emp_list=[('Ankit',10000),('Rahul',12000),('Sumit',14000),('Dheeraj',21000),('Pavan',11000),('Mohit',13000)] def average(emp_mark): avergae_mark = sum(emp_mark)/len(emp_list) return avergae_mark avg_res=average([ele[1] for ele in emp_list]) qualified_emp=[ele for ele in emp_list if avg_res<ele[1]]

emp_list=[('Ankit',10000),('Rahul',12000),('Sumit',14000),('Dheeraj',21000),('Pavan',11000),('Mohit',13000)] def average(emp_mark): avergae_mark = sum(emp_mark)/len(emp_list) return avergae_mark avg_res=average([ele[1] for ele in emp_list]) qualified_emp=[ele for ele in emp_list if avg_res<ele[1]]

Bro where is the data set

My attempt on SQL server: =============================== with partition_flag as (select *, ROW_NUMBER() over(order by (select 1)) [rn], case when category is not null then 1 else 0 end [partition flag] from brands), group_values as (select *, SUM([partition flag]) over(order by [rn]) [group] from partition_flag) select FIRST_VALUE(category) over(partition by [group] order by [group]) [Category], brand_name from group_values ===============================

Need same 2HRS video on python.

Good one as always.. here's my approach on SQL server: ================================== with city_count as (select *, COUNT(city_id) over(partition by city_id order by year(business_date)) [new city count per year] from business_city) select YEAR(business_date), SUM([new city count per year]) from city_count where [new city count per year] = 1 group by YEAR(business_date) ==================================

Good one as always! My attempt on SQL server: =========================================== with flagged_grp as (select *, case when LAG(status, 1, status) over(order by event_time) = status then 0 else 1 end [flag] from event_status), status_grp as (select *, SUM([flag]) over(order by event_time) [grp] from flagged_grp), sorted_grp as (select *, ROW_NUMBER() over(partition by [grp] order by event_time) [row num], COUNT(*) over(partition by grp) [count] from status_grp) select s1.event_time [login], s2.event_time [logout], s1.[count] from sorted_grp s1 join sorted_grp s2 on s1.[row num] = s2.[row num] and s1.grp + 1 = s2.grp and s1.status = 'on' and s2.status = 'off' ===========================================

Great session Ankit bhai , please create more videos like the same, thanks 🙏

select customer_id ,sum (case when product <> 'Photoshop' then revenue end) NonPsRev from m_pra_adobe_transactions where customer_id in (select customer_id from m_pra_adobe_transactions where product = 'Photoshop') group by customer_id

No one would accept during the interview that they follow Ankit Bansal, not even the interviewer 😂

Your videos are really helpful in preparation of SQL. I subscribed your channel to learn more and in detail about SQL.

Where does DISTINCT come?

Very aptly explained. I was asked this window sql question in Gainwell interview. I couldn't answer as I were not aware of it. I searched for these functions and saw your video. It is very easily explained and very precise to the core.

with cte as( select Callerid,Recipientid,Datecalled,Date(Datecalled) as date, max(Datecalled) over(partition by Date(Datecalled)) as max_date, min(Datecalled) over(partition by Date(Datecalled)) as min_date from phonelog) select Callerid,Recipientid,date,count(*) from( select Callerid,Recipientid,date,'last_call' as time from cte where Datecalled=max_date union all select Callerid,Recipientid,date,'1st_call' as time from cte where Datecalled=min_date order by date ) a group by 1,2,3 having count(*)>1 ;

Thanks for this great efforts sirji ❤❤❤

For data analysis sql and Power bi is sufficient

wow, alway mind opening yet simple solutions.

My Solution :- use practise; WITH CTE AS ( SELECT A.poll_id AS poll_id ,MAX(A.correct_option_id) AS winner_option, -- SUM(CASE WHEN A.correct_option_id=poll_option_id THEN amount ELSE NULL END) AS winner_cnt, -- SUM(CASE WHEN A.correct_option_id!=poll_option_id THEN amount ELSE NULL END) AS rem_cnt ROUND(SUM(CASE WHEN A.correct_option_id!=poll_option_id THEN amount ELSE NULL END)/ SUM(CASE WHEN A.correct_option_id=poll_option_id THEN amount ELSE NULL END)) AS single_unit FROM polls P JOIN poll_answers A ON P.poll_id=A.poll_id GROUP BY A.poll_id ) SELECT P.poll_id AS poll_id, user_id, winner_option, amount*single_unit AS winning_mount FROM CTE C JOIN polls P on C.poll_id=P.poll_id AND C.winner_option=P.poll_option_id

with k as (select d.*, case when Gold in (select Silver as medal from events union select bronze as medal from events) then Gold else 'Other' end as Gold_other_medal_winner, count(Gold) over (partition by Gold ) as no_of_golds from events as d) select distinct Gold,no_of_golds from k where Gold<>Gold_other_medal_winner

Sir nice video ❤ To bring twist please use hindi also....

select student_id from ( select * from exams4 where student_id in ( select student_id from exams4 where subject like '%Chem%' and student_id in (select student_id from exams4 where subject like '%Phy%')))m group by student_id having count(distinct marks) = 1; Thank you,Ankit !

Lopez Charles Allen Joseph Hernandez Kenneth

But what about in case of 0.

Thank you, I used this video as a recap before my interview. ☺

My answer is straigh forward and easy to understand select * from ( select supplier_id,product_id,count(*) as cnt,min(record_date) as record_date from ( select *, datediff(day,rw,record_date) as id from ( select *, row_number () over (partition by supplier_id,product_id order by record_date) as rw from stock where stock_quantity<50) a ) b group by supplier_id,product_id,id ) c where cnt>1

Superb explanation

Leetcode problem solved yesterday swap I'd if odd with consecutive name and leave the last row ..

Thank you, Ankit bhai it helps a lot. Keep sharing videos for different questions

This video was amazing for revision purpose Learnt the way how to think like SQL The way you compared and shown the difference of implementing things in Excel and SQL was something new i leaned The way sequence was execution of queries you explained was great now we no more have to remember those.

Next video suggestion is please add some portfolio projects in SQL Bcz most of channels covers basic dummy projects Please add some which we can add in our resume and portfolios

select seat_no from (select *, sum(case when is_empty='Y' then 1 else 0 end) over (order by seat_no rows between 2 preceding and current row) prev_2, sum(case when is_empty = 'Y' then 1 else 0 end) over (order by seat_no rows between 1 preceding and 1 following) as prev_1, sum(case when is_empty ='Y' then 1 else 0 end) over (order by seat_no rows between current row and 2 following) as next_2 from bms1)a where prev_1= 3 or prev_2 =3 or next_2 =3; select seat_no from( select *, seat_no - rn as consecs from (select *, row_number() over ()rn from (select * from bms1 where is_empty = 'Y')a)b)n where consecs in( select consecs from (select *, seat_no - rn as consecs from (select *, row_number() over ()rn from (select * from bms1 where is_empty = 'Y')a)b)c group by consecs having count(consecs) >= 3) Thank you,Ankit !

Hello sir data analytics ka free course ya playlist bta digiye koy jo mujhe help karein please sir

Run the below script to create table and insert sample data. CREATE TABLE covid_cases ( record_date DATE PRIMARY KEY, cases_count INT ); DECLARE @end_date date; DECLARE @loop_date date; DECLARE @num INT; set @end_date = '2021-12-31' set @loop_date = '2021-01-01' WHILE @loop_date <= @end_date BEGIN select @num = floor(RAND()*(1000-500)+500); insert into covid_cases(record_date, cases_count) values(@loop_date, @num); select @loop_date = dateadd(day, 1, cast(@loop_date as date)); END