CommencerCommencer gratuitement

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!

Cet exercice fait partie du cours

Data Types and Functions in Snowflake

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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 ___)
);
Modifier et exécuter le code