linfo2172-databases/P3/queries/query3.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;