DBS Bank SQL Interview Question - Find the missing weeks in a table

SQL questions recently asked in DBS Bank interview. Find the missing weeks in a table
To find out the expected output, I use Recursive CTE.
--Create table syntax
CREATE TABLE sls_tbl (pid INT, sls_dt DATE, sls_amt INT )
-- Insert data into the table
INSERT INTO sls_tbl (pid, sls_dt, sls_amt)
VALUES (201, '2024-07-11', 140), (201, '2024-07-18', 160), (201, '2024-07-25', 150), (201, '2024-08-01', 180), (201, '2024-08-15', 170), (201, '2024-08-29', 130)
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@roasterparinda6307]

Your approach for the solution is great sir !!

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@akhilsingh5251]

amazing Question and amazing explaination

[@prami5724]

Hi

[@CloudChallengers]

@akhilsingh5251, Thanks for your comment

[@ishanshubham8355]

with recursive cte as (
select min(sls_dt) as cnt from sls_tbl
union
select cnt + interval 7 day
from cte
where cnt < (select max(sls_dt) from sls_tbl))
select cnt
from cte as c1 left join sls_tbl as s
on c1.cnt = s.sls_dt
where s.sls_dt is null

[@adityavamsi12]

Great Video ❤

[@CloudChallengers]

Glad you liked it!

[@ChaitanyaKariya-x4q]

without CTE - select
sls_dt - 7 as sls_date
from (
select *,
sls_dt - lag as Misng_wk
from(
select *,
lag(sls_dt,1,sls_dt) over (order by sls_dt)
from sls_tbl) x) y
where Misng_wk > 7

[@nirmalaswain4598]

nice question sir please upload this type of declare type question

[@himanshubasra4451]

Here is my solution in Postgre sql.
select missing_week
from (
select *, sls_dt - prev_date as diff, prev_date + interval '7 days' as missing_week
from (
select *, lag(sls_dt) over(order by sls_dt) as prev_date
from sls_tbl
)
) where diff 7

[@ashwingupta4765]

with r_cte as (
select min(sls_dt) as startdate ,max(sls_dt) as enddate from sls_tbl
union all
select dateadd(week,1, startdate) as weeks , enddate from r_cte
where startdate < enddate)
select r.startdate from r_cte as r
left join sls_tbl as s
on r.startdate = s.sls_dt
where s.sls_dt is null

[@zaravind4293]

with cte as(
select *,
lead(sls_dt) over(order by sls_dt) nxtday,
datediff(day,sls_dt,lead(sls_dt) over(order by sls_dt)) diff from sls_tbl
)
select case when diff>7 then dateadd(day,7,sls_dt) end missing_wk from cte
where diff>7

[@ANUSHRIDAFE]

nice and easy solution, thank you

[@sachinn5503]

WITH CTE AS(
SELECT SLS_DT,adddate(SLS_DT , INTERVAL +7 DAY) week
FROM SLS_TBL), J AS(
SELECT *,
LAG(WEEK,1) OVER(ORDER BY WEEK) N
FROM CTE)
SELECT N FROM J
WHERE SLS_DT!=N;

[@Mr.DhanarajBhandari]

With cte as (
Select Dateadd(week,1,Sls_dt) as sls_dt from sls_tbl
) Select * from cte
Where sls_dt not in (Select sls_dt from sls_tbl)
and sls_dt < (Select Max(sls_dt) from sls_tbl)

[@harshitsalecha221]

WITH cte1 AS (SELECT *,WEEK(sls_dt) as weeks FROM sls_tbl),
cte2 AS (SELECT *, CASE WHEN LEAD(weeks) OVER(ORDER BY weeks) - weeks=1 THEN weeks
WHEN weeks-LAG(weeks) OVER(ORDER BY weeks)=1 THEN weeks
ELSE weeks-1
END new_weeks
FROM cte1)
SELECT DATE_ADD(DATE_ADD('2024-01-01',INTERVAL new_weeks week),INTERVAL 3 day) as missing_week
FROM cte2
WHERE weeksnew_weeks;

[@HARSHRAJ-gp6ve]

with cte as(
select sls_tbl.*,LAG(sls_dt)OVER() as x1 FROM sls_tbl
),cte1 as(
select cte.*,DATEDIFF(sls_dt,x1) as x2 FROM cte
)
select ADDDATE(x1,7) as sls_date FROM cte1 where x2!=(select x2 FROM cte1 GROUP BY x2 ORDER BY COUNT(*) DESC LIMIT 1);

[@user-gq6cg3ls7f]

with cte as(
select *,
datediff(day, sls_dt, LEAD(sls_dt) over (order by sls_dt)) days_diff,
case when datediff(day, sls_dt, LEAD(sls_dt) over (order by sls_dt)) > 7 then concat(year(sls_dt), '-0', month(sls_dt), '-', day(sls_dt)+7) end new_sls_dt
from sls_tbl
)
select new_sls_dt from cte
where new_sls_dt is not null

[@theinsightminer08]

WITH RECURSIVE week_dates AS
(SELECT
MIN(sls_dt) AS dt
FROM sls_tbl
UNION ALL
SELECT
dt + INTERVAL 7 DAY
FROM week_dates
WHERE dt < (SELECT MAX(sls_dt) FROM sls_tbl)
)
SELECT
dt AS sls_dt
FROM week_dates
WHERE dt NOT IN (SELECT sls_dt FROM sls_tbl);

[@landchennai8549]

SELECT * FROM #sls_tbl
; with cte
as
(
select min(sls_dt) as StatrDate, Max(sls_dt) as EndDate from #sls_tbl
)
, cte2
as
(
select * , convert(datetime,StatrDate) + Value as Weekd
from cte
cross apply generate_series(0,datediff(d,StatrDate,EndDate),7)
)
select a.Weekd as Sls_dt
from cte2 a
left join #sls_tbl b on a.Weekd = b.sls_dt
where b.sls_dt is null

[@anirbanbiswas7624]

with cte as(select min(sls_dt) as start_date from sls_tbl
union all
select date_add(sls_dt,interval 1 week) start_date from sls_tbl
where date_add(sls_dt,interval 1 week)

[@VenkateshMarupaka-gn3rp]

WITH CTE AS (SELECT *, LEAD(sls_dt,1,sls_dt) OVER(ORDER BY sls_dt) AS next_week, DATEADD(WEEK, 1, sls_dt) AS next_add__week
FROM sls_tbl)
SELECT next_add__week AS sls_dt
FROM CTE
WHERE next_week next_add__week
AND sls_dt < next_week

[@snehsparsh7954]

-- Approach 1
WITH sls_trx AS (
SELECT *,
LAG(sls_dt) OVER (ORDER BY sls_dt) AS prev_sales_dt,
(sls_dt - 7) AS one_week_ago_dt
FROM practiceDB1.sls_tbl
ORDER BY sls_dt
)
SELECT one_week_ago_dt AS sls_dt
FROM sls_trx
WHERE DATE_DIFF(sls_dt, prev_sales_dt, DAY) 7
;
-- Approach 2 : Using Recursive CTE
WITH RECURSIVE DateSeries AS (
SELECT MIN(sls_dt) AS sls_dt FROM practiceDB1.sls_tbl
UNION ALL
SELECT DATE_ADD(sls_dt, INTERVAL 1 WEEK)
FROM DateSeries
WHERE DATE_ADD(sls_dt, INTERVAL 1 WEEK)

[@harshityadav1545]

WITH DateRanges AS (
SELECT MIN(sls_dt) AS min_dt, MAX(sls_dt) AS max_dt
FROM sls_tbl
)
SELECT s1.sls_dt + INTERVAL '7 days' as sls_dt
FROM sls_tbl s1
LEFT JOIN sls_tbl s2
ON s1.pid = s2.pid
AND s2.sls_dt = s1.sls_dt + INTERVAL '7 days'
WHERE s2.sls_dt IS NULL
AND s1.sls_dt (SELECT min_dt FROM DateRanges)
AND s1.sls_dt (SELECT max_dt FROM DateRanges);