IniziaInizia gratis

Finding the most efficient composer

Here's a fun one! You're chatting with your coworker, and you decide that you want to find the artist that (on average) prices their songs the most per second. To do this, you'll use the tracks table and a Common Table Expression.

Questo esercizio fa parte del corso

Data Manipulation in Snowflake

Visualizza il corso

Istruzioni dell'esercizio

  • Create a Common Table Expression (CTE) called track_metrics that retrieves the composer, number of seconds and unit_price.
  • When defining track_metrics, make sure to retrieve only records with a non-empty value for composer.
  • Query the track_metrics CTE to find the average price-per-second for each composer using the formula unit_price / num_seconds.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

-- Create a CTE called track_metrics, convert milliseconds to seconds
___ ___ ___ (
    SELECT 
        composer,
        milliseconds / 1000 AS num_seconds,
        unit_price
    FROM store.track
 	-- Retrieve records where composer is not NULL
    ___
)

SELECT
    composer,
    -- Find the average price-per-second
    ___ AS cost_per_second
___
GROUP BY composer
ORDER BY cost_per_second DESC;
Modifica ed esegui il codice