Amazing Interview Question - Round 3 - Solved using Teradata Reset when functionality

The below scenario was given to calculate the series of ball_nos for which the highest no. of dot balls were bowled.

/* Amazing Interview round 3 */

create multiset table retail.cricket
(
Ball_no integer,
Runs_scored varchar(5)
) primary index(ball_no);

database retail;

insert into cricket  values(1,1);
insert into cricket  values(2,2);
insert into cricket  values(3,0);
insert into cricket  values(4,0);
insert into cricket  values(5,0);
insert into cricket  values(6,4);
insert into cricket  values(7,0);
insert into cricket  values(8,0);
insert into cricket  values(9,'W');
insert into cricket  values(10,1);
insert into cricket  values(11,0);
insert into cricket  values(12,0);
insert into cricket  values(13,0);
insert into cricket  values(14,1);
insert into cricket  values(15,4);

select * from cricket;

Dot balls are balls where 0 runs are scored

alter table cricket add modified_ball_no integer;
drop table cricket_modifed;
create table cricket_modified
as
(
select ball_no
,runs_scored
, row_number() over(partition by runs_scored order by ball_no
reset when ball_no - 1 > /* this statement gets the ball no of last row and resets the rank when the balls are not consecutive */
sum(ball_no) over(partition by runs_scored order by ball_no rows between 1 preceding and 1 preceding)) as rnk
from cricket
) with data
primary index(ball_no)
;

/* Maximum consecutive dot balls */
select (ball_no - max(rnk) + 1) as dot_ball_start , ball_no, rnk as count_of_dot_balls from cricket_modified where rnk in (select max(rnk) from cricket_modified)
group by ball_no, rnk;

The series of balls where only 0 runs were scored consecutively:

Dot Balls using Teradata Reset function

Like us on Google+, Twitter or Facebook if this post was helpful. For classroom/online training, call at the number given at the top. Mention the Discount code for offers.