SQL Interview Questions and answers Part 2 | Amazon SQL Interview Question And Answer Part-1

SQL Interview Questions and answers Part 2 | Amazon SQL Interview Question And Answer Part-1
This question has been asked in Amazon interview.
Problem Statement : Write a SQL query to find the maximum and minimum values of continuous ‘Sequence’ in each ‘Group’

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
🔴 Instagram :

🔴 Twitter:
-------------------------------------------------------------------------
🔴 Table and Insert SQL Script :
-------------------------------------------------------------------------
CREATE TABLE Emp(
[Group] varchar(20),
[Sequence] int )

INSERT INTO Emp VALUES('A',1)
INSERT INTO Emp VALUES('A',2)
INSERT INTO Emp VALUES('A',3)
INSERT INTO Emp VALUES('A',5)
INSERT INTO Emp VALUES('A',6)
INSERT INTO Emp VALUES('A',8)
INSERT INTO Emp VALUES('A',9)
INSERT INTO Emp VALUES('B',11)
INSERT INTO Emp VALUES('C',1)
INSERT INTO Emp VALUES('C',2)
INSERT INTO Emp VALUES('C',3)

#AmazonDataEngineer #AmazonInterview #AmazonSQLInterviw #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #ITJunction4All

[ankitkraken]

Most of the other interview questions on Youtube are like 'What is full form of SQL?'.
Finally a challenging question where I could learn something.
Thanks. Please make more.

[cleversachin]

A person knows everything if he can explain something to kids .. and here you goes... Your presentations are best.

[DK-dunq]

I thot I was good in sql..but can't figure out the logic on my own. Good learning. Interesting logic as solution

[mohammadsajid]

Thanks for your Clear Explanation. Keep Posting the Videos, please

[NarendraBellamkonda]

For people who want to practice on Oracle
CREATE TABLE empseq
( groupf VARCHAR2(2),
sequenc VARCHAR2(2)

);

insert into empseq (groupf, sequenc) values ('A', '1');
insert into empseq (groupf, sequenc) values ('A', '2');
insert into empseq (groupf, sequenc) values ('A', '3');
insert into empseq (groupf, sequenc) values ('A', '5');
insert into empseq (groupf, sequenc) values ('A', '6');
insert into empseq (groupf, sequenc) values ('A', '8');
insert into empseq (groupf, sequenc) values ('A', '9');
insert into empseq (groupf, sequenc) values ('B', '10');
insert into empseq (groupf, sequenc) values ('C', '1');
insert into empseq (groupf, sequenc) values ('C', '2');
insert into empseq (groupf, sequenc) values ('C', '3');


WITH CTE AS
(SELECT GROUPF, SEQUENC,
RANK() OVER ( PARTITION BY GROUPF ORDER BY SEQUENC) AS RNK,
(SEQUENC-RANK() OVER ( PARTITION BY GROUPF ORDER BY SEQUENC)) AS GRP
FROM empseq)
SELECT DISTINCT GROUPF,
MIN(SEQUENC) OVER(PARTITION BY GROUPF||GRP ) AS MINSEQ,
MAX(SEQUENC) OVER(PARTITION BY GROUPF||GRP ) AS MAXSEQ
FROM CTE order by 1;

[nihalbiswas]

I think first order the table based in both group and sequence the use ntile windows function to create 5 groups and then use first_value and last_value windows function it will be much easier I guess.. and ntile will by default group the first 3 rows as it give more priority to the first group

[trinadhgonthureddy]

You are really great. These videos helps lot❤

[NomanKhan]

Thanks for your very use full videos and please keep posting the videos like this...

[zlla]

Damn. Sql is really easy and learning it can land you a job at Amazon? Wow time to spend more time studying sql

[Ilovefriendswebseries]

with cte as (
select *,
row_number() over(partition by grp order by sequence) as rnk,
sequence-row_number() over(partition by grp order by sequence) diff
from emp)
select
distinct(grp),
min(sequence) over(partition by diff ) as minseq,
max(sequence) over(partition by diff) as maxseq
from cte order by grp;

[Savenature]

Here is the my approach in mysql :

with cte as (select *, lag(sequence+1, 1, sequence) over(partition by grp ) as prev_sequence,
sequence-lag(sequence+1, 1, sequence) over(partition by grp) as grp_flag
from emp_sq), cte_2 as
(select *, sum(grp_flag) over(partition by grp rows between unbounded preceding and current row) as groupp
from cte)
select Grp, min(sequence) as min_seq, max(sequence) as max_seq
from cte_2
group by Grp, groupp;

[subrahmanyamvemuri]

Great Explanation. Thanks for the solution

[adityanjsg]

Instead of row number I used, Lag function, it worked

[mayankbhardwaj]

with one as (select *,
row_number() over(partition by gp order by sequence) as rnk,
sequence-row_number() over(partition by gp order by sequence) as diff
from em)

Select gp, min(sequence) as min_seq, max(sequence) as max_seq from one
group by diff, gp

[ashwinmani]

Please make more real interview vidoes in context with ds/ba/da roles like this, Good content Thumbs up.

[joanna]

Thanks for the video! Would this apply for the Business analyst role? Do you have any other problem sets for this specific role?

[mohammadshahbaz]

My sol -
with base as(
select *
, lag(Sequence) over(partition by Group1 order by Sequence) as lag_Sequence
, case when Sequence - lag_Sequence=1 then 0 else 1 end as flag
from Emp
), base2 as(
select *
, sum(flag) over(partition by Group1 order by Sequence) as seq
from
base)
select Group1, seq, min(Sequence), max(Sequence)
from
base2
group by Group1, seq;

[notavi]

with cte as (
select *, sequence - row_number() over(partition by grp) x from emp
)
select grp, min(sequence) min_seq, max(sequence) maxsequence
from cte
group by grp, x;

[vijay.s-llyq]

with cte as
(select *, row_number() over (partition by [group] order by sequence)dsf from Emp),
cte2 as
(select *, (sequence-dsf) pairs from cte ),
cte3 as
(select min(sequence) as min_seq, max(sequence) max_seq, pairs, [group] from cte2
group by pairs, [group])

select [group], min_seq, max_seq from cte3

[SUPRITHAKB]

Why are we using [ ] brackets in the query? what is it's purpose?