SQL Interview Problem - Solution (Part -XXII) |

#dataanalyst #sqlfordataengineer #education #dataanalytics
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
Table Create and insert statements:
----------------------------------------------------------
create table Players_info (name varchar(20), role varchar(20), performance int)
insert into Players_info values('virat', 'bat', 89),('axar', 'ball',3),
('gill', 'bat',47),('buvi','ball', 1),('shami','ball', 5),
('shardul', 'ball',2),('sreyas', 'bat',54),('rohit', 'bat',38)

[@AjayKumar-xi3rp]

with cte as(select name,role,performance, rank() over(partition by role order by performance desc) as rank from players)
select p2.name as batsman,p1.name as bowler,p1.rank as rank from cte p1 join cte p2 on p1.rank=p2.rank and p1.role!=p2.role and p1.role='ball'

[@kailashpatro5768]

with cte as (
select *, row_number() over(partition by role order by performance desc) as rnk,
(case when role = 'ball' then name else null end) as batsman,
(case when role = 'bat' then name else null end ) as ball from Players_info )
select max(batsman) as batsman, max(ball) as ballwer, rnk from cte
group by rnk

[@VijayKumar-bx2ov]

with cte as (
select *,dense_rank() over(partition by role order by performance desc ) as rank from Players_info),
cte_2 as (
select string_agg(name, ' : ') as mark, rank from cte
group by rank order by 2 asc)
SELECT
split_part(mark, ' : ', 1) AS Baller,
split_part(mark, ' : ', 2) AS Batsman,
rank
FROM cte_2

[@anilkumark3573]

with cte as (
select
case when role='bat' then name end as batsmen,
case when role='ball' then name end as bowler,
rank() over (partition by role order by performance desc) as playerrank
from players_info
)
select max(batsmen) batsmen, max(bowler) bowler, playerrank from cte
group by playerrank;

[@Manifestion_kannada]

@@anilkumark3573 without CTE ?

[@Manifestion_kannada]

Without CTE from next video
Solve both with CTE without CTE

[@MeanLifeStudies]

Okay.

[@vasanthkumar-zw3xf]

with cte as (
SELECT CASE WHEN ROLE='bat' THEN NAME END bat,
CASE WHEN ROLE='bat' THEN performance END score,
dense_rank() over(partition by role order by performance desc) rnk
FROM PLAYERS_INFO where role='bat'),
cte1 as ( select CASE WHEN ROLE='ball' THEN NAME END ball,
CASE WHEN ROLE='ball' THEN performance END wicket
,dense_rank() over(partition by role order by performance desc) rk
from PLAYERS_INFO where role='ball')
select c.bat,c1.ball,rnk from cte c join cte1 c1
on c.rnk=c1.rk;

[@varunas9784]

Fantastic! I'm mentioning your channel on my Linkedin post, hope you're good with that.
Please make more videos as such, i.e., guessing the problem statement.
I see everyone answering in comments but the problem statement could be something like:
"Rank the batsman and bowlers, with highest performance being ranked first. Return the result set in the order [Batsman] [Bowler [Rank]]"
my attempt:
==============================================
with ranked_batsman as (select *,
DENSE_RANK() over(partition by role order by performance desc) [rank]
from Players_info
where role = 'Bat'),
ranked_bowler as (select *,
DENSE_RANK() over(partition by role order by performance desc) [rank]
from Players_info
where role = 'Ball')
select r1.name [Batsman], r2.name [Bowler], r1.[rank]
from ranked_batsman r1
join ranked_bowler r2 on r1.[rank] = r2.[rank]
==============================================
Thanks!

[@MeanLifeStudies]

Thank you. I will do.