Introduction

The index containing the Olympic event data is now populated with, at least what we currently consider to be, clean data. It’s in good shape to begin some analysis and in these exercises we’ll start to use queries and aggregations.

We’ll begin here by writing a few aggregations and scripted fields with some simple searches.

Before starting this set, make sure you have worked through the first and second sets of exercises.

Exercises

Exercise 19

Write a single query to find the name of all Gymnastics events. There are less than 100 Gymnastics event types.

Exercise 20

Write a single query to find the average weight for male and female competitors in Gymnastics events.

Exercise 21

Write a single query to find the year that each of the 590 unique events first appeared in the Olympic Games, and which events were introduced most recently.

Exercise 22

Write a query to return only the following fields for the 50 tallest athletes in the 2016 Rio de Janeiro Games:

• athleteName
• team
• sport
• age
• height
• weight
• gender

Exercise 23

The weight and height fields are in metric. Weight is in kg and height is in cm. Add a scripted field called weightLbs to the previous query to return the weight in lbs. The formula for this is: Weight * 2.2

Exercise 24

Add a scripted field called bmi to the previous query to return the BMI for each athlete, calculated using the following formula: Weight / (Height in m squared)

Next steps

Part four of the exercises can be found here.