CommencerCommencer gratuitement

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!

Cet exercice fait partie du cours

Data Manipulation in Snowflake

Afficher le cours

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.

Exercice interactif pratique

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