CommencerCommencer gratuitement

Finding the most popular artists

Something that we haven't been able to do is tie track sales to an artist. Let's change that! You're about to get hands-on using common table expressions to take data from two tables and find the artist with the most minutes listened. Before you get started, make sure to take a peek at the album and artist tables in the output window.

Cet exercice fait partie du cours

Data Manipulation in Snowflake

Afficher le cours

Instructions

  • Define an artist_info CTE that JOINs the artist and album tables on the artist_id field.
  • Create a second CTE called track_sales that retrieves the album_id, name, and number of seconds per track from the track table.
  • Calculate the total minutes listened for each artist.
  • Group the results on artist_name from the appropriate CTE.

Exercice interactif pratique

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

-- Create an artist_info CTE, JOIN the artist and album tables
___ ___ ___ (
    SELECT
        album.album_id,
        artist.name AS artist_name
    FROM store.album
    JOIN store.artist ON album.artist_id = artist.artist_id

-- Define a track_sales CTE to assign an album_id, name,
-- and number of seconds for each track
), ___ ___ (
    SELECT
        track.___,
        track.___,
        track.milliseconds / 1000 AS num_seconds
    FROM store.invoiceline
    JOIN store.track ON invoiceline.track_id = track.track_id
)

SELECT
    ai.artist_name,
    -- Calculate total minutes listed
    SUM(___) / 60 AS minutes_listened
FROM track_sales AS ts
JOIN artist_info AS ai ON ts.album_id = ai.album_id
-- Group the results by the non-aggregated column
GROUP BY ___.___
ORDER BY minutes_listened DESC;
Modifier et exécuter le code