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;

group by 2 what is 2 ?

SELECT ym, ROUND(((total - lag(total) OVER(ORDER BY ym ASC))/lag(total) OVER(ORDER BY ym ASC)*100),2) as revenue_diff_pct FROM(SELECT SUM(value) as total, date_format(created_at, '%Y-%m') as ym FROM `sf_transactions` GROUP BY 2) as t;

SELECT user_id,count(*) as total_transaction, COUNT(case when type='credit' then Transaction_Id else null end) as total_credit_count, COUNT(case when type='debit' then Transaction_Id else null end) as total_debit_count, CONCAT(SUM(case when type='credit' then 1 else 0 end)*100/count(*),'%') as credit_percentage, CONCAT(SUM(case when type='debit' then 1 else 0 end)*100/count(*),'%') as debit_percentage, from transactions GROUP by user_id order by user_id

select p.name as player_name, COUNT(m.runs_scored) half_century from wc_matches as m join wc_players as p on m.player_id=p.id where m.runs_scored>=50 and m.runs_scored<100 GROUP by p.name HAVING count(m.runs_scored)>=3

Hi Sir, This is my query with t as ( select count(*) as total_txns, user_id from transactions group by user_id ), t1 as ( select user_id, type, count(type)over(partition by user_id, type order by user_id) as count_of_each_type from transactions ), t2 as ( select t1.user_id, t1.type,t.total_txns,t1.count_of_each_type, case when type = 'credit' then (t1.count_of_each_type/t.total_txns)*100 when type = 'debit' then (t1.count_of_each_type/t.total_txns)*100 end as percent from t1 inner join t on t.user_id = t1.user_id ), t3 as ( select user_id, total_txns, count(case when type = 'credit' then percent end) as credit_txns, count(case when type = 'debit' then percent end) as debit_txns from t2 group by user_id, total_txns ) select user_id, total_Txns, (credit_txns/total_Txns)*100 as Credit_Percent, (debit_txns/total_Txns)*100 as debit_Percent from t3;

Hi Sir, Thanks for uploaded the new video. This is my query for this problem. create table transactions ( user_id int, transaction_date date, amount int, type varchar(10) check (type in ('credit', 'debit')) ); insert into transactions values (1, to_date('2024-06-01','yyyy-mm-dd'), 1000, 'credit'); insert into transactions values (2, to_date('2024-06-02','yyyy-mm-dd'), 500, 'debit'); insert into transactions values (1, to_date('2024-06-03','yyyy-mm-dd'), 2000, 'credit'); insert into transactions values (3, to_date('2024-06-04','yyyy-mm-dd'), 1500, 'debit'); insert into transactions values (2, to_date('2024-06-05','yyyy-mm-dd'), 7500, 'credit'); insert into transactions values (4, to_date('2024-06-06','yyyy-mm-dd'), 3000, 'credit'); insert into transactions values (3, to_date('2024-06-07','yyyy-mm-dd'), 1000, 'debit'); insert into transactions values (4, to_date('2024-06-08','yyyy-mm-dd'), 2000, 'debit'); insert into transactions values (5, to_date('2024-06-09','yyyy-mm-dd'), 2500, 'credit'); insert into transactions values (5, to_date('2024-06-10','yyyy-mm-dd'), 500, 'debit'); insert into transactions values (1, to_date('2024-06-11','yyyy-mm-dd'), 4000, 'credit'); insert into transactions values (2, to_date('2024-06-12','yyyy-mm-dd'), 3000, 'debit'); insert into transactions values (3, to_date('2024-06-13','yyyy-mm-dd'), 1000, 'credit'); insert into transactions values (4, to_date('2024-06-14','yyyy-mm-dd'), 1500, 'debit'); insert into transactions values (5, to_date('2024-06-15','yyyy-mm-dd'), 2000, 'credit'); insert into transactions values (1, to_date('2024-06-16','yyyy-mm-dd'), 2500, 'debit'); insert into transactions values (2, to_date('2024-06-17','yyyy-mm-dd'), 2500, 'credit'); insert into transactions values (3, to_date('2024-06-18','yyyy-mm-dd'), 1500, 'debit'); insert into transactions values (4, to_date('2024-06-19','yyyy-mm-dd'), 1000, 'credit'); insert into transactions values (5, to_date('2024-06-20','yyyy-mm-dd'), 3000, 'credit'); */ select * from transactions; with ts as ( select user_id, transaction_Date, amount, type, case when type = 'credit' then amount else -amount end as st from transactions ), t1 as ( select user_id, transaction_Date, amount,type, sum(st)over(partition by user_id order by transaction_date) as total from ts order by transaction_Date ) select user_id, transaction_Date, amount,type,total, case when type = 'credit' then 'eligible' when type = 'debit' and total < 0 then 'ineligible' else 'eligible' end as eligibility from t1;

I really love to solve SQL so i mostly learned and worked in MYSQL only , so if you make vedios in MYSQL it will be useful for me to gain MYSQL knowledge it's my request brother thankyou...

Question: Which customer has visited the same store location twice for consecutive 2 days and next date shopping amount is higher than previous day In mysql SELECT customer_id, amount FROM(SELECT *, lag(date) OVER(PARTITION BY customer_id, location ORDER BY date ASC) as pre_date, lag(amount) OVER(PARTITION BY customer_id, location ORDER BY date ASC) as pre_amount FROM `shopping`) as e WHERE amount>pre_amount AND DATE_ADD(date, INTERVAL -1 DAY) = pre_date;

SELECT name, SUM(runs_scored) as runs FROM(SELECT *, SUM(output) OVER(PARTITION BY name) as total_no FROM(SELECT *,CASE WHEN runs_scored < 50 THEN 0 ELSE 1 END as output FROM (SELECT m.match_id, p.name, m.runs_scored FROM `matches` m JOIN players p ON m.player_id = p.id WHERE p.id NOT IN(SELECT id FROM players WHERE id IN(SELECT player_id FROM matches WHERE runs_scored = 0))) as t) as x) as y WHERE total_no >1 GROUP BY name;

Pivot:- ***** select * from ( select product,region,sales from sales )pivot(sum(sales) for Region in ('North' as North,'South' as South,'East' as East) ); Unpivot:- ******* Creating table: --------------- create table sales_Details as select * from ( select product,region,sales from sales )pivot(sum(sales) for Region in ('North' as North,'South' as South,'East' as East) ); select * from sales_Details; Unpivot the above table: ----------------------- select * from (select product,to_char(NORTH) as NORTH, to_char(SOUTH) as SOUTH, to_char(EAST) as EAST FROM sales_Details ) unpivot(column_Val for column_name in (NORTH,SOUTH,EAST));

with qer as ( select m.match_id,m.player_id,m.runs_scored,p.name from wc_matches m inner join wc_players p on m.player_id = p.id where m.runs_scored >= 50 ), ert as ( select q.*, row_number()over(partition by player_id order by match_id) as r from qer q ),hjk as ( select match_id,player_id,runs_Scored,name, match_id-r as f from ert ),uio as ( select match_id,player_id,runs_Scored,name,count(1)over(partition by player_id,f order by player_id) as f from hjk), tip as ( select player_id,f,name, count(*) as cnt from uio group by player_id,f,name having count(*) >= 3), eri as (select player_id, name from tip) select * from eri;

with cte as (select match_id-ranks as match_id ,player_id,name from ( select m.match_id,m.player_id,p.name, row_number()over(partition by player_id order by player_id)ranks from wc_matches m join wc_players p on m.player_id=p.id where runs_scored>50 group by match_id,player_id,name)t1) select name from cte group by match_id,player_id,name having count(match_id)=3 Is this right sir?