SQL tutorial | How to find n consecutive date records | Sales for at least n consecutive days.

No video

SQL tutorial | How to find n consecutive date records | Sales for at least n consecutive days.

Рет қаралды 37,603

Күн бұрын

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.eqc...
Data Science and Business Analytics with Python
skillshare.eqc...
Get 40% OFF of Skillshare with code FLASH40 - October 2022
skillshare.eqc...
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.ne...
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.c...
Email - learn@knowstar.org

Пікірлер: 50

@LearnatKnowstar Жыл бұрын

The second approach to solving this scenario using Windows Lag function is discussed in our latest video tutorial here - kzfaq.info/get/bejne/bb-Bq9KorsylmWg.html We also take the example of excluding Weekends in this tutorial.

@iamyuvi009 2 жыл бұрын

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

@pabeader1941 2 жыл бұрын

For the 'filter' you could have used Having CNT > =3 after the Group

@lingxu9697 2 жыл бұрын

Very importan 'islands and gaps' question, thanks for sharing

@KritarthMalviya 3 жыл бұрын

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 KZfaq. Thank you in advance!

@sksaratchandra8266 Жыл бұрын

This video is life saver. Thank you very much!

@LearnatKnowstar Жыл бұрын

Glad it helped!

@amitsah2839 3 жыл бұрын

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?

@ishanshah7460 2 жыл бұрын

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

@rpatel4698 Жыл бұрын

Hello DATEADD is not working in my Orcale. please, help me to get same output(no of Consecutive days) without DATEADD function Thanks

@theaugustleo 2 жыл бұрын

Thank you so much. I was struggling with this.

@LearnatKnowstar 2 жыл бұрын

Thank you

@baisakhibhattacharya7067 11 ай бұрын

Hello, I am getting this error. Msg 517, Level 16, State 1, Line 46 Adding a value to a 'datetime' column caused an overflow.

@mnunez6153 2 жыл бұрын

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.

@millerdane 2 жыл бұрын

Hi Great Video. But how do I change this to show consecutive days per product if I have a listing of various products??

@theraizadatalks14 6 ай бұрын

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

@animesh7296 4 ай бұрын

How to get the dataset in the video for practise?

@purushottamdwa6943 3 жыл бұрын

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.

@LearnatKnowstar 3 жыл бұрын

Thank you. Will plan something soon.

@Balajionceagain 3 жыл бұрын

Thanks for the tutorial Maam. Please create a video for complex flat file column

@LearnatKnowstar 3 жыл бұрын

Thank you

@mnunez6153 2 жыл бұрын

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.

@purbashachowdhury2985 Жыл бұрын

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?

@atom8926 Жыл бұрын

But suppose we want to list all the consecutive dates in the same column, then in that case how can we do it.

@aswinkumar9751 3 жыл бұрын

Excellent

@LearnatKnowstar 3 жыл бұрын

Thank you

@ritudahiya6223 Жыл бұрын

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

@LearnatKnowstar Жыл бұрын

We are planning a Data Analytics boot camp on our KZfaq 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!

@AnalogDave 2 жыл бұрын

This was helpful, thank you.

@LearnatKnowstar 2 жыл бұрын

Thank you

@travelnexpedition 2 жыл бұрын

Will this query work if the dates are 1st may, 3rd may. 5th may so on.... It won't.

@battulasuresh9306 2 жыл бұрын

What if there repeated order date values Then row number will be 1 4-1-21 2 4-1-21

@sramkumar2005 2 жыл бұрын

LEAD/LAG function is the most appropriate for this kind of scenario, instead of using multiple functions like row_number, dateadd

@LearnatKnowstar 2 жыл бұрын

Thank you for sharing your approach.

@shravyakulal5756 2 жыл бұрын

Can you give an example?? How can we write this with lead lag

@aswinkumar9751 3 жыл бұрын

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

@LearnatKnowstar 3 жыл бұрын

Sure, we will plan soon.

@chrajeshdagur 2 жыл бұрын

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

@LearnatKnowstar 2 жыл бұрын

Thank you for sharing 🙏