:::: MENU ::::
Browsing posts in: Data analytics

Using SQL to improve your music library

I’m obsessed with my music collection. This is not simply a love a music – I love the collecting and organizing. I still choose to own rather than stream my music so I can retain the ability to tag, rate, and track my listening habits.

My obsesses likely stems from need to apply data analytics to everything. With my media library, I can dive into nerdy details about which artists are rising in the ranks and what great songs have been forgotten. With more than 75,000 songs, I need some heavy lifting to keep things neat and tidy.

This is where some data analytics comes in. I use MediaMonkey to organize my music, which supports a growing supply of powerful plug-ins. Using the Magic Nodes plug-in, I can apply complex SQL filters to help filter my music library far more than an iTunes auto playlist (though MediaMonkey still supports those).

I wanted to share some of my favorite Magic Nodes and playlists (some I wrote and some written by the Magic Nodes community). (Note: Magic Nodes SQL queries only require the WHERE statement, so the SELECT part is set by the interface). MediaMonkey shares its database structure on its wiki.

Find half played complete albums

Too often, I listen to part of an album, then forget to return. Thankfully, Magic Nodes some with this filter out of the box, checking for complete albums that only had half their tracks played.

magicnode-halfplayedalbums

<Group|Name:Playing statistics|Show tracks:No>\Complete albums where at least half of the tracks are not played|Icon:Top level|Filter:Songs.IDAlbum IN (SELECT IDAlbum FROM Songs GROUP BY IDAlbum HAVING Count(TrackNumber) = Max(Cast(TrackNumber As integer)) AND Count(TrackNumber) > 3 AND Sum(CASE WHEN PlayCounter > 0 THEN 1 ELSE 0 END) < Count(*) / 2)|Child of:Library|MM filter:Nodes|Position:Last child\<Genre|Statistic:Count(Album), Count(Tracks)>\<Album artist with album>

Find songs in multiple playlists

I try to keep songs I love on a variety of mood-based playlists (like for writing, playing video games, napping, cooking, etc.). I keep all of these playlists nested under a parent mood folder. To ensure I’m cycling new music through, this Magic Node checks when songs are used in more than 4 playlists.

magicnode-playlists

<Group|Name:Playlists…|Show tracks:No>\Songs in 4 or more Mood Playlists|Icon:Top level|Filter:Songs.ID IN (SELECT ps.IDSong FROM PlaylistSongs ps INNER JOIN Playlists p ON ps.IDPlaylist = p.IDPlaylist WHERE p.ParentPlaylist = 869 GROUP BY ps.IDSong HAVING Count(*) > 3)|Position:Last child|Child of:Library|MM filter:Nodes\<Genre|Statistic:Count(Items)>\<Artist|Statistic:Count(Items)>\<Title|Statistic:Count(Playlist)>\<Playlist|Statistic:Count(All)|Unknown:No>

Artists that have never been played

This default node helps find artists I’ve downloaded and never actually listened to. Often, these will be from soundtracks or compilation albums, but sometimes there are exciting new artists I’ve discovered, then forgot I once discovered them. This lists helps me find the hidden gems within my lbirary

<Group|Name:Playing statistics|Show tracks:No>\Artists that never played|Icon:Top level|Filter:Songs.ID = AArtistsSongs.IDSong AND (AArtistsSongs.PersonType = 1 OR AArtistsSongs.PersonType IS NULL) AND AArtistsSongs.IDArtist IN (SELECT ArtistsSongs.IDArtist FROM Songs, ArtistsSongs WHERE Songs.ID = ArtistsSongs.IDSong AND (ArtistsSongs.PersonType = 1 OR ArtistsSongs.PersonType IS NULL) GROUP BY ArtistsSongs.IDArtist HAVING Sum(PlayCounter) = 0)|Child of:Library|MM filter:Nodes|Position:Last child\<Genre|Statistic:Count(Items)>\<Artist|Statistic:Count(Tracks)>

First time played

Feeding into my nerdy obsession, this list isn’t so much as useful as it is informative. MediaMonkey (and other music libraries) keep track of every individual play date of a song, but they don’t share this information in highly visible ways. Thankfully, with a SQL query, we can uncover when a song was first played. I even organize it by the year so I can see which years I’m listening to more new music.

<Group|Name:Playing statistics>\First date played of each song|Child of:Library|MM filter:Nodes|Position:Last child|Filter:Songs.ID IN (SELECT Songs.ID FROM Songs JOIN Played ON Songs.ID = Played.IDSong GROUP BY Songs.ID HAVING Played.PlayDate = MIN(Played.PlayDate))\<Year played|Statistic:Count(Tracks)>\<Genre|Statistic:Count(Items)|All:Yes>\<Album|Statistic:Count(Items)>\<Title with artist|Statistic:Min(Date played)|Sort by:Min(Date played)>

Played 5 times within 30 days

I can get a small set of songs in my head, so this node becomes super helpful to quickly access those songs I want on constant repeat. Often, I’ll even use this list to inform a weekly highlights static playlist, with songs I love putting on repeat.

<Group|Name:Playing statistics|Show tracks:No>\Played at least 5 times within last 30 days|Filter:Songs.ID IN (SELECT Songs.ID FROM Songs LEFT JOIN Played ON Songs.ID = Played.IDSong WHERE CAST(julianday(date(‘now’, ‘localtime’)) – julianday(date(Played.PlayDate + julianday(‘1899-12-30’))) AS integer) <= 210 GROUP BY Songs.ID HAVING Count(*) >= 5)|Icon:Top level|Child of:Library|MM filter:Nodes|Position:Last child\<Title with artist>

 

This is just a sample of my favorite nodes Magic Nodes comes with more than 100 for your organizing needs. Also, check out the SQL-Viewer plug-in, which is a built in SQL tool to help test out any other nodes you’d like to create (remember to add back in your SELECT statement).