Get startedGet started for free

Albums driving sales

Your director came up to your desk today and gave you the inside scoop on a holiday promotion on "Greatest Hits" albums that will be dropping soon. However, to make sure the optimal albums are discounted, she wants to know which "Greatest Hits" albums drive the most sales. To do this, you'll put all the skills you learned to the test!

This exercise is part of the course

Data Manipulation in Snowflake

View Course

Exercise instructions

  • Define a CTE called album_map.
  • Create a CASE statement that returns TRUE if greatest is in an album's title and FALSE otherwise, aliasing as is_greatest_hits.
  • Update the trimmed_invoicelines CTE, LEFT JOINing the invoice and tracks table to invoicelines on invoice_id and track_id respectively.
  • Use a subquery to only return "Greatest Hits" albums.

Hands-on interactive exercise

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

-- Define an album_map CTE to combine albums and artists
___ (
    SELECT
        album.album_id, album.title AS album_name, artist.name AS artist_name,
  		-- Determine if an album is a "Greatest Hits" album
        ___ 
            ___ album_name ILIKE '%greatest%' ___ TRUE
            ELSE FALSE
        ___ AS ___
    FROM store.album
    JOIN store.artist ON album.artist_id = artist.artist_id
), trimmed_invoicelines (
    SELECT
        invoiceline.invoice_id, track.album_id, invoice.total
    FROM store.invoiceline
    LEFT JOIN store.invoice ON invoiceline.invoice_id = invoice.invoice_id
    LEFT JOIN store.track ON invoiceline.track_id = track.track_id
)

SELECT
    album_map.album_name,
    album_map.artist_name,
    SUM(ti.total) AS total_sales_driven
FROM trimmed_invoicelines AS ti
JOIN album_map ON ti.album_id = album_map.album_id
-- Use a subquery to only "Greatest Hits" records
___ ti.___ ___ (SELECT album_id FROM album_map WHERE is_greatest_hits)
GROUP BY album_map.album_name, album_map.artist_name, is_greatest_hits
ORDER BY total_sales_driven DESC;
Edit and Run Code