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.

Difference between Rank() and Row_Number() functions

RANK ():- RANK returns ranking (order) of rows based on the number or expression given in the ORDER BY clause.

ROW_NUMBER ():- It returns the sequential number of the row within a group, starting with 1 based on the ORDER BY clause.

We will see the difference between these 2 functions based on an example:

EMPID	ENAME	SALARY	DEPT_ID	RNK	ROWNUM
1	MATT	1000	10	1	1
4	SHANE	1000	12	1	2
2	SCOTT	2000	10	3	3
7	MARY	2000	10	3	4
3	JEAN	2000	10	3	5
5	DEAN	4000	10	6	6
6	LISA	4000	10	6	7

Now, in a practical scenario, we are required to find the all the employees having Nth highest salary.

In such cases, we can use the below query:

SELECT

  E1

.EMPID

  ,

E1.ENAME

  ,

E1.SALARY

  ,

E1.DEPT_ID

  ,

RANK() OVER(ORDER BY E1.SALARY ASC) AS RNK

  ,

ROW_NUMBER() OVER(ORDER BY E1.SALARY ASC) AS ROWNUM

FROM

TL_62917_DLY1_QRMDB

.EMP1 AS E1

WHERE

(

E1.SALARY =

SELECT

SAL2.SALARY FROM

(

SELECT

SAL1.SALARY,SAL1.RNK FROM

(

SELECT

SALARY,RANK() OVER(ORDER BY SALARY DESC) RNK FROM TL_62917_DLY1_QRMDB.EMP1

GROUP

BY 1

)

SAL1

WHERE

SAL1.RNK= ?RNK

)

SAL2

)

Output for a value of ?RNK =2 will be:

EMPID	ENAME	SALARY	DEPT_ID	RNK	ROWNUM
3	JEAN	2000	10	1	1
2	SCOTT	2000	10	1	2
7	MARY	2000	10	1	3

This query will return Nth salary even if duplicate values are present in the Salary column.

If you look at the above table, 2000 is the 2nd highest salary.