Видео

Bro pls mention this type of scenario based questions for beginners or for experience it better to learn more

Thanks ❤

Very good problem. Excellent explanation. Thanks 😊

Excellent example.

with cte_1 AS (SELECT *,SUM(salary) OVER(PARTITION BY experience ORDER BY emp_id ) AS cum_sum FROM office), cte_2 AS ( SELECT ('60000'- SUM(salary)) ramount FROM cte_1 WHERE experience='Senior' AND cum_sum <='60000') SELECT * FROM cte_1 WHERE experience='Junior' AND cum_sum <=(SELECT * FROM cte_2) UNION SELECT * FROM cte_1 WHERE experience='Senior' AND cum_sum <='60000' ;

with cte AS (SELECT *,DATE_FORMAT(trans_date,'%y-%m') AS months ,case when state='approved' then 1 ELSE 0 end AS approved_tx_count, case when state='approved' then amount ELSE 0 end AS approved_tx_amount FROM transactions) SELECT months,country,COUNT(*) AS tx_count,SUM(amount) AS tx_amount, SUM(approved_tx_count), max(approved_tx_amount) FROM cte GROUP BY months,country ;

SELECT team,COUNT(*) AS NOofmatch,SUM(case when team=result then 1 ELSE 0 END) AS won, SUM(case when team !=result then 1 ELSE 0 END) AS loss from( SELECT team1 AS team,result FROM cricket_match UNION ALL SELECT team2 as team,result FROM cricket_match) AS k GROUP BY team ORDER BY team ;

with cte AS ( SELECT * , LAG(revenue,1,0) OVER(PARTITION BY company_name ORDER BY year_cal) AS pre_revenue, COUNT(company_name) OVER (PARTITION BY company_name ) AS COUNT_1 FROM company_info ORDER BY company_name,year_cal), cte2 AS ( SELECT *,COUNT(company_name) OVER (PARTITION BY company_name) AS COUNT_2 FROM cte WHERE revenue >= pre_revenue) SELECT * FROM cte2 WHERE COUNT_1 =COUNT_2 ;

Bhai bitwise ke interview sql questions pe video banale

thumbnail output is different than actual output (😇) select to_char(trans_date , 'yyyy-mm'),country, count(state),sum(case when state = 'approved' then 1 else 0 end),sum(amount) from transactions10 group by to_char(trans_date , 'yyyy-mm'),country

my solution with cte as ( select * , case when revenue - (lag(revenue , 1 , 0) over (partition by company_name order by year_cal)) >=0 then 0 else 1 end as diffrence from company_info) select company_name from cte group by company_name having sum(diffrence) = 0

with main as (select *, lag(revenue,1,0) over(partition by company_name order by year_cal) as prev, lead(revenue,1,0) over(partition by company_name order by year_cal) as next from company_info), result as (select *, case when next>revenue and prev<revenue then 1 when next= revenue or prev=revenue then 1 else 0 end as flag from main ) select company_name from result where flag=1 group by company_name;

with temp as (select c.*, case when lead(revenue,1,revenue) over(partition by company_name order by year_cal) >= revenue then 1 else 0 end as flag from company_info c) select * from company_info where company_name not in (select company_name from temp where flag = 0)

Superb explanation 👌 👏 👍

with CTE as (select *, revenue - (lag(revenue) over(partition by company_name order by year_cal)) as yr_rn from company_info) select company_name, year_cal, revenue from CTE where company_name in (select company_name from CTE group by company_name having min(yr_rn) >=0)

select team_name , count(1) as no_of_matches_played,sum(win_flag) as no_of_matches_won,(count(1) - sum(win_flag)) as no_of_matches_lost from ( select team1 as team_name,case when team1 = result then 1 else 0 end as win_flag from cricket_match union all select team2 as team_name, case when team2 = result then 1 else 0 end as win_flag from cricket_match )a group by team_name;

with cte as ( select * ,row_number() over(partition by dept order by salary ASC) as min_rn ,row_number() over(partition by dept order by salary DESC) as max_rn from emp ) select dept ,max(case when min_rn = 1 then salary end) as min_salary ,min(case when max_rn = 1 then salary end ) as max_salary from cte group by dept

A little different query than yours. I didn't used Order By inside the CTE. with cte as( select e.*, m.name as manager_name, count(m.id) over(partition by m.id)as cnt from employee e inner join employee m on e.managerId = m.id) select manager_name,count(*) as no_of_employees_managed from cte where cnt >4 group by manager_name;

superb explanation.........

We can also check the last status of employee whether it's IN or OUT and based on that we can find out employees who are inside the office. WITH CTE AS( SELECT *, LAST_VALUE(EMP_STATUS) OVER(PARTITION BY EMP_ID ORDER BY TIME_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FLAG FROM OFFICE ) SELECT DISTINCT EMP_ID FROM CTE WHERE FLAG='IN';

While querying on top of cte sum(flag) alone would work as we have only 1 and 0 as flag values

select emp_id,max(case when salary_component_type ="salary" then val end )as salary, max(case when salary_component_type="bonus" then val end) as bonus, max(case when salary_component_type ="hike_percent" then val end) as hike_percent from emp group by emp_id ;

Interesting query.

Very nice 🙂 plz continue provide more videos that are generally asked in interviews

if possible can post pdf of all the videos you have posted

just keep going bro

Great explaining..Your step-by-step breakdown made it easy to understand the logic and purpose behind each part..

Directly why not dense_rank() ?

Just a small change, used IIF insted of Case when WITH cte AS ( SELECT *,FORMAT(trans_date, 'yyy-MM') as month FROM transactions) SELECT month, country, count(*) AS trans_count, SUM(amount) AS trans_total_Amount, SUM(IIF(state = 'Approved', 1, 0)) AS approved_count, SUM(IIF(state = 'Approved', amount, 0)) AS approved_total_amount FROM cte GROUP BY month, country

U made complicated😢

Simply you can filter the date by state = 'approved'

Nicely explained

Well Explained 👍

another approach with cte as( select FORMAT(trans_date,'yyyy-MM') as month,country, count(*) cnt, sum(amount) trans_total_amount from transactions_thus group by FORMAT(trans_date,'yyyy-MM') , country ), cte2 as( select FORMAT(trans_date,'yyyy-MM') as month2, count(*) approved, amount from transactions_thus where state='approved' group by amount, FORMAT(trans_date,'yyyy-MM') ) select c1.*, approved, amount from cte c1 inner join cte2 c2 on c1.month = c2.month2

Very important points 👍 super

Firstly thanks, got to learn this kind of join! But, if any ticket is opened on friday and closed on monday, actual business days taken is 1 day. As per your logic does it give 1 or 3?

Wow great 😮

WITH cte AS ( SELECT *, MIN(population) OVER(PARTITION BY state) AS min_pop, MAX(population) OVER(PARTITION BY state) AS max_pop FROM city_info ) SELECT state, MIN(CASE WHEN population = min_pop THEN city END) AS minimum_popluation, MAX(CASE WHEN population = max_pop THEN city END) AS maximum_population FROM cte GROUP BY state;

Awesome explanation 😃

Keep up the good work!

solve problem on window functions

with cte as (select max(resolve_date) as MAX_DATE from ticket ), cte2 as( select min(issue_date)STRT_DATE from ticket union all select DATEADD(dd,1,STRT_DATE) from cte2,cte where STRT_DATE<=MAX_DATE ) select --a.STRT_DATE b.ticket_id ,b.issue_date ,b.resolve_date ,count(strt_date)-1 Day_count --,datename(WEEKDAY,strt_date) Day_of_week --,c.holiday_date from cte2 as a join ticket as b on a.strt_date between b.issue_date and b.resolve_date where datename(WEEKDAY,strt_date) not in ('Saturday','Sunday') and a.STRT_DATE not in (select holiday_date from holiday_cal) group by --a.STRT_DATE b.ticket_id ,b.issue_date ,b.resolve_date option(maxrecursion 0) this will give a correct answer because '2024-12-22' and '2025-01-11' both dates are on Sunday and Saturday

One question I am not clear why did you multiply with 2 for week Including weekends the total days are 20 (first value)

Nice bro ..Keep posting videos

Thank you so much for sharing with dataset ... making more interesting for me

Very good brother, i learnt for the first time that we can even join tables using such logic

Superb explanation 👌 👏 👍

Wow great 👍

Its out of my mind

Hi, I have a doubt, inner join both table have null values, 1st table have 2 and 2nd table have 1, in inner join we can take one null value for the two tables.