bigquery-public-data covid19_open_data covid19_open_data count_of_states
SELECT
COUNT(*) AS count_of_states
FROM (
SELECT
subregion1_name AS state,
SUM(cumulative_deceased) AS death_count
FROM
`bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
country_name="United States of America"
AND date='2020-04-10'
AND subregion1_name IS NOT NULL
GROUP BY
subregion1_name
)
WHERE death_count > 100