https://dba.stackexchange.com/q/329095/8783 Same Variable Changing its Min/Max Values within the Same Group?

stackexchange.sql

WITH ntiles AS (
    SELECT 
        id,
        height,
        weight,
        gender,
        country,
        favorite_color,
        disease, 
        NTILE(5) OVER (PARTITION BY gender, country, favorite_color ORDER BY height) as height_ntile
    FROM my_table
),
ntiles2 AS (
    SELECT 
        *,
        NTILE(5) OVER (PARTITION BY gender, country, favorite_color, height_ntile ORDER BY weight) as weight_ntile
    FROM ntiles
),
height_bounds AS (
    SELECT 
        gender,
        country,
        favorite_color,
        height_ntile, 
        MIN(height) as min_height, 
        MAX(height) as max_height
    FROM ntiles
    GROUP BY gender, country, favorite_color, height_ntile
)
SELECT 
    n2.height_ntile,
    n2.weight_ntile, 
    h.min_height, 
    h.max_height,
    n2.gender,
    n2.country,
    n2.favorite_color,
    MIN(n2.weight) as min_weight, 
    MAX(n2.weight) as max_weight,
    COUNT(*) as count, 
    COUNT(CASE WHEN n2.disease = 'y' THEN 1 END) as disease_count,
    COUNT(CASE WHEN n2.disease = 'y' THEN 1 END)*100.0/COUNT(*) as disease_rate
FROM ntiles2 n2
JOIN height_bounds h 
    ON n2.height_ntile = h.height_ntile
    AND n2.gender = h.gender
    AND n2.country = h.country
    AND n2.favorite_color = h.favorite_color
GROUP BY n2.height_ntile, n2.weight_ntile, h.min_height, h.max_height, n2.gender, n2.country, n2.favorite_color;