Find how many hat-trick 4's were scored by the batsmen from the following table in SQL

Question

CREATE TABLE 
cricket_scores ( 
Ball_No INT,Batsman varchar(30) NOT NULL, Bowler Varchar(30) NOT NULL, Runs INT, PRIMARY KEY(Ball_No)
);

INSERT INTO cricket_scores (ball_no,batsman,bowler,runs)
VALUES
(   101 ,   "Kohli" ,   "Anderson"  ,   1   )   ,
(   102 ,   "Sharma"    ,   "Anderson"  ,   1   )   ,
(   103 ,   "Kohli" ,   "Anderson"  ,   4   )   ,
(   104 ,   "Sharma"    ,   "Anderson"  ,   2   )   ,
(   105 ,   "Sharma"    ,   "Anderson"  ,   2   )   ,
(   106 ,   "Sharma"    ,   "Anderson"  ,   6   )   ,
(   201 ,   "Kohli" ,   "Robinson"  ,   1   )   ,
(   202 ,   "Sharma"    ,   "Robinson"  ,   1   )   ,
(   203 ,   "Kohli" ,   "Robinson"  ,   1   )   ,
(   204 ,   "Sharma"    ,   "Robinson"  ,   1   )   ,
(   205 ,   "Kohli" ,   "Robinson"  ,   2   )   ,
(   206 ,   "Kohli" ,   "Robinson"  ,   4   )   ,
(   301 ,   "Kohli" ,   "Curran"    ,   1   )   ,
(   302 ,   "Sharma"    ,   "Curran"    ,   1   )   ,
(   303 ,   "Sharma"    ,   "Curran"    ,   4   )   ,
(   304 ,   "Kohli" ,   "Curran"    ,   4   )   ,
(   305 ,   "Sharma"    ,   "Curran"    ,   4   )   ,
(   306 ,   "Kohli" ,   "Curran"    ,   6   )   ,
(   401 ,   "Sharma"    ,   "Ali"   ,   1   )   ,
(   402 ,   "Kohli" ,   "Ali"   ,   0   )   ,
(   403 ,   "Bumrah"    ,   "Ali"   ,   2   )   ,
(   404 ,   "Bumrah"    ,   "Ali"   ,   1   )   ,
(   405 ,   "Sharma"    ,   "Ali"   ,   4   )   ,
(   406 ,   "Bumrah"    ,   "Ali"   ,   4   )   ,
(   501 ,   "Sharma"    ,   "Root"  ,   4   )   ,
(   502 ,   "Bumrah"    ,   "Root"  ,   2   )   ,
(   503 ,   "Bumrah"    ,   "Root"  ,   2   )   ,
(   504 ,   "Bumrah"    ,   "Root"  ,   6   )   ,
(   505 ,   "Sharma"    ,   "Root"  ,   0   )   ,
(   506 ,   "Pandya"    ,   "Root"  ,   1   )   ,
(   601 ,   "Sharma"    ,   "Overton"   ,   2   )   ,
(   602 ,   "Sharma"    ,   "Overton"   ,   4   )   ,
(   603 ,   "Pandya"    ,   "Overton"   ,   2   )   ,
(   604 ,   "Pandya"    ,   "Overton"   ,   2   )   ,
(   605 ,   "Sharma"    ,   "Overton"   ,   1   )   ,
(   606 ,   "Pandya"    ,   "Overton"   ,   2   )   ,
(   701 ,   "Pandya"    ,   "Curran"    ,   0   )   ,
(   702 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   703 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   704 ,   "Pandya"    ,   "Curran"    ,   4   )   ,
(   705 ,   "Ashwin"    ,   "Curran"    ,   4   )   ,
(   706 ,   "Pandya"    ,   "Curran"    ,   4   )   ,
(   801 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   802 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   803 ,   "Ashwin"    ,   "Curran"    ,   6   )   ,
(   804 ,   "Pandya"    ,   "Curran"    ,   2   )   ,
(   805 ,   "Pandya"    ,   "Curran"    ,   2   )   ,
(   806 ,   "Pandya"    ,   "Curran"    ,   6   )   ,
(   901 ,   "Ashwin"    ,   "Anderson"  ,   4   )   ,
(   902 ,   "Pandya"    ,   "Anderson"  ,   4   )   ,
(   903 ,   "Ashwin"    ,   "Anderson"  ,   4   )   ,
(   904 ,   "Pandya"    ,   "Anderson"  ,   6   )   ,
(   905 ,   "Ashwin"    ,   "Anderson"  ,   6   )   ,
(   906 ,   "Pandya"    ,   "Anderson"  ,   0   )   ,
(   1001    ,   "Rahul" ,   "Anderson"  ,   6   )   ,
(   1002    ,   "Ashwin"    ,   "Anderson"  ,   6   )   ,
(   1003    ,   "Rahul" ,   "Anderson"  ,   6   )   ,
(   1004    ,   "Ashwin"    ,   "Anderson"  ,   1   )   ,
(   1005    ,   "Rahul" ,   "Anderson"  ,   2   )   ,
(   1006    ,   "Rahul" ,   "Anderson"  ,   4   )   ;

From the following table find how many hat-trick 4's (i.e. 4 runs were scored consecutively 3 times) were scored by the batsmen

Note that the column balls_no contains the over and ball number combined for example: 101 = 1st-over 01-ball, 205 = 2nd-over 05-ball, 1006 = 10th-over 06-ball

Not a homework question, this is a question I came across during an interview.

SQLite or MySQL, any or both iterations are welcome.

I was able to figure out the answers, just need a less lengthier query

SELECT overs, COUNT(BALLRANK)
FROM
(SELECT
    ball_no, overs, batsman, bowler,
    runs,
    DENSE_RANK () OVER ( 
      PARTITION BY overs
        ORDER BY ball_no
    ) BALLRANK    
FROM
    (SELECT ball_no, (CASE 
WHEN ball_no BETWEEN 100 AND 107 THEN "1st"
WHEN ball_no BETWEEN 200 AND 207 THEN "2nd"
WHEN ball_no BETWEEN 300 AND 307 THEN "3rd"
WHEN ball_no BETWEEN 400 AND 407 THEN "4th"
WHEN ball_no BETWEEN 500 AND 507 THEN "5th"
WHEN ball_no BETWEEN 600 AND 607 THEN "6th"
WHEN ball_no BETWEEN 700 AND 707 THEN "7th"
WHEN ball_no BETWEEN 800 AND 807 THEN "8th"
WHEN ball_no BETWEEN 900 AND 907 THEN "9th"
WHEN ball_no BETWEEN 1000 AND 1007 THEN "10th"
     ELSE 
        'NA' 
     END) as overs, batsman, bowler,
    runs
FROM cricket_scores
Where runs = 4)
    GROUP BY ball_no
    ORDER BY ball_no)
   GROUP BY overs
   HAVING COUNT(BALLRANK)=3;

1) for home work related questions we always expect the OP to to provide a reasonable attempt at solving the question as you are supposed to learn 2) mysql and sqlite are two different database products. Which one do you use? — Shadow, Aug 27 '21 at 21:30
You should also explain to use your interpretation of how to determine what a hat-trick is — Chris Schaller, Aug 28 '21 at 04:00

rohit prakash · Answer 1 · 2021-08-28T13:53:57.213

-1

with fours_count as (SELECT batsman, count(runs) as hat_trick FROM cricket_scores WHERE runs = 4 group by batsman) select batsman , count(hat_trick) from fours_count where hat_trick = 3 group by batsman;

edited Aug 28 '21 at 13:53

answered Aug 28 '21 at 03:37

rohit prakash

1
1

2

This doesn't address the _hat-trick_ requirement at all. Code only answers are also strongly discouraged, you should provide some explanation on your solution and how it solves the original request. – Chris Schaller Aug 28 '21 at 03:58

Find how many hat-trick 4's were scored by the batsmen from the following table in SQL

1 Answers1