duckdb_queries.md (2712B)
1 - `listened_at`: BIGINT (Unix timestamp) 2 - `track_name`: VARCHAR 3 - `artist_name`: VARCHAR 4 - `release_name`: VARCHAR 5 - `release_mbid`: VARCHAR 6 - `caa_release_mbid`: VARCHAR 7 8 ## General Statistics 9 10 ### Total number of scrobbles 11 ```sql 12 SELECT count(*) FROM scrobbles; 13 ``` 14 15 ### Artist diversity (Total unique artists) 16 ```sql 17 SELECT count(DISTINCT artist_name) FROM scrobbles; 18 ``` 19 20 ## Top Lists 21 22 ### Top 10 Artists 23 ```sql 24 SELECT artist_name, count(*) as play_count 25 FROM scrobbles 26 GROUP BY artist_name 27 ORDER BY play_count DESC 28 LIMIT 10; 29 ``` 30 31 ### Top 10 Tracks 32 ```sql 33 SELECT artist_name, track_name, count(*) as play_count 34 FROM scrobbles 35 GROUP BY artist_name, track_name 36 ORDER BY play_count DESC 37 LIMIT 10; 38 ``` 39 40 ### Top 10 Albums 41 ```sql 42 SELECT artist_name, release_name, count(*) as play_count 43 FROM scrobbles 44 WHERE release_name IS NOT NULL AND release_name != '' 45 GROUP BY artist_name, release_name 46 ORDER BY play_count DESC 47 LIMIT 10; 48 ``` 49 50 ### Top 10 Labels 51 ```sql 52 SELECT rm.label, count(*) as play_count 53 FROM scrobbles s 54 JOIN release_metadata rm ON s.release_mbid = rm.release_mbid 55 WHERE rm.label IS NOT NULL AND rm.label != '' 56 GROUP BY rm.label 57 ORDER BY play_count DESC 58 LIMIT 10; 59 ``` 60 61 ## Time-based Analysis 62 63 ### Scrobbles per day (Last 30 days) 64 ```sql 65 SELECT 66 to_timestamp(listened_at)::DATE as date, 67 count(*) as count 68 FROM scrobbles 69 WHERE to_timestamp(listened_at) > now() - INTERVAL '30 days' 70 GROUP BY date 71 ORDER BY date DESC; 72 ``` 73 74 ### Scrobbles per month 75 ```sql 76 SELECT 77 date_trunc('month', to_timestamp(listened_at)) as month, 78 count(*) as count 79 FROM scrobbles 80 GROUP BY month 81 ORDER BY month DESC; 82 ``` 83 84 ### Listening activity by hour of day 85 ```sql 86 SELECT 87 extract('hour' from to_timestamp(listened_at)) as hour, 88 count(*) as count 89 FROM scrobbles 90 GROUP BY hour 91 ORDER BY hour; 92 ``` 93 94 ### Most active day of the week 95 ```sql 96 SELECT 97 dayname(to_timestamp(listened_at)) as day, 98 count(*) as count 99 FROM scrobbles 100 GROUP BY day, dayofweek(to_timestamp(listened_at)) 101 ORDER BY dayofweek(to_timestamp(listened_at)); 102 ``` 103 104 ## Maintenance & Integrity 105 106 ### Find duplicate scrobbles (Same artist, track, and timestamp) 107 ```sql 108 SELECT listened_at, artist_name, track_name, count(*) 109 FROM scrobbles 110 GROUP BY listened_at, artist_name, track_name 111 HAVING count(*) > 1; 112 ``` 113 114 ### Recent scrobbles with human-readable timestamps 115 ```sql 116 SELECT 117 to_timestamp(listened_at) as time, 118 artist_name, 119 track_name 120 FROM scrobbles 121 ORDER BY listened_at DESC 122 LIMIT 20; 123 ``` 124 125 ### Check MBID coverage 126 ```sql 127 SELECT 128 count(*) as total, 129 count(release_mbid) FILTER (WHERE release_mbid != '') as with_mbid, 130 (count(release_mbid) FILTER (WHERE release_mbid != '')::FLOAT / count(*)) * 100 as percentage 131 FROM scrobbles; 132 ```