5 ms·

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 3y ago
Oh cool, I'd like to hear what sort of queries you make.
- deleted 3y ago
  [deleted]
- deleted 3y ago
  [deleted]
- unzadunza 3y ago
  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 3y ago
  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 3y ago
    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.
- unzadunza 3y ago
  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 3y ago
    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...

jakear 3y ago

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

deleted 3y ago
[deleted]
deleted 3y ago
[deleted]
unzadunza 3y ago
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 3y ago
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 3y ago
  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.
unzadunza 3y ago
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 3y ago
  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...