SQL Cricket Match Problem | Solving SQL Interview Problem

[@priyasarathy8213]

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

[@sh-pa-bh]

Excellent solution ❤

[@chaitanyainamdar3478]

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;

[@devarapallivamsi7064]

Brilliant !!

[@saibabuvasamsetti6455]

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

[@junn0073]

Awesome Mr Toufiq New window function i learnt.... Thanks so much

[@techTFQ]

Glad to hear that

[@reviewbyanand]

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

[@christopherbird5520]

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

[@demon2.099]

Actually He Already Mentioned That There Are Neither No balls Nor Wide Balls

[@PankajAgnihotri-s6k]

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

[@techTFQ]

Awesome, thank you!

[@RamaKrishna-z3z]

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

[@shrikantshirsekar]

I always learn new things from your video thanks brother

[@techTFQ]

You're welcome ☺️

[@HARSHRAJ-wz2rp]

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;

[@Ameem-rw4ir]

thanks toufig! solving this problem.

[@ajithomas1590]

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

[@abuzainah1545]

Very eloquently solved the problem with ease. Thank you @Techtfq

[@techTFQ]

Glad it helped

[@HARSHRAJ-gp6ve]

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;

[@atishpradhan9164]

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

[@pavanrebel683]

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

[@dev4128]

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

[@shivsharma9153]

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

[@SohailKhan-si9mx]

Another great tutorial thank you Toufic!

[@techTFQ]

You're welcome

[@tanmoykarmakar3917]

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)

[@abdniv1985]

5 runs is possible with wide 4 Ball hitting helmet Batsman scoring 3 and Overthrow 2

[@mithunkt1648]

Hi, this may look a bit cumbersome to review. kindly review if the process in right. I have used reverse table to get multiples of 6 at beginning, followed by flagging of multiples of 6, then a drop down operation, then aggregating the runs and reversing the order of overs. Happy SQLing everyone. -- reverse order to bring multiples of 6 at the beginning with cte as ( Select balls, runs from table order by balls desc ), --flag multiples of 6 as 1 for drop down operation in next cte balls_flag as ( Select balls, runs, case when mod(balls,6) = 0 then 1 else 0 end as balls_flag from cte ), -- use count aggregation with window function to create new row which will be used to aggregate runs over wise in next cte. over_group as ( select balls, runs, balls_flag, count(balls_flag)over(order by balls desc) as over_group from balls_flag ), -- aggregate runs over wise by grouping on over_group. however output will give reverse order of over aggregation. eg runs in over 20 will be aggregated as over 1. this will be reversed in next cte rev_over_sum as ( Select over_group as over_rev, sum(runs) as runs from over_group group by 1 or ), -- create new column using row number window function by ordering in desc order of over_rev reorder_overs as ( Select over_rev, runs, row_number()over(order by over_rev desc) as overs from rev_over_group ) --select reversed overs and runs column to get desired results Select overs, runs from reorder_overs

[@shivinmehta7368]

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;

[@datazerohero]

Enjoyed your SQL interview tips! 🚀 I've just kicked off a channel diving into SQL questions and solutions. Would love your insights and feedback! Keep rocking the SQL world

[@techTFQ]

Thank you

[@zainulabadin3057]

Thanks for sharing valuable knowledge, Can you please suggest some extended technologies to learn , I have SQL/PLSQL real time experience...

[@techTFQ]

Python, Power BI

[@tarunpothala2071]

Hello All, I felt the approach used in the video is little lengthier. Here is my solution for same ;with cric_overs as -------- Data preparation ( Select 1 AS balls,2 AS runs union all Select 2 AS balls,3 AS runs union all Select 3 AS balls,4 AS runs union all Select 4 AS balls,6 AS runs union all Select 5 AS balls,1 AS runs union all Select 6 AS balls,1 AS runs union all Select 7 AS balls,2 AS runs union all Select 8 AS balls,3 AS runs union all Select 9 AS balls,4 AS runs union all Select 10 AS balls,4 AS runs union all Select 11 AS balls,2 AS runs union all Select 12 AS balls,6 AS runs union all Select 13 AS balls,2 AS runs union all Select 14 AS balls,1 AS runs union all Select 15 AS balls,4 AS runs union all Select 16 AS balls,1 AS runs union all Select 17 AS balls,2 AS runs union all Select 18 AS balls,4 AS runs ) ------------------------------- Data preparation ------------------ logic that solves the problem ---------- ,cte_flag as ( Select balls, runs, CASE when balls % 6 = 0 THEN balls / 6 else (balls / 6) + 1 end as overs from cric_overs ) select overs ,SUM(runs) as TotalRunsEachOver from cte_flag Group by overs Order by overs

[@rsrithvik]

Hi First of all thank you , your videos have been really helpful. I tried visiting your blog but it wont open.

[@shaikmujeer9560]

Hi toufiq bhayya

[@DEEPAKKUMAR-ij7hw]

Hi TFQ, kindly make videos on power bi

[@techTFQ]

Noted

[@shekhark1139]

Very interesting video, Ntile came to my mind at first for segregation of overs and count, this is because, I have watched your video in the past you explained, I learn new things from your vids. Thanks a lot. My only concern is, generate series is not working in the ssms(sql server) that im using. Its compatibility is 130. Can I use row_number to generate numbers until 120 or so.

[@latestlaunchesandreviews5489]

You can also use recursive cte to generate series of numbers and it works in all the databases

[@manojcheera9769]

Man you are awesome

[@techTFQ]

Thank you very much 😊

[@vidyasagarjkollurkar5726]

how about this method ... select ((balls-1)/6)+1 as balls, sum(runs) from cricket group by ((balls-1)/6)+1 order by 1;

[@kartikwagh6971]

Hi Toufiq ,can please tell me what is a use of Within group clause it will be helpful if take example

[@kemjhonponce3881]

Hello, sir! Could you please make a video about DCL or SQL Triggers?

[@techTFQ]

Noted

[@mounikagundlapalli5428]

Sir when will be your new sql bootcamp

[@techTFQ]

Probably in Jan. I'll announce on LinkedIn

[@karthiksn7441]

Same was asked to me in a interview few months ago

[@techTFQ]

👍

[@lakshmikanth1988]

yes it was interesting

[@techTFQ]

Thank you very much 😊

[@yogeshtalaskar1468]

Learnt something new things

[@techTFQ]

cool 👌

[@saraswathirajendran2395]

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

[@ketanverma7839]

i have a quesion in SQL is it possible to connect with you on any platform to discuss about that problem ?

[@Mjeditz87]

Can we do by using case statement but lengthy code

[@rajkumarrajan8059]

Does SQL server got generate_Series function?

[@hiteshmalviya8910]

learn new thing..

[@techTFQ]

Great 👍

[@bhushansagar7558]

❤

[@techTFQ]

Thank you

[@fathimafarahna2633]

👍👍👍

[@techTFQ]

Thank you very much 😊

[@janauppara]

Why functions are called as window functions in SQL

[@rajishn]

for MYSQL ?

[@mohammadnadeemnadeem5021]

Wide+4=5

[@virendratrivedi3975]

i do not how but might be any window fun ?

[@DailyDiary4U]

while using Random function in microsoft sql server management the output are the same value to all the 120 Balls for e.g Balls 1, random value is 0.855, the value for remaining 119 balls are also showing the same random value as in ball 1 i.e 0.855. Plz let me know how to solve this pblm

[@shekhark1139]

Thx for letting know

[@chandrashekhara1]

i am unable to use the Generate_series function in SQL, could you please help me on this

[@SatyalekhyaDsln]

thank you for the video sir but can you explain the same in the MySQL workbench, that would be really helpful

[@latestlaunchesandreviews5489]

NTILE() fn is not an inbuild fn in MYSQL , although you can use these two ways in MSQL workbench to solve this problem -- Method 1 WITH temp_overs AS (SELECT *, CEIL(balls/6) AS overs FROM match_score) SELECT overs, SUM(runs) AS runs_per_over FROM temp_overs GROUP BY overs -- Method 2 SET @overs = 0; WITH temp_overs AS (SELECT *, @overs := CASE WHEN balls % 6 = 1 THEN @overs + 1 ELSE @overs END AS overs FROM match_score) SELECT overs, SUM(runs) AS runs_per_over FROM temp_overs GROUP BY overs There may be other methods too.

[@Shivani-yk7tw]

can anybody tell me how to insert data in my sql server as some function is not supported

[@shyamkumar-th4zp]

can someone help me how to use cte. . i am a beginner help me with sql

[@vishalsonawane.8905]

Remains

[@Reacher1998]

4 wides

[@programmerhrutik9933]

Bhai ads jyada ho Gaye..

[@your_old_nemesis]

But we can score 5 runs in a single ball wide+four.

[@AkashSingh-ud4ng]

Wide is not calculated as ur run ... That's why he said we can score means through our bat..

[@AkashSingh-ud4ng]

Only possible by 5 singles but practically it's not possible

[@AkashSingh-ud4ng]

And also possible by overthrow but again which is not possible in every ball

[@neel9010]

If it touches helmet behind WK it's 5 runs.

[@vineethmenon2372]

is ntile function divides the number of rows by ntile value ? in that case suppose if i have 152 balls.

[@anudeepreddy5559]

Plsql bootcamp please

[@techTFQ]

Noted

[@dr.aravindacvnmamit3770]

But 5 runs can be scored if the ball is noball and the batsman hit 4 !!! if am not wrong please correct me

[@AviralOjha]

If you are not wrong how come someone can correct you dumb? Rip English 😂

[@ishtyaqe]

You are right ✅️

[@demon2.099]

Actually He Already Mentioned That There Are Neither No balls Nor Wide Balls

[@PrasannaChalamalasetti-gi6im]

with cte as ( select balls,runs,(case when balls % 6=0 then balls/6 else balls/6+1 end) as overs from match_score) select overs,sum(runs) runs_per_over from cte group by overs;

[@danishhasan49]

You are a genius man!!!!