Amazon Interview | SQL Interview Problem asked during Amazon Interview

In this video, let us solve an SQL Problem asked during the Amazon Interview.

Download the scripts used in the video:

Thanks for watching!

[Lekhatopil]

My solution in PostgreSQL:
WITH CTE AS
(SELECT *
, dates - (ROW_NUMBER() OVER(PARTITION BY employee, status ORDER BY dates)::INT) AS grp
FROM emp_attendance)
SELECT employee, MIN(dates) AS from_date
, MAX(dates) AS end_date, status
FROM CTE
GROUP BY employee, grp, status
ORDER BY employee, from_date

In my ROW_NUMBER function, I have partitioned by employee and status, and ordered by dates. For each employee, the data is grouped by status and ordered by dates. The row number resets to 1 whenever the status changes (from present to absent or vice-versa) within each employee's partition.
I then subtracted the row number from the date to create a group identifier (grp) to identify consecutive dates within the same status for each employee.

[saralavasudevan]

Thanks for the problem and explaination!. This was my solve:

with mycte as
(
SELECT *,
rank() over(partition by employee, status order by dates) as rn,
datepart(day, dates) as theday,
(datepart(day, dates) -rank() over(partition by employee, status order by dates)) as diff
from emp_attendance
)

select employee, from_date, to_date, status
from
(
select employee, diff, status, min(dates) as from_date, max(dates) as to_date
from mycte
group by employee, diff, status
) as x
order by 1, 2, 3

[manojdevareddy]

CTEs and window functions are new to me in learning stage, but I got this very clearly thanks for the in detail explanation

[MahidharYouTube]

Your videos helped me a lot in cracking my data analyst interview brother, thank you so much

[satishkumar-rpzb]

solving challenging queries from top mnc with nice explanation, great thoufiq keep it up.

[sathyamoorthy]

with first as (
select *, lag(status, 1) over(partition by employee order by dates) as prev_status
from emp_attendance
),
second as (
select b.* from (
select *, case when status = prev_status then 'SAME' else 'CHANGE' end as status_check from first
) b
where b.status_check='CHANGE'
),
final as (
select employee, dates as from_date, lead(dates,1) over(partition by employee order by dates)-1 as to_date, status
from second )
select employee, from_date, coalesce(to_date, from_date), status from final
order by employee, from_date;

[adityatomar]

Man you are legend....great explanation 😮

[Damon-]

My solution:
WITH cte AS (
SELECT *,
CASE WHEN status = LAG(status, 1, status) OVER (PARTITION BY employee ORDER BY dates) THEN 0 ELSE 1 END AS flag
FROM emp_attendance
),
cte2 AS (
SELECT employee, dates, status, SUM(flag) OVER (PARTITION BY employee ORDER BY dates) AS flag_sum
FROM cte
)
SELECT employee, MIN(dates) AS from_date, MAX(dates) AS to_date, MAX(status) AS status
FROM cte2
GROUP BY employee, flag_sum
ORDER BY employee, from_date;
Sir, Is there will be any difference i use iif inplace of case Statment???

[Tech_with_Srini]

Bro Odin school is not a good option, i wasted my time and money, They wont provide you placements , I joined in 2022, still i am not get a job through it, pls dont waste ur time and money

[KoushikT]

with A as (select
*,
row_number() over (partition by employee, status order by dates) as rnk
from emp_attendance
),

B as (
select
*,
dates - CONCAT(rnk::text, ' day')::interval as diff
from A
)

select
employee,
min(dates) as start_date,
max(dates) as end_date,
max(status)
from
B
group by employee, diff
order by 1, 2

[sirajuddinmohamedsaleem]

@TFQ can we use min and max instead of first_value and last_value in the window function?

[andriimoskovskykh]

You can achieve the same differentiation between employees based on status by simply using rank() and partitioning it by employee, status (as in the first cte).

WITH rank_cte AS (
SELECT
*,
rank() OVER(partition by employee, status order by dates) as r
FROM emp_attendance
ORDER BY employee, dates
),
consec_cte AS (
SELECT
*,
r - row_number() OVER() AS consec
FROM rank_cte
)
SELECT
employee,
MIN(dates) AS start_date,
MAX(dates) AS end_date,
status
FROM consec_cte
GROUP BY employee, status, consec
ORDER BY employee, start_date;

[sunnygoud]

Hi comments box here is my solution:
with cte as(
SELECT *, dense_rank()over( partition by employee order by employee, dates) as rn,
dense_rank() over(partition by employee, status order by employee, dates ) as rn2 from emp_attendance),
cte1 as(
select employee, dates, status, rn-rn2 as fn from cte
order by dates)
select distinct employee, first_value(dates) over(partition by employee, fn order by dates )as from_date, last_value(dates) over(partition by employee, fn) as to_date, status from cte1
order by employee, from_da

[andynelson]

I struggled with this. The rn - rn where status = X is a cool pattern.

[AmanRaj-pw]

MySql solution: with cte as (
select *, row_number() over (partition by employee, status order by dates ) as rw,
dates - row_number() over (partition by employee order by employee) as diff from emp_attendance
order by employee, dates
)

select employee, min(dates) as from_date, max(dates) as to_date, status from cte
group by employee, status, diff

[mihirit]

this one is a very tough question, for what level role was this question asked 😰

[florincopaci]

Hello my solution in Sql Server:

WITH FLO AS (
SELECT *, CASE WHEN STATUS <> LAG(STATUS, 1, 'OPOUI')OVER(PARTITION BY EMPLOYEE ORDER BY DATES)THEN 1 ELSE 0 END
AS FLAG
FROM EMP_ATTENDANCE
), FLO1 AS (
SELECT *, SUM(FLAG)OVER(PARTITION BY EMPLOYEE ORDER BY DATES)AS GRP
FROM FLO
)
SELECT EMPLOYEE, MIN(DATES)AS FROM_DATE, MAX(DATES)AS TO_DATE, STATUS
FROM FLO1
GROUP BY EMPLOYEE, STATUS, GRP
ORDER BY EMPLOYEE, FROM_DATE

Hope it helps.

[manianbarasu]

Sir, Your videos are really awesome. can you make videos for python programming

[amanbhattarai]

How difficult sql queries are to write on real job senario? Intermediate or hard ?

[sreerag__]

create table emp_attendance(employee varchar(200), Dates date, status varchar(200));
Insert into emp_attendance values
('A1', '2024-01-01', 'PRESENT'),
('A1', '2024-01-02', 'PRESENT'),
('A1', '2024-01-03', 'PRESENT'),
('A1', '2024-01-04', 'ABSENT'),
('A1', '2024-01-05', 'PRESENT'),
('A1', '2024-01-06', 'PRESENT'),
('A1', '2024-01-07', 'ABSENT'),
('A1', '2024-01-08', 'ABSENT'),
('A1', '2024-01-09', 'ABSENT'),
('A1', '2024-01-10', 'PRESENT'),
('A2', '2024-01-06', 'PRESENT'),
('A2', '2024-01-07', 'PRESENT'),
('A2', '2024-01-08', 'ABSENT'),
('A2', '2024-01-09', 'PRESENT'),
('A2', '2024-01-10', 'ABSENT');

select * from emp_attendance;