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

preview_player
Показать описание
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!
Рекомендации по теме
Комментарии
Автор

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

subhajitdey
Автор

Next up, we need #100Days_Of_SQL_Challenge

oyaiskhan
Автор

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'

DEEPAK-jxsi
Автор

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 ;

iamkiri_
Автор

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

Damon-
Автор

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'

amzahaan
Автор

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

mohdtoufique
Автор

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!

NabeelKhan-umzk
Автор

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

akshaysalve
Автор

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😊

subramanianks
Автор

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

LuisHinojosaFalcon
Автор

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

msk-plhw
Автор

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

Nagnath_Mitkal
Автор

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

niharvaishnav
Автор

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

mayankmehta
Автор

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'

tapanyawalkar
Автор

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;

poojagonla
Автор

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;

narendrashekhavat
Автор

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;

sapnasaini
Автор

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

nambidasan
visit shbcf.ru