26 lines
1.2 KiB
SQL
26 lines
1.2 KiB
SQL
-- Determine for each country the province in which the total number of purchases of product 0 is the highest; list the name of the country, the name of the province, and the number of purchases, both absolute and as a proportion of the total number of purchases in that country.
|
|
|
|
-- Expected Heading: RELATION {country CHARACTER, province CHARACTER, abs INTEGER, prop FLOAT}
|
|
-- How do you treat the default case as showed in the instructions?
|
|
-- How do you treat the case where several provinces of a same country have the same total number of purchases of product 0? You should show all such provinces.
|
|
|
|
SELECT
|
|
ProductZeroPurchasesByProvince.CountryName AS country,
|
|
ProductZeroPurchasesByProvince.ProvinceName AS province,
|
|
MaxCount AS maximum,
|
|
CAST(MaxCount AS FLOAT) / TotalCount AS proportion
|
|
FROM (
|
|
SELECT
|
|
CountryName AS CountryMax,
|
|
MAX(cnt) AS MaxCount,
|
|
SUM(cnt) AS TotalCount
|
|
FROM
|
|
ProductZeroPurchasesByProvince
|
|
GROUP BY
|
|
CountryName
|
|
) AS AggregatedData
|
|
JOIN
|
|
ProductZeroPurchasesByProvince ON AggregatedData.CountryMax = ProductZeroPurchasesByProvince.CountryName
|
|
WHERE
|
|
AggregatedData.MaxCount = ProductZeroPurchasesByProvince.cnt;
|