Comparing aggregated data to unaggregated data in SQL

Depending on the type of questions we want to answer there might be times when we need to compare aggregated data to unaggregated data in SQL.

This is where the fact that the GROUP BY clause forces us to 'squeeze' all unaggregated columns into one row can be a headache.

In this article, you would see how a window function can make this quite easy.

The Big question

Suppose we wanted to answer the following question using our COVID Dataset:

How does each case recorded in Nigeria from March 2020 to June 2020 compared with the maximum case recorded within the same time frame.

We can answer this question by returning a table that has the recorded case for each day and the maximum case recorded in a day within the time frame. Something like this:

This would allow us to easily plot graphs that help us with the comparison. Let's see how we can solve this

The Inefficient solution

We can generate this result using a subquery in the SELECT clause. This subquery would calculate the maximum value and return that value for each row in our table. See the code below:

SELECT
  date,
  countries_and_territories as country,
  daily_confirmed_cases AS confirmed_today,
  (
    SELECT
      MAX(daily_confirmed_cases)
    FROM
      `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
    WHERE
      geo_id = 'NG'
      AND date >= '2020-03-01'
      AND date < '2020-06-01'
  ) as highest_case_recorded_in_a_day
FROM
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
WHERE
  geo_id = 'NG'
  AND date >= '2020-03-01'
  AND date < '2020-06-01';

This returns the following results:

As you can see it returns exactly the shape of the data that we want in our table. Now we can go ahead and generate a chart, like the one shown below, which are easier to comprehend:

You can interact with the chart here

So what are the problems

The first issue is that subqueries perform calculations by running through the entire table. This simply means that the query above runs through the table twice to get the results that we got. This can affect performance

Another problem is that subqueries are quite difficult to read and maintain. It is quite difficult to grasp what the query above is doing at a glance

The window function solution

Now let's try to solve this problem using a window function.
The way we would do this is to replace the subquery part of the first query with our window function, like so:

SELECT
  date,
  countries_and_territories as country,
  daily_confirmed_cases AS confirmed_today,
  MAX(daily_confirmed_cases) OVER() as highest_case_recorded_in_a_day
FROM
  `bigquery-public-data.covid19_ecdc.covid_19_geographic_distribution_worldwide`
WHERE
  geo_id = 'NG'
  AND date >= '2020-03-01'
  AND date < '2020-06-01'

The OVER() function is what starts our window function(as stated in the previous article in the series) while the MAX() gets the maximum case recorded on a particular day in the specified time frame.

The window function uses the ResultSet gotten from the WHERE part of the query thus there it does not run through the entire table like the subquery alternative.

In the end, you get a much shorter, more readable, maintainable and performant code.

Conclusion

What I have described in this article is a very simple use case of a window function. Let me know what insightful questions related to COVID-19 we could answer by comparing aggregated and unaggregated data in the comments section.

Next in this series, we would be talking about performing calculations that are dependent on the value of other rows eg. running totals, daily_increase in cases etc.