Find Jobs
Hire Freelancers

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.
Project ID: 29932657

About the project

3 proposals
Remote project
Active 3 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
Awarded to:
User Avatar
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
User Avatar
I have experience in advance mysql , I can help you with that , thank you
$25 AUD in 4 days
0.0 (0 reviews)
0.0
0.0
User Avatar
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
$25 AUD in 7 days
0.0 (0 reviews)
0.0
0.0

About the client

Flag of AUSTRALIA
Thornlands, Australia
5.0
3
Payment method verified
Member since Dec 22, 2015

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.