SQL Cricket Match Problem | Solving SQL Interview Problem

In this video, let's solve an SQL interview problem related to a Cricket match.
This is a real SQL Query asked during a SQL Job Interview.

This is a good SQL problem to test out your SQL Query writing skills.

This video is sponsored by OdinSchool.

Download Dataset, SQL scripts for free from my blog below:

Thanks for watching!
Thoufiq | techTFQ

[priyasarathy]

Thank you Toufiq!

I tried in the following way:
SELECT over_num, SUM(runs) tot_runs
FROM
(
SELECT ceil(balls / 6) over_num, m.runs
FROM match_score m
)
GROUP BY over_num
ORDER BY over_num;

Note: Tested in Oracle SQL

[chaitanyainamdar]

5 runs are possible.
There should be wide+4.
No ball +4.
Overthrow 1+4.

There is a possibility of 5 runs.

Query was good. Please create a video more like this with new scenarios. Thank you!

[KisaanTuber]

Thanks for sharing this video. Here is my approach:
SELECT
case when balls%6=0 then balls/6 else (balls/6)+1 end as over,
sum(runs) as score
FROM match_score
GROUP by 1;

[saibabuvasamsetti]

Useful info Toufiq!!

I have tried the following way in snowflake:

with raw_data as
(select case when ball%6 != 0 then to_decimal(left(round(ball/6, 1), 1)+1) else to_decimal(ball/6) end as rank,
           ball,
           score from demo_db.public.cricket)

select    rank, sum(score) as runs
from raw_data
group by rank

[reviewbyanand]

great to know about ntile and random function and there use in sql

[ajithomas]

My approach will be. Creating a column named over. Where value will be quotient when divided by 6. Then group by it. 12:47

[RamaKrishna-zz]

with cte1 as(
select ball, runs, sum(runs) over(order by balls rows between 5 preceding and current row as totalruns_per_over)
), cte2 as(
select *, row_number() over(order by ball ) as rn from cte1 where ball%6==0
)
select rn, totalruns_per_over from cte2

[christopherbird]

5 runs are possible. Overthrows. Single + 4 overthrows, etc.

[junn]

Awesome Mr Toufiq
New window function i learnt....

Thanks so much

[HARSHRAJ-gpve]

with cte as(
select balls FROM match_score where balls%6=0
), cte1 as(
select balls as last_ball, COALESCE(LAG(balls+1)OVER(), 1) as first_ball FROM cte
), cte2 as(
select last_ball, first_ball, runs FROM cte1 JOIN match_score where match_score.balls BETWEEN first_ball and last_ball
), cte3 as(
select ROUND((last_ball/6), 0) AS over1, SUM(runs) as runs1 FROM cte2 GROUP BY last_ball
)
select * FROM cte3;

[HARSHRAJ-wzrp]

with cte as(
SELECT balls, runs as "1stball", LEAD(runs, 1)OVER() as "2stball",
LEAD(runs, 2)OVER() as "3rdtball",
LEAD(runs, 3)OVER() as "4thball",
LEAD(runs, 4)OVER() as "5thball",
LEAD(runs, 5)OVER() as "6thball"
FROM match_score where balls%6=1
)
select ROUND(balls/6)+1 as "overs", (1stball+2stball+3rdtball+4thball+5thball+6thball) as "runs" FROM cte;

[dev]

select ceil(ball_number/6) as overs, sum(run) from ball_runs group by ceil(ball_number/6) order by overs;

[atishpradhan]

with cte as(
select balls, runs, ceil(balls/6) as over from match_score)
select over, sum(runs) from cte
group by over;

[shivsharma]

select ceil(balls/6) as overs, sum(runs) as runs from match_score
group by ceil(balls/6);

[shrikantshirsekar]

I always learn new things from your video thanks brother

[PankajAgnihotri-sk]

i have put the 786th like on the video. Thanks for sharing such interesting questions.

[pavanrebel]

It's possible to score five runs in one ball
First is wide + boundary and
Second 1 run + byes 4runs(thrown by fielder)

[tanmoykarmakar]

WITH cte AS (select ((balls-1)/6) AS groupid, SUM(runs) over (partition by (balls-1)/6 order by balls ASC) AS Score from cricket) <---- cumulative sum of runs using window function
select (groupid +1) AS over, MAX(score) from cte
Group by groupid
order by over ASC;

[shivinmehta]

with cte as (
SELECT *, ceil(balls/6::decimal) as "over" FROM match_score ORDER BY Balls
)
select over, sum(runs) from cte group by 1 order by 1;

[saraswathirajendran]

MYSQL SOLUTION:

👇For creating data, recursive query is used:👇
INSERT INTO match_score
WITH RECURSIVE numbers AS (
SELECT 1 AS balls, ROUND(RAND() * 6) AS runs
UNION ALL
SELECT 1+ balls AS balls, ROUND(RAND()* 6) AS runs
FROM numbers
WHERE 1+ balls<=120)
SELECT * FROM numbers;

👇final solution:👇
SELECT CEIL(balls/6) AS `Over`, SUM(runs) AS runs
FROM match_score
GROUP BY `Over`
ORDER BY `Over`;