Get startedGet started for free

Analyzing track length

Previously, you used a subquery to find the average length of songs, in milliseconds, for each genre. Now, you're going to do something similar with a common table expression, but this time, with a bit more attention to detail. Let's get to it!

This exercise is part of the course

Data Manipulation in Snowflake

View Course

Exercise instructions

  • Write a common table expression with the name track_lengths to retrieve the name field from store.genre, as well as the genre_id and milliseconds from the store.track table.
  • Find the average track_length, in seconds, for each genre using the track_lengths common table expression.
  • Sort the results by the average track length, from longest to shortest.

Hands-on interactive exercise

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

-- Create a CTE named track_lengths
___ ___ ___ (
	SELECT
        genre.name,
        track.genre_id,
        track.milliseconds / 1000 AS num_seconds
    FROM store.track
    JOIN store.genre ON track.genre_id = genre.genre_id
)

SELECT
    track_lengths.name,
    -- Find the average length of each track in seconds
    ___(___.___) AS avg_track_length
FROM ___
GROUP BY track_lengths.name
-- Sort the results by average track_length
___;
Edit and Run Code