report contiguous dates | google sql interview questions and answers | #interview #sql #google

report contiguous dates
google sql interview questions and answers
coding ninjas

Create Table Statement :
====================

Create table If Not Exists Failed
(
fail_date date
);

Create table If Not Exists Succeeded
(
success_date date
);

insert into Failed (fail_date)
values ('2018-12-28')
,('2018-12-29')
,('2019-01-04')
,('2019-01-05');

insert into Succeeded (success_date) values ('2018-12-30'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03'),
('2019-01-06');

select *
from failed ;

select *
from succeeded ;

top interview question and answer in pyspark :

#google #codingninjas #sql #dataengineers #pivot #zs #fang #pyspark #sql #interview #dataengineers #dataanalytics #datascience #StrataScratch #Facebook #data #dataengineeringinterview #codechallenge #datascientist #pyspark #CodingInterview
#dsafordataguy #dewithdhairy #DEwithDhairy #dhiarjgupta #leetcode #topinterviewquestion

[saktibiswal]

Thanks. I will try to get this done in MS SQL Server.

[kushmanthreddy]

with cte as(
select *, day(success_date) as day, row_number()over() as r, day(success_date)-row_number()over() as diff
from succeeded where success_date>='2019-01-01')
select distinct 'succeded' as period_state, min(success_date) over(partition by diff) as start_date,
max(success_date) over(partition by diff) as end_date from cte
union
(with cte as(
select *, day(fail_date) as day, row_number()over() as r, day(fail_date)-row_number()over() as diff
from failed where fail_date>='2019-01-01')
select distinct 'fail' as period_state, min(fail_date) over(partition by diff) as start_date,
max(fail_date) over(partition by diff) as end_date from cte )
order by start_date

[kalaivanik]

with report as(
select success_date as date, 'succeeded' as status from succeeded
union
select fail_date as date, 'fail' as status from failed)
, cte2 as( select *, row_number() over(partition by status order by date)rn, (day(date) - row_number() over(partition by status order by date))diff
from report where date >= '2019-01-01')
select
case when status = 'succeeded' then 'succeeded' else 'failed' end as period_state,
min(date)as start_date, max(date)as end_date
from cte2
group by diff, status