Complex SQL Problem with Detailed Solution | Cricket Analytics

script:
CREATE TABLE cricket_runs
(ball_no integer,runs integer,delivery_type varchar(20));
insert into cricket_runs values
(1,1,'legal'),(2,2,'legal'),(5,4,'legal'),(6,3,'legal'),(8,3,'legal'),(10,2,'legal'),(11,2,'legal'),(12,0,'legal'),(13,4,'legal'),(14,2,'legal'),(17,3,'legal'),(18,4,'legal'),(19,4,'legal'),(22,1,'legal'),(23,2,'legal'),(24,0,'legal'),(26,0,'legal'),(27,4,'legal'),(28,2,'legal'),(29,4,'legal'),(31,2,'legal'),(32,3,'legal'),(33,1,'legal'),(34,3,'legal'),(35,3,'legal'),(36,2,'legal'),(37,3,'legal'),(38,1,'legal'),(39,0,'legal'),(41,4,'legal'),(42,0,'legal'),(43,1,'legal'),(44,3,'legal'),(45,2,'legal'),(46,0,'legal'),(47,1,'legal'),(48,4,'legal'),(49,1,'legal'),(50,4,'legal'),(51,1,'legal'),(52,1,'legal'),(53,2,'legal'),(54,2,'legal'),(55,3,'legal'),(56,0,'legal'),(57,0,'legal'),(58,1,'legal'),(59,1,'legal'),(60,3,'legal'),(61,4,'legal'),(62,2,'legal'),(63,3,'legal'),(64,2,'legal'),(65,1,'legal'),(66,1,'legal'),(67,2,'legal'),(68,0,'legal'),(69,1,'legal'),(70,1,'legal'),(71,0,'legal'),(72,0,'legal'),(73,1,'legal'),(74,1,'legal'),(75,0,'legal'),(76,4,'legal'),(77,4,'legal'),(78,3,'legal'),(79,3,'legal'),(80,2,'legal'),(81,4,'legal'),(82,4,'legal'),(83,2,'legal'),(84,1,'legal'),(85,3,'legal'),(86,3,'legal'),(87,1,'legal'),(88,3,'legal'),(89,3,'legal'),(90,4,'legal'),(91,3,'legal'),(92,4,'legal'),(93,0,'legal'),(94,3,'legal'),(95,3,'legal'),(96,2,'legal'),(97,2,'legal'),(98,1,'legal'),(99,4,'legal'),(100,4,'legal'),(101,0,'legal'),(102,4,'legal'),(103,2,'legal'),(104,4,'legal'),(105,0,'legal'),(106,3,'legal'),(107,4,'legal'),(108,0,'legal'),(109,0,'legal'),(110,2,'legal'),(111,4,'legal'),(112,3,'legal'),(113,3,'legal'),(114,4,'legal'),(115,1,'legal'),(116,2,'legal'),(117,2,'legal'),(118,3,'legal'),(119,1,'legal'),(120,3,'legal'),(121,4,'legal'),(122,3,'legal'),(123,2,'legal'),(124,4,'legal'),(125,4,'legal'),(126,3,'legal'),(127,2,'legal'),(128,1,'legal'),(129,2,'legal'),(130,3,'legal'),(131,0,'legal'),(132,3,'legal'),(133,3,'legal'),(134,1,'legal'),(135,3,'legal'),(136,3,'legal'),(137,2,'legal'),(138,3,'legal'),(139,4,'legal'),(140,3,'legal'),(141,2,'legal'),(142,2,'legal'),(143,2,'legal'),(144,0,'legal'),(145,4,'legal'),(146,2,'legal'),(147,1,'legal'),(148,2,'legal'),(149,3,'legal'),(150,3,'legal'),(151,0,'legal'),(152,1,'legal'),(153,4,'legal'),(154,2,'legal'),(155,3,'legal'),(156,0,'legal'),(157,1,'legal'),(158,3,'legal'),(159,0,'legal'),(160,1,'legal'),(161,3,'legal'),(162,1,'legal'),(163,3,'legal'),(164,2,'legal'),(165,0,'legal'),(166,1,'legal'),(167,0,'legal'),(168,3,'legal'),(169,3,'legal'),(170,1,'legal'),(171,4,'legal'),(172,0,'legal'),(173,4,'legal'),(174,0,'legal'),(175,3,'legal'),(176,4,'legal'),(177,0,'legal'),(178,2,'legal'),(179,2,'legal'),(180,2,'legal'),(181,2,'legal'),(182,1,'legal'),(183,4,'legal'),(184,2,'legal'),(185,0,'legal'),(186,0,'legal'),(187,3,'legal'),(188,0,'legal'),(189,1,'legal'),(190,0,'legal'),(21,0,'wd'),(191,3,'legal'),(192,2,'legal'),(193,2,'legal'),(194,0,'legal'),(195,2,'legal'),(196,1,'legal'),(197,1,'legal'),(198,4,'legal'),(199,0,'legal'),(200,0,'legal'),(201,0,'legal'),(202,2,'legal'),(203,3,'legal'),(204,2,'legal'),(205,3,'legal'),(206,2,'legal'),(207,0,'legal'),(208,0,'legal'),(209,1,'legal'),(210,0,'legal'),(211,1,'legal'),(212,2,'legal'),(213,4,'legal'),(214,1,'legal'),(215,0,'legal'),(216,1,'legal'),(217,2,'legal'),(218,1,'legal'),(219,2,'legal'),(220,1,'legal'),(221,1,'legal'),(222,1,'legal'),(223,4,'legal'),(224,2,'legal'),(225,1,'legal'),(226,2,'legal'),(227,4,'legal'),(228,0,'legal'),(229,4,'legal'),(230,2,'legal'),(231,4,'legal'),(232,2,'legal'),(233,3,'legal'),(234,0,'legal'),(235,3,'legal'),(236,1,'legal'),(237,3,'legal'),(238,1,'legal'),(239,4,'legal'),(240,4,'legal'),(241,2,'legal'),(242,3,'legal'),(243,0,'legal'),(244,3,'legal'),(245,2,'legal'),(246,3,'legal'),(247,3,'legal'),(248,2,'legal'),(249,1,'legal'),(250,3,'legal'),(251,3,'legal'),(252,4,'legal'),(253,3,'legal'),(254,3,'legal'),(255,4,'legal'),(256,0,'legal'),(257,3,'legal'),(258,3,'legal'),(259,0,'legal'),(260,1,'legal'),(261,3,'legal'),(262,3,'legal'),(263,3,'legal'),(264,3,'legal'),(265,3,'legal'),(266,1,'legal'),(267,1,'legal'),(268,0,'legal'),(269,3,'legal'),(270,3,'legal'),(271,4,'legal'),(272,2,'legal'),(273,2,'legal'),(274,1,'legal'),(275,1,'legal'),(276,0,'legal'),(277,4,'legal'),(278,3,'legal'),(279,1,'legal'),(280,3,'legal'),(281,3,'legal'),(282,1,'legal'),(283,3,'legal'),(284,0,'legal'),(285,2,'legal'),(286,0,'legal'),(287,2,'legal'),(288,4,'legal'),(289,4,'legal'),(290,0,'legal'),(291,0,'legal'),(292,4,'legal'),(293,0,'legal'),(294,2,'legal'),(295,4,'legal'),(296,4,'legal'),(297,2,'legal'),(298,4,'legal'),(299,2,'legal'),(300,1,'legal'),(301,4,'legal'),(302,4,'legal'),(303,0,'legal'),(304,1,'legal'),(305,1,'legal'),(306,2,'legal'),(307,1,'legal'),(308,1,'legal'),(309,4,'legal'),(310,0,'legal'),(3,1,'nb'),(15,0,'nb'),(16,1,'nb'),(25,4,'nb'),(4,4,'wd'),(9,0,'wd'),(20,3,'wd'),(30,3,'wd'),(40,3,'wd'),(7,0,'legal')
#sql #dataengineer #cwc2023 #cricketanalysis

[@ankitbansal6]

Master the art of SQL @ Rs 1999 with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch. www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354 The course contains 2 portfolio projects and a bonus session on Tableau. 100+ interview problems to crack any SQL INTERVIEW.

[@dasoumya]

Please make a video of this question using pandas also

[@musaalamdarali6132]

with cte as (select *,ntile(50) over(order by ball_no) as overs from cricket_runs where delivery_type = 'legal'), overs as (select overs,lag(max(ball_no),1,0) over(order by overs)+1 as min,max(ball_no)as max from cte group by overs) select overs, sum(case when delivery_type='legal' then runs else 0 end)+sum(case when delivery_type in ('nb','wd') then 1 else 0 end)+sum(case when delivery_type in ('nb','wd') then runs else 0 end)as runs from cricket_runs join overs on cricket_runs.ball_no = overs.min group by 1

[@Alexpudow]

Hi, Ankit. This is my approach. select overn, sum(runs2) sm from ( select *, case delivery_type when 'legal' then runs else runs +1 end runs2 ,ceiling( case when count(case delivery_type when 'legal' then 1 end) over(order by ball_no)% 6 = 0 and delivery_type 'legal' then count(case delivery_type when 'legal' then 1 end) over(order by ball_no) +1 else count(case delivery_type when 'legal' then 1 end) over(order by ball_no) end *1.0 / 6 ) overn from cricket_runs) t group by overn

[@soumyaranjandash3597]

Aswome Solution broo 🔥

[@varunas9784]

Fantastic Video as always Ankit! here's my take on the solution (included extra columns in final output for reference) -------------------------------------------------------------------------------------- with cte as (select *, Floor((COUNT(delivery_type) over (partition by delivery_type order by ball_no))) over_count, Sum(case when delivery_type = 'nb' or delivery_type = 'wd' then runs+1 else runs end) over (order by ball_no) run_total from cricket_runs ), cte2 as (select *, case when over_count%6 = 0 then 'Y' else 'N' end as 'overs' from cte where delivery_type = 'legal' ) select (over_count/6) Overs, run_total, ((Lag(ball_no, 1,0) over (order by ball_no)) + 1) as First_ball_over, ball_no as Last_ball_over from cte2 where overs = 'Y' ------------------------------------------------------------------------------------------

[@vishwassharma3312]

Mazedaar

[@avi8016]

Just wow, It was hard to get it first time Wonderful solution 💯 Thankyou for bringing such amazing content🙏

[@ankitbansal6]

My pleasure 😊

[@vikramjitsingh6769]

Alternate easy way is using some case statements -----------------with cte as (select *, sum(finalover) over (order by ball_no) as overss from (select *, lag(flag,1,0) over(order by ball_no) as finalover from (select *, case when x%6 = 0 and delivery_type = 'legal' then 1 else 0 end as flag from (select * , sum(overs) over(order by ball_no) as x from (select *, case when delivery_type = 'legal' then 1 else 0 end as overs from cricket_runs order by ball_no)y)z)x)a) select overss, sum(legal_runs), sum(non_runs), sum(extraa_runs) from (select *, (case when delivery_type = 'legal' then runs else 0 end) as legal_runs, (case when delivery_type 'legal' then runs else 0 end) as non_runs, (case when delivery_type 'legal' then 1 else 0 end) as extraa_runs from cte )x group by overss

[@Tarasankarpaul1]

Awesome ❤

[@ankitbansal6]

Thank you! Cheers!

[@muditmishra9908]

Interesting question. Sharing my sol below: with cte1 as ( select *, count(case when delivery_type = 'legal' then ball_no else null end) over(order by ball_no) as result from cricket_runs order by ball_no ) , cte2 as ( select *, case when delivery_type != 'legal' and result%6 = 0 then result+1 else result end as result_2 from cte1 ) , cte3 as ( select *, count(case when result_2 %6 != 0 then null else result_2 end ) over(order by ball_no)+1 as result_3 from cte2 ) , cte4 as ( select *, case when delivery_type = 'legal' then runs else runs+1 end as total_run, case when result_2%6 =0 then result_3 -1 else result_3 end over_no from cte3 ) select over_no, sum(total_run) as runs from cte4 group by over_no

[@garvitchaudhary4499]

how r u able to think this much? whats the secret?

[@dasoumya]

Instead of using row number and ceiling in 1st cte we can also use ntile window function for simplicity

[@christopherdavasekaran3145]

Thanks a lot just know revising this concept 😂😂

[@muthusamyrs6356]

Loved this question and your contents are amazing... Thank you

[@ankitbansal6]

My pleasure 😊

[@bhumikalalchandani321]

yes too in python Ankit

[@gabrielcarbajalcarbajal3025]

easy approach on MySQL: with cte as( select *, sum(case when delivery_type = "legal" then 1 else 0 end) over(order by ball_no) as a from cricket_runs), cte2 as( select *, ceiling((case when a%6 = 0 and delivery_type!="legal"then a+1 else a end)/6) as no_over from cte) select no_over, sum(case when delivery_type!="legal" then 0 else runs end) as runs, sum( case when delivery_type != "legal" then runs+1 else runs end) as extra_runs from cte2 group by no_over;

[@satyendrakumar3319]

When the new batch will start

[@user-de7mr2uv2t]

needed in python also

[@175off66]

If possible, please solve the same question using pandas also. Thank you

[@karthikmaddi]

My Answer: with cte as (select *, 1 as total_runs, case when delivery_type='legal' then 1 else 0 end as legal_runs from cricketruns), c2 as (select *, truns+(t_balls-balls) as tt_runs ,balls%6 as tt from (select ball_no, sum(runs) over(order by ball_no) as truns ,delivery_type, sum(total_runs) over(order by ball_no) as t_balls, sum(legal_runs) over(order by ball_no) as legal_balls from cte) a) ,cte3 as (select ball_no, tt_runs, sum(1) over(order by ball_no) as over_no from c2 where tt=0 and delivery_type='legal') select over_no, tt_runs-(lag(tt_runs, 1, 0) over(order by over_no)) as runs_scored from cte3

[@sumitagarwal880]

Solution without using join and easy to understand: with cte_legal_flag as ( select ball_no, runs, case when delivery_type='legal' then 1 else 0 end as is_legal from cricket_runs ), cte_running_delivery as ( select *, sum(is_legal) over(order by ball_no) as legal_ball_no from cte_legal_flag ), cte_legal_over as ( select ball_no, runs, case when is_legal = 0 and legal_ball_no%6 = 0 then ceiling((legal_ball_no+1) / 6) else ceiling(legal_ball_no / 6) end as over_no from cte_running_delivery ) select over_no, sum(runs) + count(over_no) - 6 as total_runs from cte_legal_over group by over_no;

[@anandahs6078]

Your solution is wrong. you have not considered additional +1 run for wd or nb. Also first ball of 2nd over which is nb has been bucketed under 1st over in your solution. Please compare your results with Ankit Bansal results.

[@rajeshgoud9619]

need to analyse step by step and practice it to understand clearly for first time listeners

[@adityakishan1]

with tb1 as ( select A.*, ceiling(row_number() over(order by ball_no)*1.0/6) as over_no from cricket_runs A where delivery_type = 'legal' ) ,tb2 as ( select over_no,max(ball_no) as max_ball_no, sum(runs) as total_runs from tb1 group by over_no ) ,tb3 as ( select A.ball_no as extra_bn, A.runs as extra_runs, B.*,row_number() over(partition by A.ball_no order by max_ball_no) as rn from cricket_runs A inner join tb2 B on A.ball_no < B.max_ball_no where A.delivery_type 'legal' ) select B.over_no , B.total_runs + isnull(A.total_extra_runs,0) as total_runs_final from tb2 B left join (select over_no, sum(extra_runs) + count(extra_runs) as total_extra_runs from tb3 where rn=1 group by over_no) A on B.over_no = A.over_no

[@rajeshwarigundam2363]

What is tool you are using now?

[@ankitbansal6]

Postgres

[@aniketghodinde3041]

Solved using while loop to find over_no DROP TABLE IF EXISTS #temp; with c1 as ( select row_number() over (order by ball_no) as rn,null as over_no,ball_no,delivery_type,CASE WHEN delivery_type != 'legal' THEN runs+1 ELSE runs END AS runs from cricket_runs ) Select * into #temp from c1 select * from #temp DECLARE @maxi int = (Select count(*) from #temp) DECLARE @i int,@j int,@k int SET @i = 1 SET @j = 0 SET @k = 1 WHILE @i

[@varunas9784]

Yooo! fantastic approach using while loop! you do need 'UPDATE' execution permissions on database though for your solution.

[@2412_Sujoy_Das]

Got stuck at halfway sir.... where I could figure out only the ball_no where the over is completed but couldn't figure out how to spread the ball_no below it under same over...... Here's my solution with a partial influence of your solution: WITH CTE_1 AS (Select COALESCE (LAG( MAX(ball_no)) OVER (order by (SELECT NULL)),0) +1 as first_ball, MAX(ball_no) as last_ball, SUM(Net_Runs) as Net_Runs, Over_Number FROM (Select ball_no, SUM(runs) as Net_Runs, CEILING((DENSE_RANK() OVER (order by ball_no)*1.0)/6) as Over_Number from cricket_runs WHERE delivery_type = 'legal' GROUP BY ball_no) xyz GROUP BY Over_Number) Select MAX(first_ball), MAX(last_ball), MAX(Net_Runs)+COALESCE(SUM(net_runs_1),0) as Tot_Runs, Over_Number from CTE_1 A LEFT JOIN (Select ball_no, runs+1 as net_runs_1 from cricket_runs WHERE delivery_type !='legal') xyz ON ball_no BETWEEN first_ball AND last_ball GROUP BY Over_Number;

[@himanshuparihar9888]

with cte as ( select * , ifnull(lag(e) over(order by e) ,1) as p from ( select o, max(ball_no) as e , sum(runs) as t from ( select * , ntile(50) over() as o from cricket_runs where delivery_type not in ('nb' , 'wd') order by ball_no ) as s group by o ) as d ) , cte1 as ( select * from cricket_runs where ball_no not in (select ball_no from cricket_runs where delivery_type not in ('nb' , 'wd') ) ) select o , t + ifnull(sum(runs) ,0) + ifnull(count(runs) , 0 ) as total_score from cte as a left join cte1 as b on b.ball_no between a.p and a.e group by o , t order by p

[@ayushisingla1240]

mysql is more efficient

[@ramchavali5825]

Hey Ankit, Thanks for the solution, Can you please let me know if this is correct. The output matches. with cte as ( select ball_no, (301 - sum(case when delivery_type = 'legal' then 1 else 0 end) over (order by ball_no desc)) as cumm_over, case when delivery_type = 'legal' then runs else runs + 1 end as final_runs from cricket_runs ), cte1 as ( select case when (cumm_over % 6 = 0) then cumm_over / 6 else (cumm_over / 6) + 1 end as cric_over, final_runs from cte order by cric_over ) select cric_over, sum(final_runs) as final_runs from cte1 group by cric_over order by cric_over

[@akshayb451]

in SQL Server with x1 as ( select * from dbo.cricket_runs ), x2 as ( select *,lag(ball_no,5) over(order by ball_no) l, row_number() over(order by ball_no) rn from x1 where delivery_type = 'legal' ), x3 as ( select ball_no,runs,delivery_type,ov from ( select *,max(rn/6) over(partition by mi) as ov from ( select a.*,b.ball_no as ma,b.l as mi from x2 a cross join (select * from x2 where rn%6 =0) b )t where ball_no >= mi and ball_no