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'
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
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
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;
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;
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!
