calculate average in separate column over period and group by date Standard SQL BigQuery

Question

I have a table in BQ that looks like this:

date          rate 
02/02/22       null
02/01/22       null
01/31/22        1
01/30/22        1.5
01/29/22        0.5

I want to create avg_rate column. I tried simple calculations for averages but because I have a group by statement - it assigns nulls to the avg_rate column. I need each date where the rate is null to grab all sums of rate that are not nulls and divide by rows count (for those that has not nulls for rate) and assign this number to each date. Here is my query:

SELECT
  date,
  SUM(rate) / COUNT(*) AS avg_rate
FROM
  `my_table`
GROUP BY
  1

The output I a getting:

date          avg_rate 
02/02/22       null
02/01/22       null
01/31/22        1
01/30/22        1.5
01/29/22        0.5

Desired output is:

date          avg_rate 
02/02/22        1
02/01/22        1
01/31/22        1
01/30/22        1.5
01/29/22        0.5

Answer 1

You can use coalesce to return the avg grouped by date, and if it's null return the total average of the column instead using a subquery:

select date, coalesce(avg(rate), (select avg(rate) from my_table))
from my_table
group by date