ComeçarComece de graça

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.

Este exercício faz parte do curso

Data Manipulation in Snowflake

Ver curso

Instruções do exercício

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

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

-- 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;
Editar e executar o código