Better homepage box

[neilmenon]

Add ticker stats

• Can't get enough of
• favorite genre
• total scrobbles (with info icon for next update)
• down or up scrobbles % from previous week
• most active hour
• artists/albums/tracks this week
• top rising artist

[neilmenon]

Favorite genre initial query (dirty):

SELECT genres.name, COUNT(*) as genre_count, SUM(scrobbles) as sum_scrobbles FROM genres 
LEFT JOIN artist_genres ON genres.id = artist_genres.genre_id
INNER JOIN (SELECT track_scrobbles.artist_id, COUNT(*) as scrobbles FROM `track_scrobbles` WHERE track_scrobbles.user_id = 1 AND from_unixtime(track_scrobbles.timestamp) BETWEEN '2021-12-23T19:04:30Z' AND '2021-12-30T19:04:30Z' GROUP BY track_scrobbles.artist_id ORDER BY scrobbles DESC) as top ON artist_genres.artist_id = top.artist_id 
GROUP BY genres.name ORDER BY sum_scrobbles DESC

Most active hour query:

SET time_zone = '+00:00';

SELECT EXTRACT(HOUR FROM from_unixtime(track_scrobbles.timestamp)) as hour, COUNT(*) as total FROM track_scrobbles WHERE user_id = 1 AND from_unixtime(track_scrobbles.timestamp) BETWEEN '2021-10-01T19:04:30Z' AND '2021-10-31T19:04:30Z' GROUP BY hour ORDER BY total DESC