How to get weekly data but starting from the first date of the month and do SUM calculation accordingly in BQ?

Question 1

I have an issue to pull this kind of data. So I need to pull weekly data with these specifications:

The data pull will be scheduled, hence it will involve multiple months
The very first week will start from the first date (1 in every month) -- Green in the pic
The last week doesn't involve dates from the next month -- Red in the pic

The raw data and the desirable output(s) will more or less look like this:

Is there any workaround to do this in BigQuery? Thanks (attached below the data)

+-------------+-------+
| date        | sales |
+-------------+-------+
|  1 Oct 2021 |     5 |
+-------------+-------+
|  2 Oct 2021 |    13 |
+-------------+-------+
|  3 Oct 2021 |    75 |
+-------------+-------+
|  4 Oct 2021 |     3 |
+-------------+-------+
|  5 Oct 2021 |    70 |
+-------------+-------+
|  6 Oct 2021 |    85 |
+-------------+-------+
|  7 Oct 2021 |    99 |
+-------------+-------+
|  8 Oct 2021 |    90 |
+-------------+-------+
|  9 Oct 2021 |    68 |
+-------------+-------+
| 10 Oct 2021 |    97 |
+-------------+-------+
| 11 Oct 2021 |    87 |
+-------------+-------+
| 12 Oct 2021 |    56 |
+-------------+-------+
| 13 Oct 2021 |    99 |
+-------------+-------+
| 14 Oct 2021 |    38 |
+-------------+-------+
| 15 Oct 2021 |     6 |
+-------------+-------+
| 16 Oct 2021 |    43 |
+-------------+-------+
| 17 Oct 2021 |    45 |
+-------------+-------+
| 18 Oct 2021 |    90 |
+-------------+-------+
| 19 Oct 2021 |    64 |
+-------------+-------+
| 20 Oct 2021 |    26 |
+-------------+-------+
| 21 Oct 2021 |    24 |
+-------------+-------+
| 22 Oct 2021 |     4 |
+-------------+-------+
| 23 Oct 2021 |    36 |
+-------------+-------+
| 24 Oct 2021 |    68 |
+-------------+-------+
| 25 Oct 2021 |     4 |
+-------------+-------+
| 26 Oct 2021 |    16 |
+-------------+-------+
| 27 Oct 2021 |    30 |
+-------------+-------+
| 28 Oct 2021 |    89 |
+-------------+-------+
| 29 Oct 2021 |    46 |
+-------------+-------+
| 30 Oct 2021 |    28 |
+-------------+-------+
| 31 Oct 2021 |    28 |
+-------------+-------+
|  1 Nov 2021 |    47 |
+-------------+-------+
|  2 Nov 2021 |    75 |
+-------------+-------+
|  3 Nov 2021 |     1 |
+-------------+-------+
|  4 Nov 2021 |    26 |
+-------------+-------+
|  5 Nov 2021 |    26 |
+-------------+-------+
|  6 Nov 2021 |    38 |
+-------------+-------+
|  7 Nov 2021 |    79 |
+-------------+-------+
|  8 Nov 2021 |    37 |
+-------------+-------+
|  9 Nov 2021 |    83 |
+-------------+-------+
| 10 Nov 2021 |    97 |
+-------------+-------+
| 11 Nov 2021 |    56 |
+-------------+-------+
| 12 Nov 2021 |    83 |
+-------------+-------+
| 13 Nov 2021 |    14 |
+-------------+-------+
| 14 Nov 2021 |    25 |
+-------------+-------+
| 15 Nov 2021 |    55 |
+-------------+-------+
| 16 Nov 2021 |    16 |
+-------------+-------+
| 17 Nov 2021 |    80 |
+-------------+-------+
| 18 Nov 2021 |    66 |
+-------------+-------+
| 19 Nov 2021 |    25 |
+-------------+-------+
| 20 Nov 2021 |    62 |
+-------------+-------+
| 21 Nov 2021 |    36 |
+-------------+-------+
| 22 Nov 2021 |    33 |
+-------------+-------+
| 23 Nov 2021 |    19 |
+-------------+-------+
| 24 Nov 2021 |    47 |
+-------------+-------+
| 25 Nov 2021 |    14 |
+-------------+-------+
| 26 Nov 2021 |    22 |
+-------------+-------+
| 27 Nov 2021 |    66 |
+-------------+-------+
| 28 Nov 2021 |    15 |
+-------------+-------+
| 29 Nov 2021 |    96 |
+-------------+-------+
| 30 Nov 2021 |     4 |
+-------------+-------+

Question 2

Consider below approach

with temp as (
  select parse_date('%d %B %Y', date) date, sales
  from your_table
)
select format_date('%d %B %Y', weeks[ordinal(num)]) start_week, sum(sales) total_sales 
from (
  select sales, weeks, range_bucket(date, weeks) num
  from temp, unnest([struct(generate_date_array(date_trunc(date, month), last_day(date, month), interval 7 day ) as weeks)])
)
group by start_week

if to apply to sample data (as is) in your question - output is

Mikhail Berlyant · Answer 1 · 2021-11-24T04:12:50

Consider below approach

with temp as (
  select parse_date('%d %B %Y', date) date, sales
  from your_table
)
select format_date('%d %B %Y', weeks[ordinal(num)]) start_week, sum(sales) total_sales 
from (
  select sales, weeks, range_bucket(date, weeks) num
  from temp, unnest([struct(generate_date_array(date_trunc(date, month), last_day(date, month), interval 7 day ) as weeks)])
)
group by start_week

if to apply to sample data (as is) in your question - output is

How to get weekly data but starting from the first date of the month and do SUM calculation accordingly in BQ?

In other languages

This page is in other languages

Popular in the category