make it simple dude, you are making it more and more complicated...

for ascending order if we just create a new row with lead() return the min(abs diff between them) it will work right

Thank you for sharing! here's my little bit on SQL server: ===================================== with compiled as (select *, 'Succeed' as [Status] from Succeeded union select *, 'Failed' as [Status] from Failed), flagged_for_grp as (select *, case when DATEDIFF(DAY, LAG(success_date, 1, success_date) over(order by success_date), success_date) <= 1 and [status] = LAG(status, 1, status) over (order by success_date) then 0 else 1 end [flag] from compiled where success_date between '2019-01-01' and '2019-12-31') select Min(success_date) [Start date], MAX(success_date) [End date], [Status], COUNT([Status]) [Status count] from (select *, SUM([flag]) over(order by success_date) [group] from flagged_for_grp) s1 group by [group], [Status] order by 1 =====================================

Nice

my solution: with cte as ( select t.team_id,t.team_name,home_team_id ,away_team_id , home_team_goals,away_team_goals, case when t.team_id = m.home_team_id and home_team_goals > away_team_goals then 3 when t.team_id = m.away_team_id and home_team_goals < away_team_goals then 3 when t.team_id = m.home_team_id and home_team_goals = away_team_goals then 1 when t.team_id = m.away_team_id and home_team_goals = away_team_goals then 1 else 0 end as points from Teams t left join Matches m on t.team_id = m.home_team_id OR t.team_id = m.away_team_id ), cte2 as ( select team_id, team_name, points, case when team_id = home_team_id then home_team_goals else away_team_goals end as goal_for, case when team_id = home_team_id then away_team_goals else home_team_goals end as goal_against from cte ) select team_name,count(team_name) as matches_played, sum(points) as points, sum(goal_for) as goal_for, sum(goal_against) as goal_against, sum(goal_for) - sum(goal_against) as goal_diff from cte2 group by team_name order by sum(points) desc ,sum(goal_for) - sum(goal_against) desc,team_name

with cte_1 as ( select p.project_id,p.employee_id,e.name,e.experience_years from Project p join Employee e on p.employee_id=e.employee_id ), cte_2 as ( select *,dense_rank() over(partition by project_id order by experience_years desc) as rn from cte_1 ) select project_id,employee_id from cte_2 where rn=1

@ 9:30 I'll see you guys in the next video.

SELECT sell_date, COUNT(DISTINCT product) AS num_sold, GROUP_CONCAT(DISTINCT product) AS products -- by defalt group_concat is consider ascending order and comma as a separator FROM Activities GROUP BY sell_date;

good video , please stop saying 'hare' instead of here , learn how to say the word.

select user_id,max(time_stamp) as last_stamp from Logins where year(time_stamp) = '2020' group by user_id

you made me make a leap out of my zone ,,, thank you so much.. appreciate your hard work... dear :)

WITH cte1 AS ( SELECT *, from_id + to_id AS checkValue, COUNT(*) OVER (PARTITION BY from_id + to_id) AS call_count, SUM(duration) OVER (PARTITION BY from_id + to_id) AS total_duration FROM call_data ), cte2 AS ( SELECT MIN(from_id) OVER (PARTITION BY checkValue) AS min_from_id, MAX(to_id) OVER (PARTITION BY checkValue) AS max_from_id, call_count, total_duration FROM cte1 ) SELECT min_from_id, max_from_id, call_count, total_duration FROM cte2 group BY min_from_id, max_from_id, call_count, total_duration

Ahhhh. tired control the brain

select *, sum(score_points) over(partition by gender order by day) as totalPoint from competition_scores;

with cte as (select power, case when factor>=0 then concat('+',factor) else factor end as factor from terms), cte2 as (select 1 as nill,power,factor, case when power=0 then factor when power =1 then concat(factor,'x') else concat(factor,'x^',power) end as eqn from cte order by power desc) select concat(group_concat(eqn order by power desc separator ''),'=0' ) as equation from cte2 group by nill;

with cte as ( select * from ( select *,ifnull(lag(result,1) over(partition by player_id order by match_day),result) as prev from matches)kk where result=prev),cte2 as ( select player_id,count(player_id) as cnt from cte where result='win' and prev='win' group by player_id) select distinct(m.player_id),ifnull(c.cnt,0) as longest_streak from matches m left join cte2 c on m.player_id=c.player_id; ;

Sir thankyou so much for explaining this. leetcode solutions are very confusing to understand and yours are just beautiful

with kk as (select * from friendship union select user2_id,user1_id from friendship), cte as ( select l1.user_id as user1_id,l2.user_id as user2_id,l1.page_id as page1_id,l2.page_id as page2_id from likes l1 left join likes l2 on l1.user_id<>l2.user_id and l1.page_id<>l2.page_id), cte2 as ( select * from cte where (user1_id,user2_id) in (select * from kk) order by user1_id), cte3 as ( select user1_id,page2_id as page_id,count(*) as friends_likes from cte2 group by 1,2 order by 1,2 desc) select user1_id,page_id from cte3 where(user1_id,page_id) not in (select user_id,page_id from likes) order by user1_id,friends_likes desc; I solved it using self join

Glad to find this channel.... So nice way of explanation. Right pace, right depth of analysis and above all showing us right way of approach in writing a query !!! Thanks :)

with cte as ( select l1.user_id as user1_id,l2.user_id as user2_id from listens l1 left join listens l2 on l1.song_id=l2.song_id and l1.day=l2.day and l1.user_id<>l2.user_id group by l1.user_id,l2.user_id,l1.day having count(distinct l1.song_id)>=3) select user1_id,user2_id from cte where (user1_id,user2_id) not in (select * from friendship) and (user1_id,user2_id) not in (select user2_id,user1_id from friendship) and user2_id is not null;

with cte1 as ( select *, Lead(nextDayPrice, 1, 0) OVER(PARTITION BY t.stock_name ORDER BY t.operation) AS nextDayRate, nextDayPrice - Lead(nextDayPrice, 1, 0) OVER(PARTITION BY t.stock_name ORDER BY t.operation) AS capital_gain_loss from ( SELECT stock_name, operation, sum(price) OVER(PARTITION BY stock_name,operation) AS nextDayPrice FROM stocks )t group by t.stock_name, t.operation ) select stock_name, capital_gain_loss from cte1 where operation = 'Sell'

Hi mate, What is the necessity to add user1_id<user2_id as the given table already has that condition that the friendship table always satisfies the condition user1_id<user2_id, thought of sharing this.

UPDATE Salary SET sex = IF(sex = 'm', 'f', 'm');

with cte as (select employee_id,experience,salary from( select *,sum(salary) over (order by salary asc rows between unbounded preceding and current row ) as cum_sum from candidates where experience='senior') kk where cum_sum<=70000 union select * from candidates where experience='junior' order by salary asc), cte2 as(select employee_id,experience,salary from( select *,sum(salary) over (order by experience desc,salary asc rows between unbounded preceding and current row ) as cm_sum from cte)kk where cm_sum<=70000) select experience,count(*) as total_hired from cte2 group by 1;

WITH RECURSIVE cte AS ( SELECT id, MIN(month) AS month, MAX(month)-1 AS mx FROM employee GROUP BY id UNION ALL SELECT id, month + 1,mx FROM cte WHERE month < mx ),cte2 as (select c.id,c.month,ifnull(e.salary,0) as sume from cte c left join employee e on c.month=e.month and c.id=e.id order by c.id,c.month desc) select id,month,cum_sum from( select *,sum(sume) over(partition by id order by month desc rows between current row and 2 following) as cum_sum from cte2) as kk where sume<>0; Solved it using recursive cte

with cte as ( select t.*,u1.banned as kp ,u2.banned as tp from trips t left join users u1 on t.client_id=u1.users_id left join users u2 on t.driver_id=u2.users_id where u1.banned='no' and u2.banned='no'), cte2 as( select distinct request_at, sum(case when status='cancelled_by_client' then 1 when status='cancelled_by_driver' then 1 else 0 end ) over (partition by request_at) as pd , sum(case when client_id then 1 else 0 end) over (partition by request_at) as kd from cte) select request_at as Day, round((pd/kd),2) as `Cancellation Rate` from cte2 where request_at between "2013-10-01" and "2013-10-03";

WITH cte AS (SELECT *, Rank() OVER (PARTITION BY country ORDER BY points DESC) AS rn FROM wineries ) SELECT country, Min(CASE WHEN rn = 1 THEN concat(winery,'(',points,')') END) AS top_winery, IFNULL(Min(CASE WHEN rn = 2 THEN concat(winery,'(',points,')') END),'No second winery') AS second_winery, ifnull(Min(CASE WHEN rn = 3 THEN concat(winery,'(',points,')') END),'No third winery') AS third_winery FROM cte GROUP BY country; I tried it from my end

with recursive cte as (select customer_id,min(year(order_date)) as yr,max(year(order_date)) as mx_year from orders group by 1 union all select customer_id,yr+1,mx_year from cte where yr<mx_year) ,cte2 as (select customer_id,yr from cte order by customer_id,yr), cte3 as (select c.customer_id,c.yr as yeare,ifnull(sum(o.price),0) as total from cte2 c left join orders o on c.customer_id=o.customer_id and c.yr=year(o.order_date) group by 1,2 order by c.customer_id,yr), cte4 as ( select *, ifnull(lag(total,1) over (partition by customer_id order by yeare),0) as lg from cte3), cte5 as ( select *,(total-lg) as ts from cte4) , cte6 as( SELECT customer_id, SUM(CASE WHEN ts>0 THEN 1 ELSE 0 END) AS yes_count, COUNT(yeare) AS year_count, SUM(CASE WHEN ts<=0 THEN 1 ELSE 0 END) AS no_count FROM cte5 GROUP BY customer_id) select customer_id from cte6 where year_count=yes_count; Man I tried to solve it in my way and it took this many lines to run it! But still this works

fantastic video !

best channel on SQL and datascience.

with cte as ( select *,lag(spend,1)over(partition by product_id order by transaction_date) as lg from user_transactions) select year(transaction_date) as year,product_id,spend as curr_year_spend,lg as prev_year_spend,round(((spend-lg)*100)/lg,2) as yoy_rate from cte; One of the easist solution for this qn!

with cte as(select m.*,p.group_id as 1st_place_grp_id,p2.group_id as 2nd_place_grp_id from matches m left join players p on m.first_player=p.player_id left join players p2 on m.second_player=p2.player_id), cte2 as ( select first_player as pl_id,1st_place_grp_id as grp_id,sum(first_score) as sm from cte group by first_player,1st_place_grp_id union all select second_player as pl_id,2nd_place_grp_id as grp_id,sum(second_score) as sm from cte group by 1,2), cte3 as ( select pl_id,grp_id,sum(sm) as sam from cte2 group by 1,2 order by 2,sum(sm)desc) select grp_id,pl_id from ( select *,row_number()over(partition by grp_id order by sam desc) as rn from cte3) as kk where rn=1; Alternate solution for this!

with cte as ( select * from (select order_date,item_id,seller_id,row_number() over (partition by seller_id order by order_date) as rn from orders) as kk where rn=2),cte2 as (select u.user_id as user_id,u.favorite_brand as fav_brand,i.item_brand as item_brand from users u left join cte c on u.user_id=c.seller_id left join items i on i.item_id=c.item_id) select user_id, case when fav_brand=item_brand then 'yes' else 'no ' end as 2nd_fav_item_brand from cte2; Hope this helps as well!

with cte as ( select p.passenger_id,p.flight_id,f.capacity, count(p.flight_id) over (partition by p.flight_id order by p.booking_time rows between unbounded preceding and current row) as cap from passengers p join flights f on p.flight_id=f.flight_id) select passenger_id,case when cap<=capacity then 'Confirmed' else 'Waitlist' end as `status` from cte order by passenger_id; Thanks for the explanation mate, here's my solution!

with cte as (select p.*,e.name,e.team from project p join employees e on p.employee_id=e.employee_id) ,cte2 as (select project_id,employee_id,workload,name ,round(avg(workload)over (partition by team order by team asc),2) as avge from cte) select project_id,employee_id,workload,name from cte2 where workload>avge order by employee_id,project_id; I tried this from my end , hope this helps

with cte as ( select pr.invoice_id as invoice_id,pr.quantity as quantity,p.price as price,pr.quantity * p.price as total_sum from purchases pr join products p on pr.product_id=p.product_id) select invoice_id,quantity,total_sum from cte where invoice_id=( select invoice_id from cte group by invoice_id order by sum(total_sum) desc,invoice_id asc limit 1); One efficient solution to solve this problem , hope you find it helpful.

Select employee_id, case when employee_id%2<>0 and name not like 'M%' then salary else 0 end as bonus from employeesx

This video helped me a lot ,i hope u will continue this playlist

How are you getting auto complete code in there has you previously coded

SELECT count(*) as payment_count FROM transactions t1 Join transactions t2 On t1.transaction_id <> t2.transaction_id AND t1.merchant_id = t2.merchant_id and t1.credit_card_id = t2.credit_card_id and t1.amount = t2.amount Where cast(t2.transaction_timestamp as time) between cast(t1.transaction_timestamp as time) AND cast(t1.transaction_timestamp as time) + '00:10:00';

with cte as ( select *,max(score)over(partition by exam_id ) as maximum,min(score) over (partition by exam_id ) as minimum from exam), cte2 as ( select *, case when score=maximum then 'Highest' when score = minimum then 'lowest' else 'quiet' end as catg from cte ), cte3 as ( select student_id,group_concat(distinct catg) as grp,count(distinct catg) as cnt from cte2 group by student_id) select c.student_id,s.student_name from cte3 c join student s on c.student_id=s.student_id where cnt=1 and grp='quiet' ; Create table If Not Exists Candidates (employee_id int, experience ENUM('Senior', 'Junior'), salary int); I have tried this query on my own , pretty simple logic, hope it works for you as well.

Great idea!

hey sir nice video here is my approach :- with cte as ( select customer_id, sum(case when product_name in ('A','B') then 1 else 0 end) as flag, sum(case when product_name = 'C' then 1 else 0 end) as flag1 from Ords group by customer_id ) select cte.customer_id,cust.customer_name from cte inner join cust on cust.customer_id = cte.customer_id where flag = 2 and flag1 = 0;

please keep doing for other questions too

This is very helpful. and easy to understand

Model Deployment End to End one please make for one cloud server also .. Well hat's off bro for efforts ❤

Great video ❤

WITH cte AS ( SELECT o.quantity AS quantity, i.item_category AS item_category, DAYNAME(order_date) AS order_day FROM orders o JOIN items i ON o.item_id = i.item_id ), cte2 AS ( SELECT item_category, order_day, SUM(quantity) AS total_sold FROM cte GROUP BY item_category, order_day ) SELECT item_category, SUM(CASE WHEN order_day = 'Monday' THEN total_sold ELSE 0 END) AS Monday, SUM(CASE WHEN order_day = 'Tuesday' THEN total_sold ELSE 0 END) AS Tuesday, SUM(CASE WHEN order_day = 'Wednesday' THEN total_sold ELSE 0 END) AS Wednesday, SUM(CASE WHEN order_day = 'Thursday' THEN total_sold ELSE 0 END) AS Thursday, SUM(CASE WHEN order_day = 'Friday' THEN total_sold ELSE 0 END) AS Friday, SUM(CASE WHEN order_day = 'Saturday' THEN total_sold ELSE 0 END) AS Saturday, SUM(CASE WHEN order_day = 'Sunday' THEN total_sold ELSE 0 END) AS Sunday FROM cte2 GROUP BY item_category; I have tried it on my own , hope this helps:)

with cte as ( select *,row_number() over (partition by company order by salary desc) as pk, count(company) over (partition by company) as rk from employee), cte2 as ( select *, case when (rk%2=0) then round(rk/2)+1 else round(rk/2) end as factor, case when (rk%2=0) then round(rk/2) else round(rk/2) end as factor1 from cte) I have tried it on my own, hope this helps :)

with cte as( select e.department_id,s.amount,date_format(pay_date,'%Y-%m') as monthe from employee e join salary s on e.employee_id=s.employee_id), cte2 as( select *,round(avg(amount) over (partition by monthe)) as avg_salary from cte) select department_id,monthe, (case when sum(amount)=avg_salary then 'Same' when sum(amount)>avg_salary then 'higher' else 'Lower' end) as comparison from cte2 group by department_id,monthe; I have tried this on my own, hope it helps :)