get sum of distinct records and compare between two date ranges

Question

I have a table called purchases, with columns: name, amount, city, date.

I want to return the sum of amount and order it by city with most sum of amount, and compare two date ranges.

Here is a fiddle: http://sqlfiddle.com/#!9/6b2017/6

I can get the first set of cities with their sum(amount) in order of sum(amount) desc (period 1):

select distinct city, sum(amount) as total
from purchases
where date between '2020-07-01' and '2020-10-31'
group by city
order by sum(amount) desc
limit 3

The query above returns cities: Tulsa, New York, Chicago

but I also want to get in the same query, the previous 3 months, but with the same cities that were returned in the previous query, something like this, but like I mentioned, I want it returned in just one query (period 2)

select city, sum(amount) as total
from purchases
where city in ('Tulsa', 'New York', 'Chicago')
  and date between '2020-03-01' and '2020-06-31'
group by city

Looking to return the cities (in the order they are in the first query), with sum(amount) per each date range, so in theory:

Tulsa period 1: sum(amount) Tulsa period 2: sum(amount) New York period 1: sum(amount) New York period 2: sum(amount) Chicago period 1: sum(amount) Chicago period 2: sum(amount)

Any help is appreciated

Answer 1

Use a LEFT join of your query to the table and aggregate again:

SELECT t.city, t.total period1, COALESCE(SUM(p.amount), 0) period2
FROM (
  SELECT city, SUM(amount) AS total 
  FROM purchases 
  WHERE date BETWEEN '2020-07-01' AND '2020-10-31' 
  GROUP BY city 
  ORDER BY total DESC LIMIT 3
) t LEFT JOIN (
  SELECT city, amount
  FROM purchases
  WHERE date BETWEEN '2020-03-01' AND '2020-06-30'
) p ON p.city = t.city
GROUP BY t.city, t.total
ORDER BY t.total DESC;

Or, with conditional aggregation:

SELECT city,
       SUM(CASE WHEN date BETWEEN '2020-07-01' AND '2020-10-31' THEN amount ELSE 0 END) period1,
       SUM(CASE WHEN date BETWEEN '2020-03-01' AND '2020-06-30' THEN amount ELSE 0 END) period2
FROM purchases
GROUP BY city
ORDER BY period1 DESC LIMIT 3;

Or:

SELECT city,
       SUM(CASE WHEN date >= '2020-07-01' THEN amount ELSE 0 END) period1,
       SUM(CASE WHEN date < '2020-07-01' THEN amount ELSE 0 END) period2
FROM purchases
WHERE date BETWEEN '2020-03-01' AND '2020-10-31'
GROUP BY city
ORDER BY period1 DESC LIMIT 3;

See the demo.