Write a BigQuery query to return week-on-week, month-on-month and year-on-year data in one line
$10-30 AUD
Completed
Posted almost 3 years ago
$10-30 AUD
Paid on delivery
What I want to do is return a single row (other than header, of course) with different sums of the 'widgets' based on different date ranges relative to the current date.
In this instance let's assume we've got a table which contains dates (in single date increments) for the past few years and a single metric column called 'widgets'. Let's call the dataset 'dataset1' for the sake of simplicity and the table can be called 'table1'. Sample data can be found at this Google Drive CSV export link - [login to view URL]
Here's a description of the columns I'd like to be returned:
Col1:
Current date - not hard, let's say this was run on 2021-04-18 using CURRENT_DATE(+10).
Col2:
Week commencing date for the last full week commencing on a Wednesday. We could use WEEK(WEDNESDAY) but that'll return a number in the range [0,53] - how do we convert that back to a date commencing?
Col3:
Week commencing date for the last full week before the week now in Col2. Think this would be an easy DATE_SUB?
Col4:
Week commencing date for the week in Col2 but year-on-year. Unfortunately we can't just subtract a year from the date otherwise that will be on a different day of week.
Col5:
Date commencing of the last full month completed. I think this is relatively easy as we can use something like...
DATE_SUB(DATE CURRENT_DATE(+10), INTERVAL 1 MONTH)
I'm just unsure about this part of the documentation though and how that might throw things out:
Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date's day, then the result day is the last day of the new month.
Col6:
Sum of widgets filtered to the week commencing date described in Col2.
Col7 through to Col9:
Sum of widgets filtered to times matching Col3, Col4 and Col5 respectively (Col3/4 being a week and Col5 being a month).
Just say the query was run today, I'd be expecting the following as a result:
Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Col8 | Col9
2021-04-18 | 2021-04-07 | 2021-03-31 | 2020-04-01^ | 2021-03-01 | 147,350 | 140,063 | 167,610^ | 632,891
^ NOTE - I think this should be correct since 2020 started on a Wednesday, so it should be 2020-04-01 but it could potentially be 2020-04-08. I've based Col4 and Col8 on 2020-04-01 though.
If anyone could provide a bit of expertise it would be very much appreciated.
Hello,
I am experienced DBA and my main skill is about SQL.
I read your project detail and I also finished writting query to select all data as you needed.
Please contact me to discuss and we can test about it.
Thanks you very much!
$20 AUD in 1 day
5.0 (1 review)
0.6
0.6
3 freelancers are bidding on average $23 AUD for this job
Buen día: Tengo experiencia como DBA en Informix Database, he trabajado también con bases de datos SQL SERVER, tengo amplia experiencia en querys grandes y complejos, optimizándolos en su rendimiento. cuento con mas de 15 años de experiencia