Færdiggjort

Write a BigQuery query to return week-on-week, month-on-month and year-on-year data in one line

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.

Evner: Google Cloud Platform, SQL

Se mere: shell script remove single line file, remove single line file shell scripting, query mysql data php entries, write mysql query, ajax php mysql return data, query collect data web, create data entry line word 2007, write read serial port data php5, excel web query return data due embedded javascript, write sql query days data, write search query using data structures java, sql server import and export wizard write a query to specify the data to transfer, write a query in r to store data from r to mongodb, write a query in r and python to store data from r/python to mongodb, write a simple python program using single line comment and multi-line comment, a data analyst is concerned that a bigquery query could be too expensive., write a query to display the names of customers who have ordered more than 2 times., write a single line js comment this is a comment, write a query which will return all cities more customers that the average number of customers

Om arbejdsgiveren:
( 3 bedømmelser ) Thornlands, Australia

Projekt ID: #29932657

Tildelt til:

Vickybobo

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 Flere

$20 AUD på 1 dag
(1 bedømmelse)
0.6

3 freelancere byder i gennemsnit $23 timen for dette job

haripatel96

I have experience in advance mysql , I can help you with that , thank you

$25 AUD in 4 dage
(0 bedømmelser)
0.0
egyanes

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 Flere

$25 AUD in 7 dage
(0 bedømmelser)
0.0