Sql interview query

 Given below table emp as input which has two columns 'Group' and 'sequence'.

Write a sql query to find the maximun and minimum values of continuous

'sequence' in each Group.

I/p:

select * from emp;

Group sequence

A 1

A 2

A 3

A 5

A 6

A 8

A 9

B 11

C 1

C 2

C 3

output:

Group MIn_seq Max_seq

A 1 3

A 5 6

A 8 9

B 11 11

C 1 3

use test;

Create table Emp_grp

(

 Group_id varchar(10),

 sequence int

 );

 

insert into emp_grp values('A',1);

insert into emp_grp values('A',2);

insert into emp_grp values('A',3);

insert into emp_grp values('A',5);

insert into emp_grp values('A',6);

insert into emp_grp values('A',8);

insert into emp_grp values('A',9);

insert into emp_grp values('B',11);

insert into emp_grp values('C',1);

insert into emp_grp values('C',2);

insert into emp_grp values('C',3);

select group_id,sequence,row_number() over(partition by group_id order by sequence) as rnk,

sequence - row_number() over(partition by group_id order by sequence) as diff  from emp_grp;

group_id sequence rnk    diff

A           1 1    0

A           2 2    0

A           3 3    0

A           5 4    1

A           6 5    1

A           8 6    2

A           9 7    2

B           11        1      10

C           1 1    0

C           2 2    0

C           3 3    0

         

 

select group_id,

min(sequence),

max(sequence)

from

(select group_id,sequence,row_number() over(partition by group_id order by sequence) as rnk,

sequence - row_number() over(partition by group_id order by sequence) as diff  from emp_grp)A

group by group_id,diff;

# group_id, min(sequence), max(sequence)

'A', '1', '3'

'A', '5', '6'

'A', '8', '9'

'B', '11', '11'

'C', '1', '3'