Subtracting columns from separate BigQuery queries

user_9956822 0
Viewed 351

I have two separate BigQuery queries where I took the sum of confirmed cases for a certain date and grouped them by region and ordered in descending order by cases.

SELECT region, SUM(confirmed_cases) AS total_cases FROM provincedata WHERE date BETWEEN '2020-08-01' AND '2020-08-02' GROUP BY region ORDER BY total_cases DESC
SELECT region, SUM(confirmed_cases) AS total_cases FROM provincedata WHERE date BETWEEN '2020-08-31' AND '2020-09-01' GROUP BY region ORDER BY total_cases DESC

I want to calculate the difference between total_cases in the first and second queries and group and order by region and descending order by the difference in descending order.

google-bigquery pivot where-clause sum sql
2 Answers

Below is for BigQuery Standard SQL

The simplest way is just to reuse your queries that you already comfortable with (instead of rewriting stuff)

#standardSQL
WITH `project.dataset.query1` AS (
  SELECT region, SUM(confirmed_cases) AS total_cases 
  FROM provincedata 
  WHERE DATE BETWEEN '2020-08-01' AND '2020-08-02' 
  GROUP BY region 
), `project.dataset.query2` AS (
  SELECT region, SUM(confirmed_cases) AS total_cases 
  FROM provincedata 
  WHERE DATE BETWEEN '2020-08-31' AND '2020-09-01' 
  GROUP BY region 
)
SELECT region, q1.total_cases - q2.total_cases AS total_cases_difference
FROM `project.dataset.query1` q1 
JOIN `project.dataset.query2` q2
USING(region)
ORDER BY total_cases_difference DESC
user_52219440

This might be more efficiently expressed with conditional aggregation:

select
    region,
    sum(case when date between '2020-08-01' and '2020-08-02' then confirmed_cases else 0 end) total_cases_1,
    sum(case when date between '2020-08-31' and '2020-09-02' then confirmed_cases else 0 end) total_cases_2,
    sum(case when date between '2020-08-01' and '2020-08-02' then confirmed_cases else - confirmed_cases end) diff
from provincedata
where 
    date between '2020-08-01' and '2020-08-02'
    or date between '2020-08-31' and '2020-09-01' 
group by region
order by diff desc
user_106767160
Related