Make Your Own Spotify Wrapped With SQL (Because Why Not?)

Everybody’s showing off their Spotify Wrapped on social media, but what if you could make your own and see everything Spotify leaves out? With a bit of SQL, you can dig into your listening habits, see your true top tracks, and even find out how much time you spent looping that guilty pleasure song. It’s easy, fun, and a great way to flex your SQL skills. In this article I will show you how.

You know how Spotify Wrapped drops every year, and everyone posts their top tracks, artists, and listening stats? It’s fun, but let’s be honest—sometimes you want more than just what Spotify decides to show you. Like, how much of your life did you waste listening to that one guilty-pleasure song? Or who really was your top artist if Spotify didn’t sneak in podcasts?

If you’ve ever wanted to nerd out with your own music data, I’ve got something cool for you: build your own Spotify Wrapped using SQL. It's not as complicated as it sounds. You’ll get full control over the insights, bragging rights, and a legit reason to practice SQL while making charts for fun.

Step 1: Download Your Spotify Data

Alright, the first thing you need to do is grab your listening data from Spotify. It's surprisingly easy—Spotify is legally required to give you access to all the personal data they’ve collected, and they’ve made the process pretty straightforward.

Here’s what you do:

• Go to the Spotify Privacy page. You’ll need to log in if you’re not already. This is where Spotify keeps all the tools for managing and accessing your data.
• Once you’re in, scroll to the section that says “Download Your Data”. Click on it, and Spotify will ask you to confirm the request. It’s not instant—Spotify takes a bit of time to pull everything together (1-2 days, in my experience). They’ll send you an email when the data is ready, so don’t keep refreshing your inbox like I did.
• When the email arrives, download the ZIP file they provide. Inside that ZIP, you’ll find several files, but the one we care about is StreamingHistory.json. This file is the goldmine—it has a detailed log of every track you’ve played, when you played it, and for how long. It’s basically your Spotify life in data form.

A heads-up: the first time I looked at the StreamingHistory.json file, I felt like I was hacking into my own music taste. It’s not pretty at first glance—just lines of text with timestamps, artist names, and track titles—but trust me, it’s super powerful once you put it to work with SQL.

So, grab that file and let’s start turning your listening habits into something way cooler than Spotify Wrapped.

Step 2: Prep Your Data For SQL

Here’s the thing—Spotify gives you your data as a JSON file. If you’re not familiar, JSON (JavaScript Object Notation) is just a way to store data in a text format that machines love, but it’s not exactly human-friendly. It works great for developers, but SQL needs your data in a table format—like a spreadsheet. So, the first step here is to convert StreamingHistory.json into a CSV file.

Don’t worry; this is easier than it sounds. You don’t need fancy tools or programming knowledge—Excel, Google Sheets, or even a basic online converter will do the trick. Here’s how:

If you’re using Excel or Google Sheets:

• Open Excel (or Sheets) and look for the option to import a file. In Excel, you’ll go to Data -> Get Data -> From File -> JSON. In Google Sheets, it’s a bit more manual, so you might need an online converter first.
• Upload your StreamingHistory.json file. Excel will parse the JSON and turn it into rows and columns.

If you want a quick online solution:

Use a tool like json-csv.com or similar. These websites let you upload your JSON file and convert it into a CSV in seconds. Download the resulting file and you’re good to go.

What’s in the CSV?

Once converted, your CSV file will look like a neat little spreadsheet with four key columns:

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

For example:

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

Think of this as your raw data—the backbone of your analysis. endTime lets you track exactly when you played a song, while msPlayed tells you how long you listened. Once you have the CSV file ready, you can load it straight into SQL and start querying your listening habits like a pro.

Step 3: Load Data into PostgreSQL (pgAdmin)

Now that your Spotify data is ready as a CSV file, it’s time to load it into PostgreSQL using pgAdmin. This is where we turn your raw listening history into a SQL-friendly table. Don’t worry, I’ll walk you through the entire process.

1. Create the Table in pgAdmin

First, you need a table to hold the data. Open pgAdmin and connect to your PostgreSQL database. Once connected:

1. Open a new query window (right-click your database -> Query Tool).
2. Run the following SQL script to create a table:

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

Click the Execute button (the lightning bolt icon), and your table will be created.

2. Import the CSV File into Your Table

Now let’s load your CSV file into the table you just created:

• In the left-hand menu of pgAdmin, expand your database -> Schemas -> Tables.
• Find your table (streaming_history) and right-click on it.
• Select Import/Export from the menu.

A new window will pop up. Here’s what to do:

• Filename: Browse to the location of your CSV file.
• Format: Select CSV.
• Delimiter: Set this to a comma (,).
• Header: Make sure this is checked if your CSV has column names in the first row.

Click OK to start the import process.

3. Verify the Data

To make sure everything loaded correctly, you can run a simple SQL query to check your table:

SELECT * FROM streaming_history LIMIT 10;
    

This will display the first 10 rows of your table. If you see your tracks, artist names, and playtimes neatly in columns, you’re good to go!

Step 4: Analyze Your Playlist With SQL Queries

You’ve got your data in PostgreSQL, and now it’s time to unleash the power of SQL. With just a few queries, you can pull out insights that even Spotify Wrapped doesn’t show. Grab a coffee (or throw on your favorite playlist), and let’s go.

Top 5 Most Played Tracks

Want to see which songs you absolutely overplayed? This query sums up the time you spent on each track and sorts them in descending order.

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

What It Does:

• Groups your data by track name and artist.
• Sums up the total playtime (in milliseconds) and converts it to minutes.
• Sorts the tracks by total minutes, showing the top 5.

You’ll finally see if that one song you swear you "only listened to once" is actually dominating your year.

Total Listening Time

Ever wonder how many hours of your life you spent listening to music this year? Here’s how to find out:

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

What It Does:

• Sums up the total time (milliseconds) you spent listening to tracks.
• Converts it into hours.

You might laugh, cry, or question your life choices when you see this number. Either way, it’s a fun stat to know.

Monthly Listening Trends

Want to see how your music habits changed over the months? This query breaks it down:

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

What It Does:

• Groups your listening data by month using DATE_TRUNC.
• Sums up the total playtime and converts it into hours.
• Sorts the data chronologically so you can see trends.

You’ll notice if you listen more during summer road trips, winter hibernation, or those stressful deadlines.

Step 5: Visualize Your Data In Power BI

Now that you’ve got your insights from SQL, it’s time to turn those results into eye-catching visuals with Power BI. Here’s how:

Connect Power BI to PostgreSQL

• Open Power BI.
• Go to Home -> Get Data -> PostgreSQL Database.
• Enter your server name and database credentials.
• Load the streaming_history table into Power BI.

Create Your Visuals Use these suggestions to bring your data to life:

• Top Tracks: Bar Chart
• X-axis: Track name
• Y-axis: Total minutes listened
• Top Artists: Pie Chart
• Show the proportion of time spent on each artist.
• Monthly Listening Trends: Line Chart
• X-axis: Months
• Y-axis: Total hours listened
• Total Listening Time: Card Visualization
• Show the total listening time in hours as a big, bold number.
• Style It Like Spotify
• Use dark themes with bright colors (greens, purples, yellows) for that Spotify vibe.
• Add titles like “Top 5 NuMetal Tracks” or “Monthly Listening Habits” to give it personality.

Your dashboard will look sleek, professional, and ready for sharing. Here are my top 10 tracks of 2024!

Step 6: Share And Flex

Once your Power BI dashboard is ready, you’ve earned the right to show it off. Here’s how:

• Export it: Save the dashboard as a PDF or image.
• Share it: Post it on social media, send it to friends, or print it out if you want to go old school.
• Brag a little: Let everyone know you’re not just listening to rock—you’re analyzing it like a pro.

Imagine dropping stats like “I spent 120 hours listening to Metallica this year” or “Duality by Slipknot was my #1 song with 15 plays.” Instant respect.

Final Thoughts

Building your own Spotify Wrapped isn’t just fun—it’s a mix of your love for music and some hands-on data tinkering. With SQL, you can uncover insights that Spotify doesn’t show, like which tracks you’ve played on repeat or how your music habits changed over the year.

In this article, you’ve learned how to:

• Use SQL to pull out your Top Tracks, Top Artists, and Listening Trends.
• Run simple but powerful queries like GROUP BY, SUM(), and DATE_TRUNC.
• Turn all that data into clean, Spotify-style visuals with Power BI.

Here’s the cool part: the SQL techniques you used—sorting data, grouping it, and calculating totals—are just the beginning. If you want to learn more and get really comfortable working with data, check out the SQL From A to Z track on LearnSQL.com. It’ll take you step by step through everything you need to know, and you’ll get to work with real-world data like this.

With SQL, you can analyze anything—your music, your work data, or even side projects. It’s a tool that keeps on giving.

So, grab your data, write a few more queries, and see just how much rock and metal powered your year. And hey, if you discover something wild—like playing “Espresso” 200 times or Ariana Grande being 80% of your playlist—We’ve all been there—no judgment. Now let’s see who really ruled your playlist!