My son got an Amazon Echo for Christmas. We use the Echo mostly to play music. I have setup IFTTT (If This Then That) to save the name of any song we play in a Google Sheet.
Between December 26 and January 1st we played a little over 1000 songs. Most of the time I would just say something like "Alexa, play 80s music" or "Alexa, play 70s music" this is why you might see songs from the same period played in a row
It is no coincidence that a lot of George Michael songs were played, he died on Christmas day. The most played song was requested by my youngest son Nicholas, he loves Demons by Imagine Dragons
I decided to import the Alexa data into SQL Server and run some queries. If you want to follow along, you can get the file here from GitHub: Songs played by Alexa
I exported the Google Sheet to a tab delimited file, I saved this file on my C drive, I created a table and did a BULK INSERT to populate this table with the data from this file
The date in the file is not a format that can be converted automatically, it looks like this December 26, 2016 at 09:53AM
I decided to add a date column and then convert that value with T-SQL. I did this by using the REPLACE function and replacing ' at ' with ' '
Now that this is all done, we can run some queries
What is the artist which we played the most?
|Stone Temple Pilots||17|
|Simon & Garfunkel||13|
As you can see that is George Michael
How about if we want to know how many unique songs we played by artist?
|Stone Temple Pilots||16|
|Simon & Garfunkel||12|
|Harry Connick Jr.||9|
In this case Nirvana wins
How about the 10 most played songs? To answer that question and grab ties, we can use WITH TIES
Here are the results
|Jason Mraz||I'm Yours||9|
|Pearl Jam||Yellow Ledbetter||6|
|Josh Groban||When You Say You Love Me||5|
|House Of Pain||Jump Around [Explicit]||4|
|Meghan Trainor||Lips Are Movin||4|
|Imagine Dragons||Round And Round||4|
|Nirvana||Smells Like Teen Spirit||4|
|Sir Mix-A-Lot||Baby Got Back [Explicit]||4|
|George Michael||Careless Whisper||4|
|George Michael||Faith (Remastered)||4|
|George Michael||Father Figure||4|
|George Michael||Freedom! '90||4|
So what other interesting queries can you come up with? How about how many Christmas related songs were there? Would the query look something like this?
Maybe you would want to know how many songs you played per day?
Or maybe you want to know how many songs with the same title were sung by more than 1 artist?
Is this what the query would look like?
If you want the song as well as the artist, you can use a windowing function with DENSE_RANK
That is all for this post, I will keep collecting this data till next Christmas and hopefully will be able to run some more interesting queries