Learning SQL with your own Spotify Streaming History data

It’s 2023 and you’ve decided you want to beef up your resume with a tech skill that’s proven and lucrative. You’ve decided, NOW is the time to learn SQL. I want to show you how to make this more enjoyable by using your own data.

But wait, why not use the time honored classic examples such as EMPLOYEES and DEPARTMENTS?

It’s much better to learn with data YOU understand

It’s very easy to write a valid SQL statement and get data back. And you will be tempted to think, ‘got it!’ But, it’s also VERY easy to write a SQL statement that’s not really asking what you THINK it’s asking.

For THIS reason, I recommend you try to learn with data that you PERSONALLY have experience with. Your gut will know when the answers you’re looking at, don’t make sense or feel right.

Not a Spotify subscriber?

That’s OK! Your ITUNES library can be exported as CSV. You can download your personal data from Twitter or Strava. Find something that has meaning to YOU, and use that raw data. Hopefully it’s in either a JSON or CSV format.

Step 0: Obtain or Create a Database

I suggest an Always Free Oracle Database for the following reasons:

• it’s free
• Oracle takes care of it for you
• you can have a working database in several minutes
• you can do most of everything you need without having to install any software
• I work for Oracle (surprise!)

No, it’s really free. Always.

Watch this video tutorial from one of our biggest advocates, Layla. She makes it look easy, but it’s also pretty easy.

Go here to get started with Oracle Cloud Infrastructure (OCI) and create your first database.

Step 1: Request your Spotify data, wait, & download

If you have an account, go here. Scroll to the bottom, and click the ‘Request Data’ button. Acknowledge, respond to the email confirmation, and wait about a week.

You’ll get a notification that it’s available.

Download it, save to your machine.

Find the ‘StreamingHistory0.json’ file in the MyData directory of that archive/ZIP download.

Step 2: Login to Database Actions/SQL Developer Web

Your Always Free Database account in the Oracle Cloud comes with a console. Layla showed you this, but if you go to cloud.oracle.com, and move to the Autonomous Database page, you should see something like this –

You should end up in a screen that looks like this –

Step 3a: Import the Spotify data!

That JSON file I asked you to find? You’re going to need it now.

That’s going to prompt you for a file. Point to that JSON file.

Then you’re going to get a screen that looks like this –

If you don’t see your data here, something is wrong, like you picked the wrong file or your data is something we can’t read or recognize.

It should be good though, the Spotify JSON format/schema is SUPER simple. So keeping in mind this is working, click ‘Next.’

Step 3b: DESIGN your new table

It needs a name! By default it will use the name of the file to name the table, but change it to something that means something to you.

We’re also going to make some changes to the COLUMN definitions.

The default will work, but they could be better.

{
    "endTime" : "2022-01-16 01:09",
    "artistName" : "Megadeth",
    "trackName" : "A Tout Le Monde - Remastered 2004",
    "msPlayed" : 262133
},

Our data is pretty simple. That first JSON attribute is called “endTime”, and it’s value is a date and time. We call this a TEMPORAL type, and in Oracle it’s always best to use TEMPORAL data types to store this kind of data.

So I’m going to choose change this from ‘VARCHAR2’ to ‘DATE’

Tip: Use good object names, column names, and column data types! If you don’t, your life will be that much harder EVERY SINGLE TIME you go to write your SQL.

That’s the bare minimum we need to do. You can keep clicking through the dialog until you get to the end and click ‘Finish.’

Step 4: Write your first SQL statement!

You can now type and run a SQL statement to interact with your first table. Now, I could teach you how to write a SQL SELECT, or I could cheat and let the tool do it for you.

So let’s cheat.

Grab the table on the left, and drag it into the editor.

It will ask what kind of SQL you want.

You’ll now see your SQL, ready to be executed.

Step 5: Execute your first SQL statement!

Just click the first green play button. You can also use your keyboard, on Windows simply do ‘ctrl+enter’ or if you’re on a Mac, use ‘cmd+enter’ to execute your SQL.

SELECT
    PLAYED,
    ARTISTNAME,
    TRACKNAME,
    DURATION
FROM
    ADMIN.SPOTIFY_STREAMS_2022;

Breaking down the SQL

SELECT PLAYED, ARTISTNAME, TRACKNAME, DURATION

These are the columns I want to come back from my query.

FROM ADMIN.SPOTIFY_STREAMS_2022

This is the SCHEMA and TABLE from which the columns (and the data) can be found. In Oracle, SCHEMA is synonymous with ‘USER’ – so our user account here is ‘ADMIN’ and the tablename is the SPOTIFY_STREAMS_2022.

You could get lazy and simply run this instead and get the same results –

SELECT *
  FROM SPOTIFY_STREAMS_2022;

The asterisk, is often referred to as ‘star’ as in ‘SELECT STAR FROM’ – it’s basically shorthand for ALL of the columns in a table or view.

We’re logged in as the ADMIN user, so we don’t have to include it in the FROM clause.

Browsing our data

I can scroll down the list of songs until you get bored, or you can ask the tool to tell you how many records are in your table. To do the latter, right-click in the grid, and select ‘Count Rows…’

My table has more than 7,000 records in it, yours will be however many songs you listened to over the past year.

If we right click again, we can also choose to download the data back out as…JSON or CSV.

Summary

We grabbed our personal data from Spotify. Well, we requested it and waited. And then while we were waiting we went and created ourselves an Always Free Oracle Cloud Autonomous Database.

Then we loaded our JSON data to a new able.

And we ended things by asking the web tool to write a SQL SELECT statement for us, and we ran it!

If you liked this post, and playing with your Spotify data, I’ve got one more for you! We’ll show how to do some sorting, add functions to our SELECTs, and do a GROUP BY!

Part II: Answering some questions about our data with SQL

Looking for some good resources for learning SQL? See Chris post over on Follow the Coffee!