Complex SQL Problem with Detailed Solution | Cricket Analytics
Вставка
- Опубліковано 4 жов 2024
- 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
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.
Please make a video of this question using pandas also
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
thanks, another very good question.. cheers!
sql:
with cte as (
select *,
ceiling((ROW_NUMBER() over(partition by case when delivery_type='legal' then 1 else 0 end order by delivery_type,ball_no)*1.0)/6) as over1
from cricket_runs
where delivery_type='legal'
),cte_1 as (
select over1,sum(runs) as sum
,lag(max(ball_no),1,0) OVER(ORDER by over1)+1 as startball
, max(ball_no) lastball
from cte
group by over1
), cte_2 as (
select ball_no,runs+1 AS run_fin
from cricket_runs where delivery_type!='legal'
)
SELECT over1,sum+coalesce(sum(run_fin),0) as sum2
from cte_1 a left join cte_2 b on b.ball_no between a.startball and a.lastball
group by over1,sum
order by over1
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
Aswome Solution broo 🔥
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'
------------------------------------------------------------------------------------------
Just wow,
It was hard to get it first time
Wonderful solution 💯
Thankyou for bringing such amazing content🙏
My pleasure 😊
Loved this question and your contents are amazing...
Thank you
My pleasure 😊
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
how r u able to think this much? whats the secret?
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
Mazedaar
Instead of using row number and ceiling in 1st cte we can also use ntile window function for simplicity
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;
yes too in python Ankit
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;
Awesome ❤
Thank you! Cheers!
Thanks a lot just know revising this concept 😂😂
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
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;
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.
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
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
If possible, please solve the same question using pandas also. Thank you
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
Yooo! fantastic approach using while loop!
you do need 'UPDATE' execution permissions on database though for your solution.
needed in python also
When the new batch will start
need to analyse step by step and practice it to understand clearly for first time listeners
What is tool you are using now?
Postgres
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
mysql is more efficient
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