Get count of day types between two dates

Question

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

Answer 1

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