SQL tutorial | How to find n consecutive date records | Sales for at least n consecutive days.
ฝัง
- เผยแพร่เมื่อ 22 ก.ค. 2024
- In this series of SQL Query Interview questions, we discuss the scenario of finding n consecutive date records in a table.
Another approach using Windows Lag function to solve the above scenario is discussed in the below video tutorial. There is additional logic to exclude Weekends.
• SQL Query | How to fin...
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Beginners to Python Programming
skillshare.eqcm.net/GjMakm
Data Science and Business Analytics with Python
skillshare.eqcm.net/JrM1Aq
Get 40% OFF of Skillshare with code FLASH40 - October 2022
skillshare.eqcm.net/c/3299742...
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - learn@knowstar.org
The second approach to solving this scenario using Windows Lag function is discussed in our latest video tutorial here - th-cam.com/video/7YQxowIfAi4/w-d-xo.html
We also take the example of excluding Weekends in this tutorial.
For the 'filter' you could have used Having CNT > =3 after the Group
--Postgresql solution
select count(*), min(created_at), max(created_at) from
(select created_at,
created_at - row_number() over(order by created_at) * interval '1 day' as constant_date
from posts) a
group by created_at
order by 1 desc;
Very importan 'islands and gaps' question, thanks for sharing
This video is life saver. Thank you very much!
Glad it helped!
Thank you so much. I was struggling with this.
Thank you
Ma'am, please create a video for Hierarchical Stage tutorial, both as an Input stage and an Output stage. It would be very helpful. Right now, no video is available for Hierarchical stage on TH-cam. Thank you in advance!
I like your videos and thank you for sharing all. 😊
Can you please create a video on how to get average sales value for Last 10 same day week on date level? For example for order date 27-05-2021 (i.e Thursday), last week is 20-05-2021, last to last week is 13-05-2021 and so on till last 10 weeks.
So there like 27-05-2021 as one of the order date, there will be different other order dates.
I can do this by creating 10 different column in subquery and do an average using those. But is that possible using some other logic or function?
hi can you pls. make a video on making a query, for example: I want to display the total jobtitle_A, jobtitle_B... so on and so forth in the database registered daily by the encoder, displaying value zero for a particular day if there was no entry made.
This was helpful, thank you.
Thank you
Hi Great Video. But how do I change this to show consecutive days per product if I have a listing of various products??
How to get the dataset in the video for practise?
hello mam, your videos are quite wonderful for newbies, i just want to know more in detail about
real-time working scenario. what is the project team size.? how many are they? how to integrate SVN or git into datastage and maintain version.?
how to deploy the code into production environment.? what exactly be the transformations you do in your enhancement or support project?
what could be the exact enhancements to the project.? what are SLA breaches and tickets?
how to install autosys or cron scheduler and integrate with datastage to schedule jobs? Sorry for the list. But, i didnt find
any blogs for these. if you have time, please do a session on these. Thanks a lot.
Thank you. Will plan something soon.
It has a minor issue, for MySQL, it was not working if the records had the same dates. Try the below version
WITH CTE as
(SELECT DATE(created_at) dt, DATE_ADD(DATE(created_at), INTERVAL - ROW_NUMBER() OVER (ORDER BY created_at) DAY) as col
FROM statistics
GROUP BY dt
ORDER BY dt)
SELECT COUNT(*) as CNT
FROM CTE
GROUP BY col
Thanks for the tutorial Maam. Please create a video for complex flat file column
Thank you
Very easy to understand :)
Thank you 🙏
Excellent
Thank you
Hello
DATEADD is not working in my Orcale. please, help me to get same output(no of Consecutive days) without DATEADD function
Thanks
Thank you..
Thank you so much.
Hello, I am getting this error.
Msg 517, Level 16, State 1, Line 46
Adding a value to a 'datetime' column caused an overflow.
Hi, great video, how to exclude weekends while keeping the continuous date?
Thank you
Thank you for mentioning this scenario. This has been answered in our latest tutorial here - th-cam.com/video/7YQxowIfAi4/w-d-xo.html
pls. show how to display last 30 days record of items (including dates with no particular items (just set its value to 0) and also show how many times each item exists each day.
brilliant
Thank you
But suppose we want to list all the consecutive dates in the same column, then in that case how can we do it.
Will this query work if the dates are 1st may, 3rd may. 5th may so on.... It won't.
Given 3 columns: date|customer ID| product usage; find out customers who used the product for 5 consecutive days. Can you please help me with this question?
LEAD/LAG function is the most appropriate for this kind of scenario, instead of using multiple functions like row_number, dateadd
Thank you for sharing your approach.
Can you give an example?? How can we write this with lead lag
Hi maam.. I am fan of your teachings.. You are doing great work.. It's a request maam can you please make video on data analytics project using sql... The way we do in our analytics job.. Please reply maam when can we expect the same.. Thanku
We are planning a Data Analytics boot camp on our TH-cam channel where we will post video tutorials on all Data Analytics topics with Live chat option. It will be posted shortly starting mid May. Stay tuned for more information!
If you get a chance can you please make a video to find next month first Monday
2.find which day the year start
3.write a query to find everymonth 1st dat Jan 1 Feb 1 marc1....dec1
Sure, we will plan soon.
What if there repeated order date values
Then row number will be
1 4-1-21
2 4-1-21
--ORACLE
with cte as(
select start_date, end_date,
start_date - row_number() over (order by start_date) as GRN
from projects)
select min(start_date), max(end_date) from cte group by grn order by grn;
Thank you for sharing 🙏
Why don't use lead()?
can you explain how we can use lead()?
-- Another Appraoch to solve the problem in SQL SERVER
go
with cte as(
SELECT *, day(orderdate) - row_number() over(order by (Select null)) as grp_key
FROM Orders
)
Select min(orderdate) as [start_date], max(orderdate) as [end_date]
from cte
group by grp_key
having count(1) >=3