linfo2172-databases/P3/queries/query10.sql

-- List the top-10 of provinces for which the difference between the total number of items in purchases of the last 10 days and the preceding period of 10 days is the largest (i.e., the increase is large; we do not look for the largest decreases); list the full name of the province, the full name of the country, and the number of purchases in the last 10 days, as well as in the preceding period of 10 days; order the results by the size of the difference. Note that if there is no preceding period, the outcome is supposed to be empty.


-- Expected Heading: RELATION {province CHARACTER, country CHARACTER, qty_last10 INTEGER, qty_1020 INTEGER}
-- How do you treat the default case as showed in the instructions?

SELECT province.name AS province, country.name AS country, qty_last10, qty_1020
FROM (
	SELECT province, SUM(qty) as qty_last10
	FROM Purchases
	WHERE time > date((SELECT MAX(time) FROM Purchases),'-10 days')
	GROUP BY province
) AS L
JOIN (
	SELECT province, SUM(qty) as qty_1020
	FROM Purchases
	WHERE time > date((SELECT MAX(time) FROM Purchases),'-20 days') AND time <= date((SELECT MAX(time) FROM Purchases),'-10 days')
	GROUP BY province
) AS P ON L.province = P.province
JOIN province ON L.province = province.ROWID
JOIN country ON province.country = country.code
ORDER BY qty_last10-qty_1020 DESC
LIMIT 10
;