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

Suppose you have this:

SELECT *
FROM (
  select 1 as i union select 2 union select null
) x;

This will output:

i
1
2
NULL

With some aggregate functions added:

select avg(i), count(i), sum(i), count(*) 
from (
   select 1 as i union select 2 union select null
) x;

The output is:

avg(i)	count(i)	sum(i)	count(*)
1.5000	2	3	3

• As you can see count(i) counts the not null values
• and count(*) counts all the values