I am trying the get the count of week days between two dates for which I have not found the solution in BigQuery standard sql. I have tried the BQ sql date function DATE_DIFF(date_expression_a, date_expression_b, date_part) following published examples, but it did not reveal the result.
For example, I have two dates 2021-02-13 and 2021-03-31 and my desired outcome would be:
| MON | TUE | WED | THUR | FRI | SAT | SUN |
|---|---|---|---|---|---|---|
| 6 | 6 | 6 | 6 | 7 | 7 | 7 |
Consider below approach
with your_table as (
select date
from unnest(generate_date_array("2021-02-13", "2021-03-30")) AS date
)
select * from your_table
pivot (count(*) for format_date('%a', date) in ('Mon','Tue','Wed','Thu','Fri','Sat','Sun'))
with output
Or you can just simply do
select
format_date('%a', date) day_of_week,
count(*) counts
from your_table
group by day_of_week
with output
You can do the following:
SELECT
CASE EXTRACT(DAYOFWEEK
FROM
dates)
WHEN 1 THEN 'MON'
WHEN 2 THEN 'TUE'
WHEN 3 THEN 'WED'
WHEN 4 THEN 'THU'
WHEN 5 THEN 'FRI'
WHEN 6 THEN 'SAT'
WHEN 7 THEN 'SUN'
END
AS day_of_week,
COUNT(*) AS day_count
FROM
UNNEST(GENERATE_DATE_ARRAY("2021-02-13", "2021-03-30")) AS dates
GROUP BY 1
The important part is the GENERATE_DATE_ARRAY function, that will return all the dates between the dates you're interested in. UNNEST will return one row for each date (instead of one row for the array of all dates).
From there, you can extract the day of the week thanks to the BQ date functions, and count the number of occurences with a GROUP BY day_of_week.
The above query gives the following result:
