The second approach to solving this scenario using Windows Lag function is discussed in our latest video tutorial here - ruclips.net/video/7YQxowIfAi4/видео.html We also take the example of excluding Weekends in this tutorial.
--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;
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 RUclips. 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?
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.
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.
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 RUclips 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!
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?
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
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
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.
--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;
If 2 or more order dates are repeated, as they're bound to in a real world, this logic will fail because the row number function will assign different incremental values to each of them. Therefore, when you do the subtraction with the dateadd function, the resulting dates will not be the same even though they're supposed to
-- 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
The second approach to solving this scenario using Windows Lag function is discussed in our latest video tutorial here - ruclips.net/video/7YQxowIfAi4/видео.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;
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 RUclips. 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?
This video is life saver. Thank you very much!
Glad it helped!
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.
Very importan 'islands and gaps' question, thanks for sharing
Hi Great Video. But how do I change this to show consecutive days per product if I have a listing of various products??
Thank you so much. I was struggling with this.
Thank you
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.
Thanks for the tutorial Maam. Please create a video for complex flat file column
Thank you
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 RUclips 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!
How to get the dataset in the video for practise?
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?
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 - ruclips.net/video/7YQxowIfAi4/видео.html
But suppose we want to list all the consecutive dates in the same column, then in that case how can we do it.
Hello
DATEADD is not working in my Orcale. please, help me to get same output(no of Consecutive days) without DATEADD function
Thanks
Will this query work if the dates are 1st may, 3rd may. 5th may so on.... It won't.
This was helpful, thank you.
Thank you
Very easy to understand :)
Thank you 🙏
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
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.
Excellent
Thank you
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.
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
What if there repeated order date values
Then row number will be
1 4-1-21
2 4-1-21
brilliant
Thank you
Thank you..
Thank you so much.
Why don't use lead()?
can you explain how we can use lead()?
--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 🙏
If 2 or more order dates are repeated, as they're bound to in a real world, this logic will fail because the row number function will assign different incremental values to each of them. Therefore, when you do the subtraction with the dateadd function, the resulting dates will not be the same even though they're supposed to
-- 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