Spotify.sql.rtf

{\rtf1\ansi\ansicpg1252\cocoartf2709
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\paperw11900\paperh16840\margl1440\margr1440\vieww11520\viewh8400\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0

\f0\fs24 \cf0 SELECT * from spotify_top50_2021;\
--What is the average danceability by artist? \
SELECT artist_name,avg(danceability) AS average_danceability FROM spotify_top50_2021 GROUP by artist_name ORDER BY average_danceability DESC;\
\
--Who are the top 10 artists based on popularity, and what are their tracks' average danceability and energy?\
SELECT artist_name,avg(popularity) AS avg_popularity,avg(danceability) AS average_danceability, avg(energy) AS avg_energy FROM spotify_top50_2021 GROUP BY artist_name ORDER BY avg_popularity DESC LIMIT 10;\
\
--What artist released the longest song?\
SELECT artist_name,track_name,duration_ms FROM spotify_top50_2021 ORDER BY duration_ms DESC LIMIT 1;\
\
--What is the average danceability of the top 12 most popular songs?\
SELECT avg(danceability) AS average_danceability FROM (SELECT danceability FROM spotify_top50_2021 ORDER by \
                                                                   popularity DESC LIMIT 12) as top_12_popu_song;\
\
\
\
\
--Query the artists in top 50 and the count of top songs by each artist\
SELECT artist_name,COUNT(*) FROM spotify_top50_2021 GROUP by artist_name order by COUNT(*) DESC;\
\
--Query the songs that have another artist featured on them\
SELECT track_name FROM spotify_top50_2021\
WHERE track_name like '%feat%' OR track_name LIKE '%ft%';\
\
SELECT COUNT(*) FROM spotify_top50_2021\
WHERE track_name like '%feat%' OR track_name LIKE '%ft%';\
\
--Query the Average Energy from TopSongs\
SELECT ROUND(avg(energy),2) as average_energy FROM spotify_top50_2021;\
\
WITH average_energy AS (\
    SELECT ROUND(AVG(energy), 2) AS avg_energy \
    FROM spotify_top50_2021\
)\
SELECT track_name, artist_name, energy,\
CASE\
    WHEN energy > ae.avg_energy THEN 'above average'\
    WHEN energy = ae.avg_energy THEN 'average'\
    WHEN energy < ae.avg_energy THEN 'below average'\
END AS compare_energy \
FROM spotify_top50_2021, average_energy AS ae;\
\
\
SELECT ROUND(avg(loudness),2) as average_loud FROM spotify_top50_2021;\
\
WITH average_loud AS (\
    SELECT AVG(loudness) AS average_loud\
    FROM spotify_top50_2021\
)\
SELECT track_name, artist_name, loudness,\
CASE\
    WHEN loudness > ae.average_loud THEN 'above average'\
    WHEN loudness = ae.average_loud THEN 'average'\
    WHEN loudness < ae.average_loud THEN 'below average'\
END AS compare_loud\
FROM spotify_top50_2021, average_loud AS ae;\
\
\
---Valence\
\
SELECT ROUND(AVG(valence), 3) as avg_valence FROM spotify_top50_2021;\
\
WITH average_valence AS (\
    SELECT AVG(valence) AS avg_valence\
    FROM spotify_top50_2021\
)\
SELECT compare_valence, COUNT(compare_valence) AS count \
FROM (\
    SELECT track_name, artist_name, valence,\
    CASE\
        WHEN valence > av.avg_valence THEN 'above average'\
        WHEN valence = av.avg_valence THEN 'average'\
        WHEN valence < av.avg_valence THEN 'below average'\
    END AS compare_valence\
    FROM spotify_top50_2021, average_valence AS av\
) AS subquery\
GROUP BY compare_valence;}