Back in the Database Programming section of this series, we created the view named
us_counties_new, showing the daily new case load. Recall that we first computed the seven-day running average of the total case loads in the
us_counties_avg7 view, then used those numbers for the daily case loads. Having the daily case loads available lets us do some interesting analysis.
We can use those numbers to discover the peak case load in each county. We do that with a simple GROUP BY query, like this one.
SELECT MAX(newcases7) peakcases, MAX(newdeaths7) peakdeaths, state, county, fips FROM us_counties_new GROUP BY state, county, fips;
Once we have the peak case load for each county, we can use it to retrieve the date of the peak with a JOIN query like the one in this view.
CREATE OR REPLACE VIEW us_counties_peakcases AS SELECT DATEDIFF(CURDATE(), date) daysago, detail.date, detail.state, detail.county, detail.fips, detail.newcases7, detail.newdeaths7 FROM us_counties_new detail JOIN ( SELECT MAX(newcases7) peakcases7, MAX(newdeaths7) peakdeaths7, state, county, fips FROM us_counties_new GROUP BY state, county, fips ) peak ON detail.state = peak.state AND detail.county = peak.county AND detail.newcases7 = peak.peakcases7;
This query handles a lot of number crunching and can be slow. So, let’s put it into a view so we can easily add WHERE filtering. For example, we can find places where the peak case load occurred within the past three days like this:
SELECT * FROM us_counties_peakcases WHERE daysago <= 10 ORDER BY daysago