BigQuery - Get percentage of null values in column grouped by another

Question

I have a table like

store_code	country_code	timestamp
null	FR	1234567890
123	FR	1234567890
456	GB	1234567890
789	GB	1234567890

I would like to get

pct_null_store_code	country_code
0.5	FR
0.0	GB

I know how to do it with a WHERE clause as in

SELECT SAFE_DIVIDE(COUNTIF(store_code IS NULL), COUNT(1)) as pct_null_store_code
FROM table
WHERE country_code = 'XX'

But I would like to only have to exec 1 query and get the results for each country_code

Answer 1

One method just uses avg():

SELECT country_code, 
       AVG(case when store_code IS NULL then 1.0 else 0 end) as null_ratio
FROM table
GROUP BY country_code;

If you want to do the count and divide by the total, then use COUNTIF():

SELECT country_code, 
       COUNTIF(store_code IS NULL) * 1.0 / COUNT(*) as null_ratio
FROM table
GROUP BY country_code;