Finding Top-100 of a given year from a mysql table with last.fm scrobbles

Question

I imported to a MariaDB table a CSV file generated by this tool with all my last.fm scrobbles, the CREATE script is the following:

CREATE TABLE `scrobbles` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `artist` VARCHAR(128) NULL DEFAULT '',
    `album` VARCHAR(128) NULL DEFAULT '',
    `title` VARCHAR(128) NULL DEFAULT '',
    `datahora` DATETIME NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`)
)ENGINE=InnoDB;

I want to know how can i get the most executed tracks (basically the title+artist combo most repeating) of a given year, ordered by the number of plays/scrobbles of each track.

Answer 1

If you want this for a single year, you can aggregate, sort and limit:

select artist, album, title, count(*) cnt
from scrobbles
where datahora >= '2019-01-01' and datahora < '2020-01-01'
group by artist, album, title
order by count(*) desc limit 100

I added the album to the group by clause, as one might expect homonym titles across different albums.

If you want this for multiple years at once, then I would recommend window functions:

select *
from (
    select artist, album, title, year(datahora) yr, count(*) cnt,
        rank() over(partition by year(datahora) order by count(*) desc) rn
    from scrobbles
    group by artist, album, title
) t
where rn <= 100
order by yr, cnt desc

Another benfit of this approach is that it allows bottom ties; it might return more than 100 rows per year if there happened to be ties in the last position.