Find Relevant Dates - SQL Interview Query 7 | SQL Problem Level "MEDIUM"
Вставка
- Опубліковано 1 сер 2024
- 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 UA-cam 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 UA-cam video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/blog/30daysqlquer...
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
Next up, we need #100Days_Of_SQL_Challenge
Vera level🎉 Keep rocking
Top notch explaination.
thanks you for such a great explanation, you are great trainer
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!
Thank you😀.
Till now questions I saw were really good and doing hands on really improving problem solving skills and thanks for creating this challenge.
Amazing :)
Thank you, that was a good problem. i solved it😬
Ms sql server
select *
from day_indicator
where Substring(day_indicator, ((datepart(weekday, dates) +5) %7)+1 , 1)
=1 ;
Fantastic!
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'
Nice. But which one do you think is faster?
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😊
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
Very neat!
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'
Brilliant brother👌
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';
Can you please tell me the website name which you refer in one of the video for SQL practice,
Please reply
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'
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;
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 ;
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;
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 UA-cam channel for learning SQL
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;
;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'
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
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;
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
Can we do -1 instead of ((+5)%7)?
Completed day7 of #30daysSqlChallenge
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
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'
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
Please Upload other videos
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
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 ) ;
Can u give solution in MySQL
Use the postgreSql extract and substring both will work on mysql
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
are you pakistani .
Nope, he's Iranian but lives in India and for employment purpose he shifts recently to Uganda 🇺🇬🇺🇬
@@mrx...223 then i know this is same person ,Then must be terrorist or Jaidhi
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';