Get startedGet started for free

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.

This exercise is part of the course

Data Manipulation in Snowflake

View Course

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

Hands-on interactive exercise

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