Nice Explanation.

select Date from (select date,temp,((temp1+temp2)/2) as avg_temp from (select date , temperature , lag(temperature) over(order by date) temp1 ,lag(temperature,2) over(order by date) temp2 from geo)GG )A where temp >avg_temp

WITH cte AS ( SELECT num, ROW_NUMBER() OVER (ORDER BY num) AS row_num COUNT(*) OVER() AS total_count FROM numbers ), mean_cal AS ( SELECT avg(num) AS mean FROM numbers ) mode_cal AS ( SELECT num AS mode FROM numbers GROUP BY num ORDER BY count(*) DESC LIMIT 1 ) SELECT (SELECT mean FROM mean_cal) AS mean, (SELECT mode FROM mode_cal) AS mode, AVG(num) AS median FROM cte WHERE row_num IN ((total_count + 1) / 2, (total_count + 2) / 2);

I think even for first question, where condition should include empid ! = mgrid For this specific table

Hello sir help mi to solve this question Write a query to display manager id who have 2 emplayee

Hi ,i have one doubt that one voter usually caste their vote for one candidate as it comes under one to many(or vice versa) relation. But in our scenario it has many to many relations which practically won't possible right ?

My solution: with cteone as ( select storeid, Salesamount as sales, date, datepart(week, date) as week, dense_rank() over(partition by storeid order by datepart(week, date) asc) as first_week, dense_rank() over(partition by storeid order by datepart(week, date) desc) as last_week from sales_info ), ctwo as ( select storeid, Sum(case when first_week = 1 then sales end) as first_amount, Sum(case when last_week = 1 then sales end) as last_amount from cteone group by storeid ) select storeid, round(((last_amount - first_amount) / first_amount) * 100, 2) as perc_increase from ctwo;

Hi Sir, I tried in Postgresql with t as ( select date, customername, salesamount from sales_info ), t1 as ( select min(date) as first_date, max(date) as last_Date, customername from t group by customername ), t2 as ( select t.customername, t.salesamount,t1.first_date from t inner join t1 on t.date = t1.first_date ), t3 as ( select t.customername, t.salesamount,t1.last_date from t inner join t1 on t.date = t1.last_date ), t4 as ( select t2.customername, t2.salesamount as Init_amount, t3.salesamount as Last_Amount from t2 inner join t3 on t2.customername = t3.customername ) select customername, init_amount, last_amount, ((last_amount - init_amount)/init_amount)*100 as diff from t4 order by diff desc limit 1;

Thank you so much sir for making videos on daily after watching all your videos im feeling that im improving everyday

Awesome 👍 Request you to please make a detailed video on the stored procedure. I have searched the entire youtube but not getting helpful

You are making awesome vidoes hope u get much more viewerships...

You plz give us the structure of table details

You're doing amazing work! Please create more scenario and case study questions for data analyst interviews. These are incredibly helpful. Thank you!

transaction_id column is missing in create table and insert table

is it correct bro with cte as( select c.*,count(voter_id) as voters_count, candidate_id from candidates c join election e on c.id=e.candidate_id group by e.candidate_id, c.id,c.name) select name from cte where voters_count=(select max(voters_count) from cte)

keep it up❤❤❤

Hii..

Thanks for making this video. Please bring more questions on data analysis topic.

with cte as( select sum(salesAmount) first_week_sales, StoreID, StoreName, DATEPART(WK, Date) weekly_transaction from sales_info where DATEPART(WK, Date) = 1 group by StoreID, DATEPART(WK, Date), StoreName ), cte2 as( select sum(salesAmount) last_week_sales, StoreID, DATEPART(WK, Date) weekly_transaction from sales_info where DATEPART(WK, Date) = 3 group by StoreID, DATEPART(WK, Date) ) select cte.StoreID, cte.StoreName, first_week_sales, last_week_sales, concat(round((last_week_sales - first_week_sales)/first_week_sales*100,2), '%') as percentage_sales_increament from cte inner join cte2 on cte.StoreID = cte2.StoreID

If possible pls create interview MFQ's series

Thanks so much for sharing valuable content🤩🤩, Small request pls add script in all the videos.

Is there any openings for freshers

Simple Solution with out using CTE SELECT name, sum(runs_scored) AS total_runs FROM Matches JOIN Players ON Matches.player_id = Players.id GROUP BY player_id HAVING ( COUNT(CASE WHEN runs_scored >= 50 THEN 1 END) >=2 AND COUNT(CASE WHEN runs_scored = 0 THEN 1 END) = 0 ) ORDER BY total_runs DESC

Thank You a Nice explanation keep going

amazing bro..

with cte as( select *,row_number() over(partition by player_id order by match_id)rn from WC_matches) select w.* from WC_players w Join ( select player_id from (select match_id,player_id, case when match_id-lag(match_id,1,match_id-1) over(partition by player_id order by match_id)=1 then 1 else 0 end cont from cte where player_id in (select player_id from cte where rn=3) )A group by player_id having count(player_id)=sum(cont))d on w.id=player_id

with cte1 as( select e.*,e1.empname as managername, e1.salary as managersalary, (e.salary + e1.salary)/2 as averagesalary from employeet1 e join employeet1 e1 on e.mgrid=e1.empid ), cte2 as( select concat(empname,':',managername) as emp_mgr_pair, averagesalary as salary, dense_rank() over(order by averagesalary desc) as rn from cte1 ) select emp_mgr_pair,salary from cte2 where rn=2;

WITH CTE AS( SELECT *,SUM(CASE WHEN runs_scored = 0 THEN 0 ELSE 1 END ) OVER (PARTITION BY player_id ORDER BY player_id)DECCOUNT ,COUNT(player_id ) OVER (PARTITION BY player_id ORDER BY player_id)TOTALCOUNT FROM matches) , A AS( SELECT match_id, player_id, runs_scored, SUM(CASE WHEN runs_scored>=50 THEN 1 ELSE 0 END ) OVER (PARTITION BY player_id ORDER BY player_id)TOTAL FROM CTE WHERE DECCOUNT=TOTALCOUNT) SELECT P.name,SUM(runs_scored)TOTALRUNS FROM A JOIN players P ON P.id=A.player_id WHERE TOTAL =2 GROUP BY P.name

with cte_m as (select player_id, count(case when runs_scored >=50 then match_id else null end) as half_century, count(case when runs_scored = 0 then match_id else null end) as duck_out, sum(runs_scored) as total_runs from matches group by player_id), cte_p as( select name,id from players ) select cp.name,cm.total_runs from cte_m cm join cte_p cp on cm.player_id = cp.id where cm.half_century > 1 and cm.duck_out = 0;

select concat(manager_name,':',b.empname) as emp_mgr_name ,(manager_salary+salary)/2 as salary from (select empid as manager_id ,empname as manager_name ,salary as manager_salary from employee where mgrid is null)a join employee b on a.manager_id=b.mgrid Can you please validate this solution

with cte as( select distinct e.empid,t.mgrid,e.empname as mngname,t.empname as empname,e.salary+t.salary as totalsal from employee e join employee t on e.empid=t.mgrid where e.salary<t.salary) select top 1 empname+': '+mngname as emp_mng_pair,totalsal/2 salary from cte order by totalsal desc get employee who are getting highest sal compare to manager then combined two sal and get highest avg sal

select mgr_emp,avg_sal from ( select *,rank() over(order by avg_sal desc) as rn from ( select concat(e.empname, ':', m.empname) as mgr_emp, (e.salary+m.salary)/2 avg_sal from employee as e join employee as m on e.mgrid=m.empid )as a ) as b where rn=2

In your table creation and insertion queries there is no transaction_id column. Please add correct queries

My Solution in Mysql: SELECT user_id, total_number, round((total_credit_count*100/total_number),0) as credit_percent, round((total_debit_count*100/total_number),0) as debit_percent FROM(SELECT user_id, COUNT(user_id) as total_number, SUM(CASE WHEN type='credit' THEN 1 ELSE 0 END) as total_credit_count, SUM(CASE WHEN type='debit' THEN 1 ELSE 0 END) as total_debit_count FROM `transactions` GROUP BY user_id) as x;

Hi, This is my 2 solutions. 2nd one is similar to your solution. SELECT name FROM(SELECT name, total, DENSE_RANK() OVER(ORDER BY total DESC) as rnk FROM(SELECT e.candidate_id, c.name, COUNT(e.voter_id) as total FROM `election` e join candidates c on e.candidate_id = c.id GROUP BY e.candidate_id) as x) as y WHERE rnk=1; SELECT name FROM(SELECT candidate_id, name, DENSE_RANK() OVER(ORDER BY SUM(voter_value) DESC) as rnk FROM(SELECT e.*, c.name, 1.0/COUNT(e.candidate_id) OVER(PARTITION BY e.voter_id) as voter_value FROM `election` e join candidates c on e.candidate_id = c.id WHERE e.candidate_id IS NOT NULL) as x GROUP BY candidate_id) as y WHERE rnk = 1

Hi Sir, I tried with hierarchical query for this problem. Can you please check it once? with t as ( select empid,empname, prior empname as managername,salary,level from employee start with mgrid = 3 connect by empid = prior mgrid ),t1 as ( select avg(salary) as av_salary from t ) select av_salary, empname||'-'||managername as emp_mgr_pair from t1, t where managername is not null;

Hi Sir, This is my query. with t as ( select e.voter_id,c.id,c.name from election e inner join candidates c on e.candidate_id = c.id where e.candidate_id is not null ),t1 as ( select count(voter_id)over(partition by id) as cnt, voter_id, name, id from t ), t2 as ( select id,name, sum(cnt) as total from t1 group by id,name ), t3 as ( select name, dense_Rank()over(order by total desc) as rnk from t2 ) select name from t3 where rnk = 1;

select * from ( select *,dense_rank() over( order by cunt desc ) as rn from ( select count(e.voter_id) as cunt,e.candidate_id,c.name from Election as e join candidates as c on e.candidate_id=c.id group by e.candidate_id,c.name ) as a )as b where rn =1

i tried select player_id, name from ( SELECT *,cunt-rn as diff FROM ( select wm.match_id ,wm.player_id,wm.runs_scored,wp.name, rank() over(partition by player_id order by match_id) as rn,count(*) over(order by player_id) as cunt from WC_matches as wm join WC_players as wp on wm.player_id=wp.id where wm.runs_scored>=50 ) AS A ) as b group by player_id,name having sum(diff)=6

WITH cte AS ( SELECT player_id, match_id, COUNT(player_id) OVER (PARTITION BY player_id) AS c, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY match_id) AS rn FROM WC_matches ), ct2 AS ( SELECT player_id, (match_id - rn) AS di FROM cte WHERE c > 2 ), ct3 AS ( SELECT player_id, COUNT(*) OVER (PARTITION BY player_id, di) AS c22 FROM ct2 ) SELECT DISTINCT player_id FROM ct3 WHERE c22 = 3;