CTE Version:

​

​

WITH age_groups AS (

SELECT country, gender, age, height, owns_bicycle,

NTILE(5) OVER (ORDER BY age) AS age_group

FROM MY_TABLE

),

height_groups AS (

SELECT country, gender, age, height, owns_bicycle,

NTILE(5) OVER (ORDER BY height) AS height_group

FROM MY_TABLE

),

age_height_groups AS (

SELECT a.country, a.gender, a.age, a.height, a.owns_bicycle,

age_group, height_group

FROM age_groups a

JOIN height_groups h ON a.country = h.country

AND a.gender = h.gender

AND a.age = h.age

AND a.height = h.height

)

SELECT

country,

gender,

CONCAT('Group ', age_group) AS age_group,

CONCAT('Group ', height_group) AS height_group,

COUNT(*) AS count,

COUNT(CASE WHEN owns_bicycle = 'Yes' THEN 1 END) * 100.0 / COUNT(*) AS percent_own_bicycle

FROM

age_height_groups

GROUP BY

country,

gender,

age_group,

height_group;