BMI bucket by sport
You are looking to understand how BMI differs by each summer sport. To answer this, set up a report that contains the following:
- sport, which is the name of the summer sport
- bmi_bucket, which splits up BMI into three groups:
<.25
,.25-.30
,>.30
- athletes, or the unique number of athletes
Definition: BMI = 100 * weight / (height squared).
Also note that CASE
statements run row-by-row, so the second conditional is only applied if the first conditional is false. This makes it that you do not need an AND
statement excluding already-mentioned conditionals.
Feel free to reference the E:R Diagram.
This exercise is part of the course
Reporting in SQL
Exercise instructions
- Build a query that pulls from
summer_games
andathletes
to showsport
,bmi_bucket
, andathletes
. - Without using
AND
orELSE
, set up aCASE
statement that splitsbmi_bucket
into three groups:'<.25'
,'.25-.30'
, and'>.30'
. - Group by the non-aggregated fields.
- Order the report by sport and then athletes in descending order.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Pull in sport, bmi_bucket, and athletes
SELECT
____,
-- Bucket BMI in three groups: <.25, .25-.30, and >.30
CASE WHEN ____ THEN '<.25'
WHEN ____ THEN '.25-.30'
WHEN ____ THEN '>.30' END AS bmi_bucket,
____ AS athletes
FROM ____ AS s
JOIN ____ AS a
ON ____
-- GROUP BY non-aggregated fields
GROUP BY ____
-- Sort by sport and then by athletes in descending order
ORDER BY ____;