Find Relevant Dates - SQL Interview Query 7 | SQL Problem Level 'MEDIUM'

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the SEVENTH video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.

Let's follow the below routine to make the best use of it:
1. Watch the YouTube video (first half) to understand the problem statement.

2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.

3. Share your solution on Discord and discuss different solutions and issues on my Discord server.

4. Watch the second half of my YouTube video to find my solution to the problem.

5. Share it with your contacts and spread the knowledge.

DOWNLOAD the Dataset from below:

Timeline:
00:00 Intro
00:10 Understanding Problem Statement
02:53 Solution in PostgreSQL
12:39 Solution in Microsoft SQL Server

Thanks for participating in this challenge!

Good luck and Happy Learning!

[subhajitdey]

this 30day sql challenge questions are top notch, need more tricky and complex sql question

[oyaiskhan]

Next up, we need #100Days_Of_SQL_Challenge

[DEEPAK-jxsi]

My solution
select * from (
select *,
case
when substring(day_indicator, coalesce((nullif(datepart(dw, Dates) - 1, 0)), 7), 1) = '1' Then 'include' else 'exclude'
end as day_required
from Day_Indicator) s
where s.day_required = 'include'

[iamkiri_]

My solution
with cte as (
select *,
row_number() over (partition by Product_ID order by Dates ) as rn
from Day_Indicator
order by Product_ID, Dates )
select Product_ID, Day_Indicator, Dates from cte
where substring(Day_Indicator, rn, 1) = 1 ;

[Damon-]

Ms sql server
select *
from day_indicator
where Substring(day_indicator, ((datepart(weekday, dates) +5) %7)+1, 1)
=1 ;

[amzahaan]

I've used row_number instead of modulas, please check and if the approach is correct.


select * from
(select *,
row_number() over (partition by product_id order by dates) rn,
case when substring(day_indicator, row_number() over (partition by product_id order by dates), 1)=1 then 'include'
else 'exclude' end as flag
from Day_Indicator )x
where flag='include'

[mohdtoufique]

Hi Thanks for the content...My approach
WITH updated_day_week AS(
select *,
CASE WHEN (DATEPART(WEEKDAY, Dates)-1)=0 THEN 7 ELSE (DATEPART(WEEKDAY, Dates)-1)
END AS Updated_Day_of_week
from Day_Indicator)
SELECT *, SUBSTRING(Day_Indicator, Updated_Day_of_week, 1) AS Updated_Indicator
FROM updated_day_week
WHERE SUBSTRING(Day_Indicator, Updated_Day_of_week, 1)=1

[NabeelKhan-umzk]

Thanks to your teachings, I've mastered the skills, and now I can effortlessly tackle all your #30DaySQLQueryChallenge questions in just 5 minutes! Your tutorials have been a game-changer for me!

[akshaysalve]

With cte as (
Select *,
case when dayofweek(dates) = 1 Then 7 Else dayofweek(dates) - 1 End as Day_num
from input_table),
cte2 as
(Select product_id, day_indicator, dates,
case when substring(day_indicator, Day_num, 1) = 1 Then 'Yes' Else 'No' end as flag from cte)
Select product_id, day_indicator, dates, flag from cte2
where flag = 'Yes';

[subramanianks]

Hi My Solutions in Oracle:

SOL 1:

select product_id, day_indicator, dates from(
select product_id, day_indicator, dates, row_number()
over(partition by product_id order by dates) as rn
from Day_Indicator) where substr(day_indicator, rn, 1)=1;


SOL 2:

select product_id, day_indicator, dates from(
select product_id, day_indicator, dates, case when to_char(dates, 'D')-1=0 then
7 else to_char(dates, 'D')-1 end as dofw
from Day_Indicator) where substr(day_indicator, dofw, 1)=1;

Please correct me if I am wrong😊

[LuisHinojosaFalcon]

3 lines solution:
SELECT product_id, day_indicator, dates
FROM Day_Indicator
WHERE SUBSTRING(day_indicator, EXTRACT(ISODOW FROM dates)::int, 1) = '1';

[msk-plhw]

Till now questions I saw were really good and doing hands on really improving problem solving skills and thanks for creating this challenge.

[Nagnath_Mitkal]

thanks you for such a great explanation, you are great trainer

[niharvaishnav]

my soln
select product_id, Day_Indicator, dates from(
select *, SUBSTRING(Day_Indicator, dayofweek, 1) as stringchar from
(
SELECT *,
CASE
WHEN DATEPART(dw, DATES) = 1 THEN 7 -- Sunday
ELSE DATEPART(dw, DATES) - 1
END AS DayOfWeek
from Day_Indicator
)t)s
where s.stringchar=1

[mayankmehta]

Nice Question to practice.

I tried with bit different approach

with cte as(
select
Product_id,
Day_Indicator,
Dates,
datename(WEEKDAY, dates) as weekdays,
Case when Day_Indicator = '1010101' then concat('Monday', ', ', 'Wednesday', ', ', 'Friday', ', ', 'Sunday')
when Day_Indicator = '1000110' then concat('Monday', ', ', 'Friday', ', ', 'Saturday') end as week_operating_details
from Day_Indicator
),

cte_2 as (
select
Product_id,
Day_Indicator,
Dates,
case when weekdays = value then 1 else 0 end as bool
from cte
cross apply string_split(week_operating_details, ', ')
)

select
Product_id,
Day_Indicator,
Dates
from cte_2
where bool = 1

[tapanyawalkar]

with day_cte as (select *, EXTRACT(isodow from dates) as day from Day_Indicator),
selected_day as (
select *, substring(day_indicator, cast(day AS INTEGER), 1) as selected_day
from day_cte
)

select product_id, day_indicator, dates from selected_day where selected_day = '1'

[poojagonla]

with q1 as (
select product_id, day_indicator,
to_char(dates, 'DAY') dow, dates from Day_Indicator),
q2 as
(Select case when (substr(day_indicator, length(dow), 1)=1)
then
1
else
0
end as result,
product_id, day_indicator, dates from q1
)
Select product_id, day_indicator, dates from q2 where result=1;

[narendrashekhavat]

Select Product_id, Day_indicator, Dates from (
Select a.*, Row_number() over (partition by Product_id order by product_id) as flag from Day_7 a) subquery
where substr(Day_indicator, flag, 1)=1;

[sapnasaini]

MySQL solution -

with cte as (
select *, DAYOFWEEK(dates) as day, (DAYOFWEEK(dates)+5)%7 + 1 as days,
case
when substring(day_indicator, (DAYOFWEEK(dates)+5)%7 + 1, 1)='1' then 1 else 0 end as flag
from Day_Indicator
)
select product_id, day_indicator, dates
from cte
where flag = 1;

[nambidasan]

;with cte as
(
select *, row_number() over (partition by Product_Id order by Dates) rn from Day_Indicator
)
Select Product_Id, Day_Indicator, Dates from cte
where SUBSTRING(Day_Indicator, rn, 1) = '1'