Creare il proprio Spotify con SQL (perché no?)

Tutti mostrano il proprio Spotify Wrapped sui social media, ma cosa succederebbe se si potesse creare il proprio e vedere tutto ciò che Spotify tralascia? Con un po' di SQL, potete scavare nelle vostre abitudini di ascolto, vedere i vostri veri brani top e persino scoprire quanto tempo avete trascorso in loop con quella canzone che vi piace tanto. È facile, divertente e un ottimo modo per mettere alla prova le vostre abilità in SQL. In questo articolo vi mostrerò come fare.

Avete presente quando ogni anno esce Spotify Wrapped e tutti pubblicano i loro brani migliori, gli artisti e le statistiche di ascolto? È divertente, ma siamo onesti: a volte si vuole qualcosa di più di ciò che Spotify decide di mostrare. Ad esempio, quanto tempo della vostra vita avete sprecato ascoltando quell'unica canzone che vi piaceva? O chi è stato davvero il vostro artista di punta se Spotify non ha inserito di nascosto i podcast?

Se avete sempre desiderato fare i nerd con i vostri dati musicali, ho qualcosa di interessante per voi: costruire il vostro Spotify Wrapped usando SQL. Non è così complicato come sembra. Avrete il pieno controllo delle informazioni, il diritto di vantarvi e un motivo legittimo per esercitarvi con l'SQL e creare grafici per divertimento.

Passo 1: scaricare i dati di Spotify

La prima cosa da fare è recuperare i dati di ascolto da Spotify. È sorprendentemente facile: Spotify è tenuta per legge a darvi accesso a tutti i dati personali raccolti e ha reso il processo piuttosto semplice.

Ecco cosa fare:

• Andate alla pagina della privacy di Spotify. Se non lo siete già, dovrete effettuare il login. Qui Spotify conserva tutti gli strumenti per la gestione e l'accesso ai vostri dati.
• Una volta entrati, scorrete fino alla sezione "Scarica i tuoi dati". Cliccateci sopra e Spotify vi chiederà di confermare la richiesta. Non è immediato: Spotify impiega un po' di tempo per mettere insieme tutto (1-2 giorni, secondo la mia esperienza). Vi invierà un'e-mail quando i dati saranno pronti, quindi non continuate ad aggiornare la casella di posta come ho fatto io.
• Quando arriva l'e-mail, scaricate il file ZIP fornito. All'interno di questo ZIP, troverete diversi file, ma quello che ci interessa è StreamingHistory.json. Questo file è una miniera d'oro: contiene un registro dettagliato di tutti i brani ascoltati, quando e per quanto tempo. In pratica è la vostra vita su Spotify sotto forma di dati.

Attenzione: la prima volta che ho guardato il file StreamingHistory.json mi è sembrato di entrare nei miei gusti musicali. Non è bello a prima vista - solo righe di testo con timestamp, nomi di artisti e titoli di brani - ma credetemi, è molto potente una volta che lo si mette al lavoro con SQL.

Quindi, prendete quel file e iniziate a trasformare le vostre abitudini di ascolto in qualcosa di molto più bello di Spotify Wrapped.

Fase 2: Preparare i dati per l'SQL

Ecco il punto: Spotify fornisce i dati in un file JSON. Per chi non lo sapesse, JSON (JavaScript Object Notation) è un modo per memorizzare i dati in un formato di testo che piace molto alle macchine, ma non è esattamente a misura d'uomo. Funziona benissimo per gli sviluppatori, ma SQL ha bisogno di dati in formato tabellare, come un foglio di calcolo. Quindi, il primo passo è quello di convertire StreamingHistory.json in un file CSV.

Non preoccupatevi, è più facile di quanto sembri. Non sono necessari strumenti sofisticati o conoscenze di programmazione: Excel, Google Sheets o anche un convertitore online di base sono sufficienti. Ecco come fare:

Se si utilizza Excel o Google Sheets:

• Aprite Excel (o Fogli) e cercate l'opzione per importare un file. In Excel, si va su Dati -> Ottieni dati -> Da file -> JSON. In Google Sheets, l'operazione è un po' più manuale, quindi potrebbe essere necessario un convertitore online.
• Caricare il file StreamingHistory.json. Excel analizzerà il JSON e lo trasformerà in righe e colonne.

Se si desidera una soluzione online rapida:

Utilizzare uno strumento come json-csv.com o simili. Questi siti web consentono di caricare il file JSON e di convertirlo in CSV in pochi secondi. Scaricate il file ottenuto e siete pronti a partire.

Cosa c'è nel CSV?

Una volta convertito, il file CSV avrà l'aspetto di un piccolo foglio di calcolo ordinato con quattro colonne chiave:

Column Name	Description
endTime	When you listened to the track (timestamp).
artistName	The name of the artist.
trackName	The title of the song you listened to.
msPlayed	How long you played the song (in milliseconds).

Per esempio:

endTime	artistName	trackName	msPlayed
2024-06-05 12:00	Metallica	Enter Sandman	325000
2024-06-05 12:05	Korn	Freak on a Leash	244000

endTime consente di tenere traccia esattamente del momento in cui si è ascoltato un brano, mentre msPlayed indica la durata dell'ascolto. Una volta pronto il file CSV, potete caricarlo direttamente in SQL e iniziare a interrogare le vostre abitudini di ascolto come un professionista.

Passo 3: caricare i dati in PostgreSQL (pgAdmin)

Ora che i dati di Spotify sono pronti come file CSV, è il momento di caricarli in PostgreSQL utilizzando pgAdmin. È qui che trasformiamo la cronologia degli ascolti in una tabella SQL. Non preoccupatevi, vi guiderò attraverso l'intero processo.

1. Creare la tabella in pgAdmin

Per prima cosa, è necessario creare una tabella che contenga i dati. Aprite pgAdmin e collegatevi al vostro database PostgreSQL. Una volta connessi:

1. Aprire una nuova finestra di query(fare clic con il tasto destro del mouse sul database -> Strumento di query).
2. Eseguire il seguente script SQL per creare una tabella:

CREATE TABLE streaming_history (
    endTime TIMESTAMP,
    artistName VARCHAR(255),
    trackName VARCHAR(255),
    msPlayed INT
);
    

Fare clic sul pulsante Esegui (l'icona del fulmine) e la tabella verrà creata.

2. Importare il file CSV nella tabella

Ora carichiamo il file CSV nella tabella appena creata:

• Nel menu di sinistra di pgAdmin, espandere il database -> Schemi -> Tabelle.
• Individuare la tabella (streaming_history) e fare clic con il pulsante destro del mouse su di essa.
• Selezionare Importazione/Esportazione dal menu.

Si aprirà una nuova finestra. Ecco cosa fare:

• Nome del file: selezionare la posizione del file CSV.
• Formato: Selezionare CSV.
• Delimitatore: Impostate una virgola (,).
• Intestazione: Assicurarsi che sia selezionata se il CSV ha nomi di colonne nella prima riga.

Fare clic su OK per avviare il processo di importazione.

3. Verifica dei dati

Per verificare che tutto sia stato caricato correttamente, è possibile eseguire una semplice query SQL per controllare la tabella:

SELECT * FROM streaming_history LIMIT 10;
    

In questo modo verranno visualizzate le prime 10 righe della tabella. Se i brani, i nomi degli artisti e i tempi di riproduzione sono ordinati in colonne, il gioco è fatto!

Passo 4: analizzare la playlist con le query SQL

I dati sono stati inseriti in PostgreSQL e ora è il momento di scatenare la potenza di SQL. Con poche query, è possibile ottenere informazioni che nemmeno Spotify Wrapped mostra. Prendete un caffè (o mettete su la vostra playlist preferita) e partite.

I 5 brani più ascoltati

Volete sapere quali brani avete ascoltato in modo eccessivo? Questa query riassume il tempo trascorso su ogni brano e lo ordina in ordine decrescente.

SELECT trackName, artistName, SUM(msPlayed) / 60000 AS totalMinutes
FROM streaming_history
GROUP BY trackName, artistName
ORDER BY totalMinutes DESC
LIMIT 5;

Cosa fa:

• Raggruppa i dati per nome del brano e artista.
• Somma il tempo totale di riproduzione (in millisecondi) e lo converte in minuti.
• Ordina i brani in base ai minuti totali, mostrando i 5 migliori.

Potrete finalmente vedere se quella canzone che giurate di aver ascoltato "solo una volta" sta effettivamente dominando il vostro anno.

Tempo totale di ascolto

Vi siete mai chiesti quante ore della vostra vita avete trascorso ascoltando musica quest'anno? Ecco come scoprirlo:

SELECT SUM(msPlayed) / 3600000 AS totalHours
FROM streaming_history;

Cosa fa:

• Riassume il tempo totale (millisecondi) trascorso ad ascoltare i brani.
• Lo converte in ore.

Potreste ridere, piangere o mettere in discussione le vostre scelte di vita quando vedrete questo numero. In ogni caso, è una statistica divertente da conoscere.

Tendenze di ascolto mensili

Volete vedere come sono cambiate le vostre abitudini musicali nel corso dei mesi? Questa ricerca lo spiega:

SELECT DATE_TRUNC('month', endTime) AS month, SUM(msPlayed) / 3600000 AS hours
FROM streaming_history
GROUP BY month
ORDER BY month;

Cosa fa:

• Raggruppa i dati di ascolto per mese utilizzando DATE_TRUNC.
• Somma il tempo di riproduzione totale e lo converte in ore.
• Ordina i dati in ordine cronologico in modo da poter vedere le tendenze.

Si noterà se si ascolta di più durante i viaggi estivi, il letargo invernale o le scadenze stressanti.

Fase 5: visualizzare i dati in Power BI

Ora che avete ottenuto le vostre intuizioni da SQL, è il momento di trasformare i risultati in immagini accattivanti con Power BI. Ecco come fare:

Collegare Power BI a PostgreSQL

• Aprite Power BI.
• Andate su Home -> Ottieni dati -> Database PostgreSQL.
• Inserire il nome del server e le credenziali del database.
• Caricare la tabella streaming_history in Power BI.

Creare le immagini Utilizzate questi suggerimenti per dare vita ai vostri dati:

• Tracce superiori: Grafico a barre
• Asse X: Nome della traccia
• Asse Y: Minuti totali ascoltati
• Artisti di punta: Grafico a torta
• Mostra la percentuale di tempo dedicata a ciascun artista.
• Tendenze di ascolto mensili: Grafico a linee
• Asse X: Mesi
• Asse Y: Ore totali ascoltate
• Tempo totale di ascolto: visualizzazione della carta
• Mostra il tempo totale di ascolto in ore come un numero grande e in grassetto.
• Stile come Spotify
• Utilizzate temi scuri con colori vivaci (verde, viola, giallo) per creare un'atmosfera da Spotify.
• Aggiungete titoli come "Top 5 NuMetal Tracks" o "Monthly Listening Habits" per dare personalità.

La vostra dashboard avrà un aspetto elegante, professionale e pronto per essere condiviso. Ecco i miei 10 brani migliori del 2024!

Fase 6: Condividere e modificare

Una volta che il vostro dashboard Power BI è pronto, vi siete guadagnati il diritto di mostrarlo. Ecco come fare:

• Esportatelo: Salvate il dashboard come PDF o immagine.
• Condividerlo: Pubblicatela sui social media, inviatela agli amici o stampatela se volete fare la vecchia scuola.
• Vantatevi un po': Fate sapere a tutti che non state solo ascoltando il rock, ma che lo state analizzando come un professionista.

Immaginatevi di rilasciare statistiche come "Quest'anno ho passato 120 ore ad ascoltare i Metallica" o "Duality degli Slipknot è stata la mia canzone numero 1 con 15 ascolti".Rispetto immediato.

Pensieri finali

Costruire il proprio Spotify Wrapped non è solo un divertimento: è un mix di amore per la musica e di lavoro pratico sui dati. Con l'SQL è possibile scoprire informazioni che Spotify non mostra, come ad esempio i brani ripetuti o come sono cambiate le vostre abitudini musicali nel corso dell'anno.

In questo articolo abbiamo imparato come:

• Usare SQL per individuare i brani più ascoltati, gli artisti migliori e le tendenze di ascolto.
• Eseguire query semplici ma potenti come GROUP BY, SUM() e DATE_TRUNC.
• Trasformare tutti questi dati in immagini pulite in stile Spotify con Power BI.

La parte più interessante è che le tecniche SQL utilizzate - ordinare i dati, raggrupparli e calcolare i totali - sono solo l'inizio. Se volete saperne di più e lavorare con i dati in modo davvero confortevole, date un'occhiata alla traccia SQL dalla A alla Z su LearnSQL.it. Vi accompagnerà passo dopo passo in tutto ciò che dovete sapere e potrete lavorare con dati reali come questi.

Con SQL potete analizzare qualsiasi cosa: la vostra musica, i dati del vostro lavoro o persino i vostri progetti secondari. È uno strumento che continua a dare.

Quindi, prendete i vostri dati, scrivete qualche altra query e scoprite quanto rock e metal hanno alimentato il vostro anno. E se scoprite qualcosa di strano, come suonare "Espresso" 200 volte o Ariana Grande all'80% della vostra playlist, ci siamo passati tutti, non giudicate. Ora vediamo chi ha davvero dominato la vostra playlist!