[unzadunza]

Neat, l learned something new. Now it’s time to query the tsv with the other new thing I learned about last week via hacker news: duckdb.

[jakear]

Oh cool, I'd like to hear what sort of queries you make.

[unzadunza]

  select artist, sum(plays) from plays where last_played > '2023-01.01' group by artist order by sum(plays) desc limit 20;
  ┌───────────────────────────────┬────────────┐
  │            artist             │ sum(plays) │
  │            varchar            │   int128   │
  ├───────────────────────────────┼────────────┤
  │ Mush                          │         97 │
  │ Waylon Jennings               │         83 │
  │ The Klittens                  │         82 │
  │ Widowspeak                    │         80 │
  │ Glyders                       │         71 │
  │ Big Thief                     │         69 │
  │ Duke Ellington                │         66 │
  │ Wet Leg                       │         59 │
  │ Bachelor, Jay Som & Palehound │         54 │
  │ Spencer Cullum                │         51 │
  │ Thee Oh Sees                  │         51 │
  │ abracadabra                   │         49 │
  │ The Beatles                   │         48 │
  │ PACKS                         │         44 │
  │ Pozi                          │         43 │
  │ Miles Davis                   │         38 │
  │ Sorry                         │         37 │
  │ Thievery Corporation          │         34 │
  │ Daniel Rossen                 │         32 │
  │ Melin Melyn                   │         31 │
  ├───────────────────────────────┴────────────┤
  │ 20 rows                          2 columns │
  └────────────────────────────────────────────┘

It's interesting to add 'last played' to the smart playlist.

[jakear]

Yeah last played is a good one. I actually have a smart list of all the top played songs that were last played over a year or two a go, which I look through to find some "fell through the cracks" things I enjoyed at one point.

What I most want is a way to count the plays that happened between a set of dates...

[unzadunza]

  select artist, sum(plays) from plays group by artist order by sum(plays) desc limit 40;
  ┌───────────────────────────────┬────────────┐
  │            artist             │ sum(plays) │
  │            varchar            │   int128   │
  ├───────────────────────────────┼────────────┤
  │ The Beatles                   │       2402 │
  │ Bob Dylan                     │        683 │
  │ Pink Floyd                    │        595 │
  │ Miles Davis                   │        580 │
  │ Duke Ellington                │        443 │
  │ Kenny Burrell                 │        339 │
  │ Vince Guaraldi                │        311 │
  │ Nightmares On Wax             │        297 │
  │ Rodriguez                     │        269 │
  │ Vince Guaraldi Trio           │        269 │
  │ John Lennon                   │        268 │
  │ Creedence Clearwater Revival  │        258 │
  │ Sonny Rollins                 │        223 │
  │ Bill Evans                    │        212 │
  │ Burning Spear                 │        210 │
  │ De La Soul                    │        201 │
  │                               │        201 │
  │ Radiohead                     │        194 │
  │ A Tribe Called Quest          │        190 │
  │ Led Zeppelin                  │        189 │
  │ Brad Mehldau                  │        188 │
  │ Martin Denny                  │        188 │
  │ Bob Marley                    │        178 │
  │ Paul Desmond & Gerry Mulligan │        161 │
  │ Charles Mingus                │        158 │
  │ Waylon Jennings               │        157 │
  │ Levi Clay                     │        149 │
  │ Marvin Gaye                   │        144 │
  │ Digable Planets               │        142 │
  │ Grateful Dead                 │        142 │
  │ John Coltrane                 │        137 │
  │ The Smiths                    │        136 │
  │ George Harrison               │        132 │
  │ Dimitri From Paris            │        124 │
  │ Dave Brubeck Quartet          │        121 │
  │ Cannonball Adderley           │        117 │
  │ Thievery Corporation          │        115 │
  │ Tommy Flanagan                │        112 │
  │ The B-52's                    │        109 │
  │ Royal Philharmonic Orchestra  │        109 │
  ├───────────────────────────────┴────────────┤
  │ 40 rows                          2 columns │
  └────────────────────────────────────────────┘

Guess I'm a Beatles fan!

[unzadunza]

  select genre, sum(plays) from plays group by genre order by sum(plays) desc limit 40;
  ┌─────────────────────┬────────────┐
  │        genre        │ sum(plays) │
  │       varchar       │   int128   │
  ├─────────────────────┼────────────┤
  │ Jazz                │       5754 │
  │ Rock                │       5149 │
  │ Alternative         │       1511 │
  │ Hip Hop/Rap         │        986 │
  │ Pop                 │        811 │
  │ Alternative & Punk  │        725 │
  │ Electronic          │        713 │
  │ Reggae              │        670 │
  │ Country             │        612 │
  │ Holiday             │        581 │
  │ Folk                │        553 │
  │ Classical           │        433 │
  │ R&B                 │        359 │
  │ World               │        334 │
  │                     │        303 │
  │ Electronica/Dance   │        302 │
  │ Singer/Songwriter   │        275 │
  │ R&B/Soul            │        242 │
  │ Metal               │        119 │
  │ Psychedelic         │        111 │
  │ Blues               │        110 │
  │ Contemporary Folk   │         74 │
  │ Indie Rock          │         65 │
  │ Fusion              │         58 │
  │ Soundtrack          │         47 │
  │ Indie Pop           │         40 │
  │ Easy Listening      │         34 │
  │ Dance               │         20 │
  │ Alternative Folk    │         20 │
  │ Punk                │         20 │
  │ rock                │         20 │
  │ Downtempo           │         18 │
  │ Electronica         │         18 │
  │ Contemporary Jazz   │         13 │
  │ Hip-Hop/Rap         │         10 │
  │ Indian Classical    │          9 │
  │ Traditional Country │          7 │
  │ New Age             │          7 │
  │ Alternative Country │          6 │
  │ Opera               │          5 │
  ├─────────────────────┴────────────┤
  │ 40 rows                2 columns │
  └──────────────────────────────────┘

I don't know if I trust Apple Music's accounting, but I'm a jazz fan too.

[jakear]

Neat. I note both of these queries could be done via my UI easily, but sharing the results would be impossible without sharing your entire history too.