SQL Interview Question - Solution (Part - X) |

[@king-hc6vi]

Good question if anyone wants to clear the basics of joins and case statements

[@MubarakAli-qs9qq]

Nice

[@saiteja-gb8ho]

Nice content bro .. Thanks for sharing such questions .

[@reddymalathi694]

I have a doubt how u r taking runs >=50 condition ,why u r taking 50 I didn't understand that

[@MeanLifeStudies]

Half century

[@AbhijitPaldeveloper]

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;

[@kaushikbendalam7396]

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

[@MeanLifeStudies]

Have you executed this query?

[@MeanLifeStudies]

This is second method I had used

[@kailashpatro5768]

with cte as (
select player_id,runs_scored from matches1
where runs_scored =0) ,
cte1 as (
select player_id,runs_scored from matches1
where runs_scored >= 50 ),
cte3 as (
select c1.*, count(c1.player_id) over( partition by c1.player_id ) as cnt from cte c right join cte1 c1 on c.player_id = c1.player_id
where c.runs_scored is null )
select p.name,sum(c3.runs_scored) as total_score from cte3 c3 inner join players1 p on c3.player_id = p.id
where cnt = 2
group by p.name

[@MeanLifeStudies]

Always prefer concise way of writing SQL queries. Avoiding complexity will helps others can easily understand.

[@kailashpatro5768]

@@MeanLifeStudies ok ji

[@sujanthapa2856]

your input of id is not correct . matches table it has 310 and players table id has 301
with cte as(
select p.name,count(*) as cnt from matches m
join players p on p.id=m.player_id
where m.runs_scored between 50 and 100
group by 1
),
cte1 as (
select p.name,m.runs_scored from matches m
join players p on p.id =m.player_id
where runs_scored=0
),
cte2 as(
select p.name,sum(m.runs_scored) as total_run from matches m
join players p on p.id =m.player_id
group by 1
)
select c.name,c2.total_run from cte c
join cte2 c2 on c.name=c2.name
join cte1 c1 on c.namec1.name
where c.cnt>1

[@MeanLifeStudies]

Kindly excuse me, I mistakenly put 301 instead of 310 in the description. However, I used the right data in SSMS.

[@MeanLifeStudies]

Now I updated that value to 310.

[@Vaibha293]

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