LeetCode Hard SQL problem | Students Reports By Geography | Pivot Ka Baap
ฝัง
- เผยแพร่เมื่อ 25 พ.ค. 2022
- In this video we will solve a LeetCode problem where we need to pivot the data from row to column. The interesting part about this problem is we don't have a common key to pivot the data on.
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
Script:
create table players_location
(
name varchar(20),
city varchar(20)
);
delete from players_location;
insert into players_location
values ('Sachin','Mumbai'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','Mumbai'),('Mayank','Bangalore');
#sql #dataengineer #pivot #leetcode - วิทยาศาสตร์และเทคโนโลยี
Hello Ankit, as usual, your explanation of the problem and its solution is excellent 🙂
Here's how I approached this in PostgreSQL:
1. At first, aggregated the rows into a single row using STRING_AGG (NULLs are eliminated)
2. Then, converted each column into array using STRING_ARRAY (So that we can use UNNEST function, as UNNEST takes array as input)
3. Then, used UNNEST to flatten each column into multiple rows.
SELECT
UNNEST(names_bangalore) AS Bangalore,
UNNEST(names_mumbai) AS Mumbai,
UNNEST(names_delhi) AS Delhi
FROM (
SELECT
STRING_TO_ARRAY(STRING_AGG(CASE WHEN city = 'Bangalore' THEN name END, ',' ORDER BY name), ',') AS names_bangalore,
STRING_TO_ARRAY(STRING_AGG(CASE WHEN city = 'Mumbai' THEN name END, ',' ORDER BY name), ',') AS names_mumbai,
STRING_TO_ARRAY(STRING_AGG(CASE WHEN city = 'Delhi' THEN name END, ',' ORDER BY name), ',') AS names_delhi
FROM players_location
) AS subquery;
Hi Ankit, l was searching for pivoting explanation or another proper way to handle this type of question and the best part is my search came to an end. This is the best explanation I ever got for pivoting.❤❤❤
😊
Thank you . This cleared all my doubts regarding pivot in mysql.
Great explanation Ankit , i am finding the advanced problem very difficult to understand but after your detail explanation it's more clear for me now ,thanks!
Initially I thought it can be easily done only with case statement. Later realised the problem. Good job brother!!
Right
Useful video Ankit!!
first i was thinkin why you use row_number but after solving it by myself then i understand awesome content......
Awesome ! Thank you
Excellent work brother
Thanks for the explanation!! 🙂🙂
what if there are 10-20 different cities like this, then I don't think so this method is feasible
true
Thanks a lot !
Can you explain the concept of using max or min in case?
Thanks...😊😊
Bhai❤ You've made things much simple for me
🙏
I really like the way you teach bro, keep up the good work.
Glad to hear that!
Sir, got so close to your solution exactly but couldn't figure out a way to aggregate them. Learned a new trick today!!!!!!
Well done!
You are doing really and awesome job brother.... really helpful ...
Glad to hear that
Thanks Ankit for video, very well explained.
And best part
Is my name in table 😁👍
😃
MYSQL solution
with base as (
select * , row_number() over(partition by city) as rnk from players_location )
select
max(case when city ='Bangalore' then name else NUll end) as Banglalore,
max(case when city ='Mumbai' then name else NUll end )as Mumbai,
max(case when city ='Delhi' then name else null end ) as Mumbai
from base
group by rnk
Nice way of Pivoting again some cool learning and Ankit i will request to make one compilation video of lot of questions on self join asked in interviews bcz it becomes really tricky when asked suddenly plz plz will be of great help in this playlist questions are there but still it will help a lot...
Noted
is there any other dynamic way to solve this problem? let suppose i have n number of cities then how once apply case when condition for this
Superb 💯
Thanks 🤗
Hi Ankit, Thanks a lot for the amazing videos. Just had one question, Any suggestion on the solution if we want the Location names to be dynamic, In case if we have 100 and 1000 of different location writing a case statement like this would be a tough call.
You can use pivot function
Wow wow
Hi Ankit,
Thanks for the video.
I tried with PIVOT in Oracle.
SELECT Bangalore,Mumbai,Delhi FROM
(
SELECT name, city, row_number() over(partition by city order by name) rnum
FROM players_location
)
PIVOT
(
min(name) FOR city IN('Mumbai' Mumbai,'Delhi' Delhi,'Bangalore' Bangalore)
);
Looks good. Thanks for posting
really great explanation 🙂
Thanks! 🙂
nice one ankit. Do you have some suggestion on what if the city is dynamic. Meaning more number of cities gets added or some cities gets deleted ?
You will have to modify the query to add more. If deleted all the values in that col will be null.
My Solution:
with yourtable as (
select *,row_number() over (partition by city order by city) as rn
from players_location
),
cte as (
select rn, Mumbai,Delhi,Bangalore
from
(
select city, rn, name
from yourtable
) d
pivot
(
max(name)
for city in (Mumbai,Delhi,Bangalore)
) piv
)
select Bangalore,Mumbai,Delhi from cte
Ankit bhai thanks for this video. After watching this I want to ask your help on one more concept. If you can make a video on it please.
Question: We all know that adding or removing a column in final group by clause gives different results, Can you please explain this in detail with more examples to clear this concept?
Big thanks in advance 😊
Sure 👍
Thank you Ankit for the video…
Quick Query, what if the city is list is too big …how can we make this clause dynamic in our query ?
We will have write all the case whens. There is is pivot function that can be used as well
with tmp as (select *,row_number() over (partition by city order by name)rnk from players_location)
select rnk
,max(case when city='Bangalore' then name end)Bangalore
,max(case when city='Delhi' then name end)Delhi
,max(case when city='Mumbai' then name end)Mumbai
from tmp group by rnk
Thanks for posting 👏
Hi sir, but what if we don't know city names , how to tackle such case.
with play as
(select *,row_number() over (partition by city order by name asc) as rn
from players_location
order by rn)
select min(case when city='Bangalore' then name end) as Bangalore,
min(case when city='Mumbai' then name end) as Mumbai,
min(case when city='Delhi' then name end) as Delhi
from play
group by rn
;
Can we create the city name dynamically?
Because in this solution we are manually using the values as Mumbai, Bangalore, Delhi
Yes, we we should add city as dynamically
with ct as (
Select *,
row_number() over(partition by city order by name) as rn
from players_location
)
Select
max(case when city = 'Bangalore' then name end) as Bangalore,
max(case when city = 'Mumbai' then name end) as Mumbai,
max(case when city = 'Delhi' then name end) as Delhi
from ct
group by rn
order by rn
Again Kudos to you Ankit for this problem solving approach !!!
Good one...
Need your advice these questions are asked in interviews and many interviewer want the result within mins even though sometime I am able to crack the logic but when some one told to write over teams chat it is little challenging reason being we need to think the output in mind and if they have group by or nested sub query it is easier to write in ssms rather than writing to teams chat. Any advice on that ?
As you practice more it will become easier. Don't think about it too much it will come naturally
@@ankitbansal6 yup daily doing.
I'm also facing the same issue. It is easier to work on the query on a RDBMS where we can slowly derive each part of the output to get to the final query properly but in interview I get stuck whenever there are multiple CTEs and sub queries involved as I have to derive the output of each CTE and subquery in my brain before proceeding.
Hi sir, In the place of city we need district and each city we had 10+ district so, can we follow same scenario that we seen in video.
Yes
Hi Ankit: thanks for the video. Please help with the same problem using Pivot. Or please make a video specifically using PIVOT function. It would be very helpful.
Ok
select Bangalore, Mumbai, Delhi from (
SELECT Player_groups, Bangalore ,Mumbai ,Delhi FROM
(select * , Row_Number() over(partition by city order by name ) As Player_groups from Players_location )Tab1
PIVOT
(
Max(Name) FOR City IN (Bangalore,Mumbai,Delhi)) AS Tab2
) As TT
Create Table #Crickter
(
[Name] varchar(50),
[City] varchar(50)
)
Insert Into #Crickter([Name],[City])
Values('Sachin','Mumbai'),
('Virat','Delhi'),
('Rahul','Bangalore'),
('Rohit','Mumbai'),
('Mayank','Bangalore')
select Name,City, RANK() OVER(Partition BY City Order By Name) As Ranks into #TempCrickter from #Crickter
select * from #TempCrickter
Select Mumbai,Delhi,Bangalore from #TempCrickter
PIVOT(
Max(Name)
FOR[City]
IN([Mumbai],[Delhi],[Bangalore])
) As Crickter_Pivot
Hi Ankit,
I am unable to frame this query if I want o/p like
City Name 1 Name2
Bangalore Mayank Rahul
Mumbai Rohit Sachin
Delhi Virat Null
I can achieve this using Pivot. Can you plz help me with this using case statement
Awesome explanation, Ankit sir, but yes, my question is also: if I have 20 cities, then how can we handle this situation? I have one dynamic solution, which I am posting here with the help of AI. For a better approach, please guide us.
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
-- Get distinct cities
SELECT @cols = STRING_AGG(QUOTENAME(city), ',') WITHIN GROUP (ORDER BY city)
FROM (SELECT DISTINCT city FROM players_location) AS Cities;
---print @cols;
SET @query = '
SELECT *
FROM (
SELECT name, city, ROW_NUMBER() OVER (PARTITION BY city ORDER BY name) AS rn
FROM players_location
) AS SourceTable
PIVOT
(
MAX(name) FOR city IN (' + @cols + ')
) AS PivotTable
ORDER BY rn;
';
EXECUTE(@query);
problem is similar to others just the difference is that we dont have ids on which we can group them into one row.Thanks
True
Why you created rownum, this could have been achieved only using case statement
Can you explain how this can be done more dynamically without hard coding the city names into the code? Do we need to use dynamic sql in that case? I have been trying to figure out this for other situation too, please lmk.
Also, i saw your reply regarding the use of the pivot function but MySQL doesn't support that.
If we have more cities like 10 to 15 then do we need to enter all case when command for each city?
Is there any other way.
Yes you need to enter all 10 case when. As you are creating new columns 🙂
@@ankitbansal6 thank you ☺️
What if we have 100 cities, difficult to manage case statement
@@jatspower1 there is a pivot function. Check that out
Hi Ankit, had one doubt here.. why are we using Max or Min function here?
Use of MIN or MAX is required to eliminate NULL VALUES for each group. Watch video from 6:00
Hi Ankit,
This time I couldn't find the schema and sample records in your description.
Sorry I missed it. Just added now.
Please solve all problems of stratascratch
Is this also correct? I have used joined the table by row number
with t1 as
(select * , row_number() over(partition by city order by city) as rn
from players_location),
t2 as
(select name as Bangalore, rn from t1 where city = 'Bangalore'),
t3 as
(select name as Mumbai, rn from t1 where city = 'Mumbai'),
t4 as
(select name as Delhi, rn from t1 where city = 'Delhi')
select a.bangalore,b.mumbai,c.delhi from t2 as a
left join t3 as b on a.rn = b.rn
left join t4 as c on b.rn = c.rn ;
This is good. Only thing is you will have to create as many cte as no of cities.
this solution doesn't work if the "Right" side table has more values as you used left join , try this solution with adding more values with the city name 'Delhi' like ('ahsan', 'Delhi')... , run ur query and u will get to know that it is not the expected solution.
@@venkataramanakumar365 if will use full join then it will work both all cases
Hi Ankit, if we have some personal doubt in sql problem how can I send you the problem to get it solved because I am stuck.
Send me on ankitbansal1988@gmail.com
not working below query
Select
max(case when city ='Bangalore' then name end) as Bangalore,
max(case when city ='Mumbai' then name end) as Mumbai,
max(case when city ='Delhi' then name end) as Delhi,
row_number() over(partition by city order by name) as player_groups
from players_location
group by player_groups
order by player_groups;
Hi Ankit, I have tried my query for this,
with cte1 as
(select name,city,
row_number() over() as rn
from players_location
where city='Bangalore'
order by name),
cte2 as
(select name,city,
row_number() over() as rn
from players_location
where city='Mumbai'
order by name),
cte3 as
(select name,city,
row_number() over() as rn
from players_location
where city='Delhi'
order by name)
select cte1.name as Bangalore,cte2.name as Mumbai,cte3.name as Delhi
from cte1 left join cte2
on cte1.rn=cte2.rn
left join cte3 on
cte2.rn=cte3.rn;
with cte as(
select city,case when city='Bangalore' then name end as 'Bangalore',
case when city='Mumbai' then name end as 'Mumbai',
case when city='Delhi' then name end as 'Delhi',
row_number() over(partition by city order by name asc) as rn
from players_location
)
select max(Bangalore) Bangalore,max(Mumbai) Mumbai, max(Delhi) Delhi from cte group by rn
here is my solution before watching the video..
my sol:
Select max(case when city = 'mumbai' then name else null end ) as Mumbai
, max(case when city = 'Delhi' then name else null end ) as Delhi
,max(case when city = 'Bangalore' then name else null end) as Bangalore
from (Select *, row_number() over(partition by city order by name) as rn from players_location) A
Group by rn
Can the columnsbe created dynamically, like the number of cities are unknown, how can it be proceeded..!
Not possible. Amy be done using procedure
@@ankitbansal6 Thanks for answering the question. And I really like all the videos. You are doing a great job!
@@sharathraj4838 may be you can achieve this by using dynamic SQL
Hi Ankit:I didn't not get the max or min concept
We need a single row for each group key. Now in select i need to use aggregate functions otherwise it will fail.
create temp table uj
as
(select * ,row_number() over(partition by city order by name) as rk from players_location );
select * from uj
select a.rk , max( case when a.city = 'Bangalore' then a.name else null end ) as "Bangalore" , max(case when b.city = 'Mumbai' then b.name else null end ) as "Mumbai",
max(case when c.city = 'Delhi' then c.name else null end ) as "Delhi"
from uj a left join uj b on a.rk= b.rk
left join uj c on a.rk= c.rk
group by 1
order by 1
Hi Ankit
Found one more solution
select * FROm
(
SELECT name,city,ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) AS rn
FROM players_location
) A
PIVOT(
MAX(Name)
FOR City IN (
[Mumbai],[Bangalore],[Delhi]
)
)AS PivotTable
Please make your suggestion on above
How will you remove the rn column in the output?
@@AmanVerma-cu3lp
Please find the below solution.
select Mumbai,Bangalore,Delhi FROm
(
SELECT name,city,ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) AS rn
FROM players_location
) A
PIVOT(
MAX(Name)
FOR City IN (
[Mumbai],[Bangalore],[Delhi]
)
)AS PivotTable
@@NishantKumar-oe9zd Actually, I was working on a dynamic approach for this question. The query is similar, but I'm unable to remove the rn column.
DECLARE @sql nvarchar(MAX)
DECLARE @cols nvarchar(max)
SELECT @cols = STUFF( (SELECT DISTINCT ', '+ QUOTENAME(city)
FROM players_location
FOR XML PATH('')
), 1, 1, '')
SET @sql = 'select * from
(
SELECT name,city,ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) AS rn
FROM players_location
) A
PIVOT(
MAX(Name)
FOR City IN ('+@cols+')
)AS PivotTable'
EXEC(@sql)
select min(if(city="mumbai",name,null)) mumbai,min(if(city="delhi",name,null)) delhi ,
min(if(city="bangalore",name,null)) bangalore from(
(select *,row_number() over(partition by city order by name) rn from players_location)) a
group by rn
(try this!)
Why cant we use the PIVOT() instead ?
It's not exactly pivot
@@ankitbansal6 Didn't get you. Can you pls eloborate ?
But pivot is easier then this case statement as this is a very long process
I feel there is no need to create row_num, this question can be solved using case only
Try it 😊
select a.name as Mumbai,b.name as Banglore,c.name as Delhi from(select name,row_number() over(order by name) as rn from players_location where city='Mumbai')a left join (
select name,row_number() over(order by name) as rn from players_location where city='Bangalore')b on a.rn=b.rn left join (
select name,row_number() over(order by name) as rn from players_location where city='Delhi')c on a.rn=c.rn
SELECT [Mumbai],[Delhi],[Bangalore] from (
Select *, RANK() OVER (PARTITION BY CITY ORDER BY NAME) AS RNK FROM PLAYERS_LOCATION) as Q1
PIVOT(
MAX(name) for city in ([Mumbai],[Delhi],[Bangalore])
) as pvtTable
SELECT Bangalore, Mumbai, Delhi
FROM (
SELECT *
,ROW_NUMBER() OVER(PARTITION BY city ORDER BY name ASC) AS rnk
FROM players_location
) AS a
PIVOT(MIN(name) FOR city in (Bangalore, Mumbai, Delhi)) AS b
ORDER BY rnk;
Hi Ankit thanks for this great video. I have a question if the table is dynamic number of cities and city names change everyday. Can we write a sql query adapting different cities ? For example ;
day 1 table;
create table players_location
(
name varchar(20),
city varchar(20)
);
delete from players_location;
insert into players_location
values ('Sachin','Mumbai'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','Mumbai'),('Mayank','Bangalore');
day 2 table;
create table players_location
(
name varchar(20),
city varchar(20)
);
delete from players_location;
insert into players_location
values ('Sachin','Mumbai'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','Mumbai'),('Mayank','Bangalore',),('Ankit','Tokyo',);
day 3 table;
create table players_location
(
name varchar(20),
city varchar(20)
);
delete from players_location;
insert into players_location
values ('Sachin','London'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','London'),('Mayank','Bangalore',),('Ankit','Tokyo',);
+1
Hi Ankit,
Firstly I would thankful the sharing this sql challenges
Here is my solution
WITH CT1 as (SELECT CITY,
CASE WHEN CITY='Bangalore' THEN NAME END as Bangalore,
CASE WHEN CITY='Mumbai' THEN NAME END as Mumbai,
CASE WHEN CITY='Delhi' THEN NAME END as Delhi,
row_number() over(partition by city order by name asc) as rn
FROM players_location
GROUP by
CITY,
NAME
)
SELECT max(Bangalore),max(Mumbai),max(Delhi) FROM CT1
group by rn
Looks good. Thanks for posting 👏
Ankit few videos are blurred. See of you can do something on that. That would be helpful
Change the video quality from TH-cam settings
My Solution :-
with cte as
(select *, ROW_NUMBER() over (partition by city order by name)rn from players_location)
select
MIN(case city when 'Bangalore' then name end)Bangalore,
MIN(case city when 'Mumbai' then name end)Delhi,
MIN(case city when 'Delhi' then name end)Mumbai
from cte
group by rn
Thanks for sharing:)
Perfect
Hi Ankit, thanks for the videos, you explain it in a simple way which is easy to catch. I was going through sql advanced certification program on hackerrank and could not solve crypto currency transaction problem. Request you to help on the same.
Link please
select bangalore, mumbai,delhi
from
(
select *
, ROW_NUMBER()over (partition by city
order by name) as player_gps
from
players_location
)x
pivot
(
max(name) for city in
([bangalore],[mumbai],[delhi])
)pt
WITH CTE AS (
SELECT
LISTAGG(CASE WHEN CITY = 'BANGALORE' THEN PLAYER_NAME END,',') AS BANGALORE,
LISTAGG(CASE WHEN CITY = 'DELHI' THEN PLAYER_NAME END,',') AS DELHI,
LISTAGG(CASE WHEN CITY = 'MUMBAI' THEN PLAYER_NAME END,',') AS MUMBAI
FROM PLAYER
)
SELECT
REGEXP_SUBSTR(BANGALORE,'[^,]+',1,level) AS BANGALORE,
REGEXP_SUBSTR(DELHI,'[^,]+',1,level) AS DELHI,
REGEXP_SUBSTR(MUMBAI,'[^,]+',1,level) AS MUMBAI
FROM CTE
CONNECT BY REGEXP_SUBSTR(BANGALORE,'[^,]+',1,level) IS NOT NULL
;