Get startedGet started for free

Which workouts burn the most calories?

It's almost the New Year, one of the busiest times for gyms everywhere. To help motivate members, the marketing team would like to create personalized content for each member to push them towards their most effective workout type. They need you to build a pivoted table using Snowflake to show the average calories burned for each member and gym, by workout type. Good luck!

This exercise is part of the course

Data Types and Functions in Snowflake

View Course

Exercise instructions

  • Create a CTE called gym_workouts that returns the user_id, gym_id, workout_type, calories_burned from the visits table and location for 'Premium' gym_type in the gyms table.
  • Exclude the gym_id field from the final result set.
  • Pivot the records in the gym_workouts temporary result set; find the sum of calories burned for each workout type in workout_type.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Create a CTE called gym_workouts returns the user_id, gym_id, 
-- workout_type, calories_burned and location for 'Premium' gym types
___ ___ AS (
    SELECT
  		visits.___,
        visits.___,
        visits.___,
  		visits.___,
        ___.___
    FROM CORE_GYM.visits
    JOIN CORE_GYM.gyms ON visits.gym_id = gyms.gym_id
    WHERE ___
)

SELECT
	-- Do NOT include the gym_id field in the final output
    * ___ ___
FROM gym_workouts
-- Pivot gym_workouts, find the sum of calories_burned for each 
-- type of workout in workout_type
___(
    ___(___) 
    ___ ___ IN (ANY ORDER BY ___)
);
Edit and Run Code