CommencerCommencer gratuitement

Identifying high-performers

What's more fun than a leaderboard? The operations team wants to use the data in the CORE_GYM schema to create a member leader board based on calories burned. To do this, they're leaning on you to generate a result set that combines the members and visits tables.

Cet exercice fait partie du cours

Data Types and Functions in Snowflake

Afficher le cours

Instructions

  • Using a CTE, create a temporary result set called flattened_members that retrieves the user_id, first name, and last name from the members table.
  • Define a second temporary result set called high_performers that returns all records in the visits table with a calories_burned > 500.
  • Join the flattened members output to high_performers on the user_id field.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

-- Retrieves the user_id, first name, and last name from the members table
___ ___ ___ (
    SELECT
        user_id,
        personal_info:name.___ AS first_name,
        personal_info:name.___ AS last_name,
    FROM CORE_GYM.members),
  
-- high_performers should return all visits where > 500 calories were burned
___ ___ (
    SELECT 
        user_id,
        TO_DATE(checkin_time) AS workout_date,
        workout_type,
        calories_burned
    FROM CORE_GYM.visits
    WHERE ___)

SELECT
    CONCAT(flattened_members.first_name, ' ', flattened_members.last_name) AS full_name,
    high_performers.workout_date,
    high_performers.workout_type,
    high_performers.calories_burned
FROM high_performers
-- JOIN flattened_members to high_performers on the user_id field
JOIN ___ ON ___.___ = high_performers.user_id;
Modifier et exécuter le code