Get startedGet started for free

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.

This exercise is part of the course

Data Types and Functions in Snowflake

View Course

Exercise 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample 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;
Edit and Run Code