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

[@subhajitdey6806]

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

[@oyaiskhan11]

Next up, we need #100Days_Of_SQL_Challenge

[@ganeshv791]

Vera level🎉 Keep rocking

[@growtogether1341]

with cte as (
select *, SUBSTRING(day_indicator, 1, 1) AS Mon,
SUBSTRING(day_indicator, 2, 1) AS Tue,
SUBSTRING(day_indicator, 3, 1) AS Wed,
SUBSTRING(day_indicator, 4, 1) AS Thu,
SUBSTRING(day_indicator, 5, 1) AS Fri,
SUBSTRING(day_indicator, 6, 1) AS Sat,
SUBSTRING(day_indicator, 7, 1) AS Sun,
TO_CHAR(dates, 'Day') AS weekday_name, extract(dow from dates) as dow from Day_Indicator
)
select product_id, day_indicator, dates from
( select product_id, day_indicator, dates,
case when dow = 1 then Mon
when dow = 2 then Tue
when dow = 3 then Wed
when dow = 4 then Thu
when dow = 5 then Fri
when dow = 6 then Sat
when dow = 0 then Sun end as dayflag from cte
) a
where a.dayflag='1'

[@DEEPAK-jx5si]

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'

[@TheRaju991]

Nice. But which one do you think is faster?

[@likinponnanna8990]

Top notch explaination.

[@msk-pl3hw]

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

[@Damon-007]

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

[@gphanisrinivasful]

Fantastic!

[@NabeelKhan-um1zk]

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!

[@mohdtoufique3786]

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

[@gphanisrinivasful]

Very neat!

[@amzahaan7232]

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'

[@UnrealAdi]

Brilliant brother👌

[@bassambenidir1729]

I think you need to cast the row number in your substring to an integer else substring won't work with an bigint argument, and you forgot to put '1' in your comparaison
here's the query, and your solution worked so well, brilliant:
select
product_id,
day_indicator,
dates
from
(
select
*,
row_number() over (
partition by product_id
order by
dates
) rn,
case when substring(
day_indicator,
cast(
row_number() over (
partition by product_id
order by
dates
) as int
),
1
) = '1' then 'include' else 'exclude' end as flag
from
Day_Indicator
) x
where
flag = 'include';

[@Nagnath_Mitkal]

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

[@ehsanshakeri620]

Thank you, that was a good problem. i solved it😬

[@sivakrishnasriram4782]

Thank you😀.

[@akshaysalve195]

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';

[@dailymoto3347]

Can you please tell me the website name which you refer in one of the video for SQL practice,
Please reply

[@tapanyawalkar8224]

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'

[@subramanianks9030]

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😊

[@sapnasaini851]

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;

[@hopess-nm4ij]

Can this question asked for a fresher or this is only for experience people.

[@LuisHinojosaFalcon]

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

[@niharvaishnav7628]

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

[@HeyyRomii]

Amazing :)

[@bhaskar4910]

Hello bro to solve these questions what are the concepts should i learn from beginning to problem solving range
Is there any course available in your TH-cam channel for learning SQL

[@VaibhavGupta-j4i]

MySQL Workbench Solution:
select product_id, day_indicator, dates
from
( select *, replace(dayofweek(dates)-1,0,7) as DOW,
case when substr(day_indicator,replace(dayofweek(dates)-1,0,7),1) = 1 then 1 else 0 end as flag
from day_indicator
) x
where x.flag = 1;

[@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 ;

[@niharvaishnav7628]

will this soln works if there are more dates in one product_id?

[@DodlaPankaj]

MYSQL
with a as (select *,weekday(dates)+1
as d2 from day_indicator)
select product_id,day_indicator,dates from a where substr(day_indicator,d2,1) = 1

[@rashmikrishnamohan1736]

Can we do -1 instead of ((+5)%7)?

[@Shubham-g199]

for MYSql:
select
product_id ,
Day_Indicator,
Dates
from(
select *,
weekday(Dates)+1 as week_no,
dayname(Dates) as day_name,
case when substring(day_indicator,weekday(Dates)+1,1)='1' then 1 else 0 end as flag
from Day_Indicator) x
where flag =1

[@poojagonla4340]

with q1 as (
select product_id,day_indicator,
to_char(dates,'DAY') ,nvl(LPAD('1',(to_number(to_char(dates,'D'))-1),0),'0000001') 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;

[@aamirsohail2576]

Ms sql server
SET DATEFIRST 1;
with cte as(
select *,DATEPART(WEEKDAY, Dates) as dow,
case when substring(Day_indicator,DATEPART(WEEKDAY, Dates),1) = '1' then 1
else 0 end as flag from Day_Indicator)
select Product_id,Day_indicator,Dates from cte where flag = 1;

[@nambidasan6314]

;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'

[@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;

[@DodlaPankaj]

MYSQL ---------------------------------------------------
with a as (select *,weekday(dates)+1
as d2 from day_indicator)
select product_id,day_indicator,dates from a where substr(day_indicator,d2,1) = 1

[@sathvikdakappagari9122]

Please check my soln
select d.product_id, d.day_indicator, d.dates
from (select *, row_number() over(partition by product_id) as rnk,
substring(day_indicator from row_number() over(partition by product_id):: int for 1 ) as flag
from day_indicator) d
where d.flag = '1'

[@nidazafarkhan8348]

Can u give solution in MySQL

[@SaurabhSingh-kz7mk]

Use the postgreSql extract and substring both will work on mysql

[@splendidabhi]

Completed day7 of #30daysSqlChallenge

[@georgekakweza3767]

Using MySql Workbench:
WITH t1 AS
(
SELECT *,
CASE WHEN DAYOFWEEK(Dates) = 1 THEN 7
ELSE DAYOFWEEK(Dates) - 1 END AS Day_Num
FROM day_indicator
),
t2 AS
(
SELECT *,
SUBSTR(Day_Indicator,Day_Num,1) AS 'Flag' FROM t1
)
SELECT Product_ID, Day_Indicator, Dates FROM t2 WHERE Flag = 1;

[@hammadyasir2174]

Please Upload other videos

[@vijay.s-ll1yq]

SELECT * FROM
(SELECT *,DENSE_RANK () OVER (PARTITION BY DAY_INDICATOR ORDER BY DATES) AS DAY,
CASE WHEN SUBSTRING(DAY_INDICATOR,DENSE_RANK () OVER (PARTITION BY DAY_INDICATOR ORDER BY DATES),1) =1
THEN 'INCLUDE' ELSE 'EXCLUDE' END AS FLAG
FROM PRODUCT) TEST
WHERE FLAG 'EXCLUDE'
ORDER BY 1 ASC

[@mayankmehta8480]

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

[@blse2000]

SQL SERVER SOLUTION:
WITH CTE1 AS
(
select *,ROW_NUMBER() OVER(PARTITION BY PRODUCT_ID,DAY_INDICATOR ORDER BY DATES) AS RN from Day_Indicator
),
CTE2 AS
(
SELECT *,SUBSTRING(DAY_INDICATOR,RN,1) AS DI FROM CTE1
)
SELECT CTE2.Product_ID,CTE2.Day_Indicator,CTE2.Dates FROM CTE2 WHERE DI=1

[@rohitsethi5696]

are you pakistani .

[@mrx...223]

Nope, he's Iranian but lives in India and for employment purpose he shifts recently to Uganda 🇺🇬🇺🇬

[@rohitsethi5696]

@@mrx...223 then i know this is same person ,Then must be terrorist or Jaidhi

[@nadimatlanagesh1951]

My solution in snowflake :
SELECT product_id ,day_indicator,dates from (
SELECT *,CASE WHEN dayname(dates) IN ('Mon','Wed','Fri','Sun') THEN 1 ELSE 0 END WEEK_FLAG
FROM day_indicator WHERE product_id ='AP755' AND WEEK_FLAG=1
union
SELECT *,CASE WHEN dayname(dates) IN ('Mon','Fri','Sat') THEN 1 ELSE 0 END WEEK_FLAG
FROM day_indicator WHERE product_id ='XQ802' AND WEEK_FLAG=1 ) ;

[@mrbartuss1]

WITH dow_cte AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY dates) AS day_of_week
FROM
day_indicator
)
SELECT
product_id,
day_indicator,
dates
FROM
dow_cte
WHERE
SUBSTRING(day_indicator,day_of_week::INT,1) = '1';