SQL Interview Questions and answers Part 21 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 21 | Amazon SQL Interview Question And Answer Part-7
This question has been asked in Amazon interview.

Input :- BalanceTbl has two columns namely Balance and Dates.

Problem Statements :- Write SQL to derive Start_Date and End_Date column when there is continuous amount in Balance column as shown below.

Follow me on Social Networking Sites :
*Twitter:
*Instagram :

Scripts :
Create Table BalanceTbl(
Balance int,
Dates Date
)

Insert into BalanceTbl Values(26000,'2020-01-01')
Insert into BalanceTbl Values(26000,'2020-01-02')
Insert into BalanceTbl Values(26000,'2020-01-03')
Insert into BalanceTbl Values(30000,'2020-01-04')
Insert into BalanceTbl Values(30000,'2020-01-05')
Insert into BalanceTbl Values(26000,'2020-01-06')
Insert into BalanceTbl Values(26000,'2020-01-07')
Insert into BalanceTbl Values(32000,'2020-01-08')
Insert into BalanceTbl Values(31000,'2020-01-09')

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

[pankajkharade]

Interesting Example with a nice explanation 👍👍👍👍
In oracle
With DS AS
(
Select BALANCE, DATES, Rownum, Rank()Over(Partition By BALANCE Order BY Dates) Ranking,
Rownum - Rank()Over(Partition By BALANCE Order BY Dates) Diff
From BalanceTbl order by Dates
)
Select BALANCE, Min(DATES), Max(DATES)
From DS
Group By BALANCE, Diff;

[asimmanna]

This a great solution. We can also do using row number. Here is my solution
WITH x AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Balance ORDER BY Dates) AS rnk
FROM balancetbl),
y AS (
SELECT *, DATE_ADD(Dates, INTERVAL -rnk DAY) AS grouped
FROM x)
SELECT MAX(balance) AS balance, MIN(Dates) AS start_date, MAX(dates) AS end_date
FROM y
GROUP BY grouped
ORDER BY start_date;

[Engineerincubicle]

This question is so much interesting !

[arunkumarn]

with cte as
(
select *, (rownum2 - rownum1) as Diff from
(
select *, ROW_NUMBER() over (partition by balance order by dates) as rownum1,
ROW_NUMBER() over (order by dates) as rownum2
from bankbalance

)res
)
select balance, diff, min(dates) as StartDate, max(dates) as EndDate
from cte
group by balance, diff
order by StartDate

[akashgoel]

Similar yet little diff logic, works the same way..

with cte as (
select a.*, (row_number() OVER(order by a.dates) - rank() OVER(partition by balance order by a.dates) ) as rnk
from BalanceTbl a), cte_2 as (
select a.balance, min(a.dates) OVER(partition by a.rnk order by a.dates)as min,
max(a.dates) OVER(partition by a.rnk order by a.dates desc) as max
from cte a
group by a.rnk, a.balance, a.dates)
select distinct balance, min, max from cte_2
order by min

[sanevva]

Amazing question and answer!! Love your videos !!

[susmitapanigrahi]

Awesome question and your solution to this question is super awesome. Keep it up. :)

[paroolsingh]

Very good question and explanation was even better !

[vishalshah]

Simplified logic --

select balance, dates as startdate, lead(dates) over (order by dates) as enddate from
(
select *,
case when lag(Balance) over (order by dates ) = balance then 0 else 1 end Stat
from BalanceTbl
) a where stat = 1

[prajjwaljaiswal]

with cte as (
select
*,
row_number() over(order by dates) - count(*) over(partition by Balance order by dates) as diff
from BalanceTbl )
select balance, min(dates) as start_dates, max(dates) as end_dates
from cte
group by balance, diff
order by start_dates
;

[rajulapatiteja]

use teja
;with cte1 as
(select balance, dates, row_number() over(partition by balance order by balance) as ranking from BalanceTbl),
cte2 as
(select balance, dates, day(dates)-ranking as temp from cte1)
select balance, min(dates) as start_date, max(dates) as end_date from cte2
group by balance, temp

[subhashreekarmakar]

with S as (
select
balance,
dates,
dateadd(day,
ROW_NUMBER() over (partition by balance order by dates desc) ,
dates) as gsplit
from BalanceTbl)

select balance, min(dates), max(dates) from S
group by balance, gsplit

[souviksen]

I was asked this in amazon interview, so you can change the title and put amazon in title :)

[prabhatgupta]

with a as (
select balance, first_value(dates)over(partition by rk), last_value(dates)over(partition by rk )
from (select *, right(cast(dates as date), 1)-dense_rank()over(partition by balance order by dates) as rk from balancetbl order by dates)x order by dates)
select * from a group by 1, 2, 3;

[sekharlakkineni]

select * from BalanceTbl;
with balance_cte as(
select BALANCE, DATES, lag (BALANCE) over (order by DATES)as lag1,
case when lag (BALANCE) over (order by DATES)=BALANCE then 0 else 1 end as tmp_seq
from BalanceTbl),
sequence_cte as(select BALANCE, DATES, sum(tmp_seq) over (order by dates) as seq --running tot
from balance_cte)
select BALANCE, min(DATES)as start_date,
max(DATES) as end_date from sequence_cte
group by BALANCE, seq
order by seq;

[narenkrishh]

ORACLE


select a.balance, min(a.dates) start_date, max(a.dates) end_date from
(select a.balance, sum(rnsum) over (order by dates) rnsum, a.dates from
(select balance, dates, case when balance <> lag(balance, 1, balance) over (order by dates) then 1 else 0 end rnsum
from balancetbl)a)a group by a.balance, a.rnsum
;

[TheEsisia]

WITH CTE
AS
(
select Balance, Dates, DATEADD(d, - RANK() OVER(PARTITION BY Balance ORDER BY Dates), Dates) Island
FROM BalanceTbl
)
select Balance, MIN(Dates) StartDate, MAX(Dates) EndDate
FROM CTE
GROUP BY Balance, Island
ORDER BY StartDate

[srinivasa]

select balance, min(dates) as Start-Date, max(dates) as End-Date
from TBL group by balance

[malliarjun]

Table columns
PID timestamp status
1 10 open
1 11 close
1 12 success
1 13 fail
2 10 null
2 11 close
2 12 fail
2 13 null


Output expected :-
1, open, failed
2, close, fail

[soumeshkayast]

SELECT
BALANCE,
START_DATE,
END_DATE
FROM
(
SELECT
BALANCE,
MIN(DATES) AS START_DATE,
MAX(DATES)AS END_DATE,
[LEAD]
FROM
(SELECT
BALANCE,
DATES,
DATEADD(DAY, -1*(DENSE_RANK()OVER(PARTITION BY BALANCE ORDER BY DATES)), DATES) AS [LEAD]
FROM BalanceTbl)A GROUP BY BALANCE, [LEAD])A