MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop table if exists users;
drop table if exists views;
drop table if exists tracks;
create table users
(idu      int
,username varchar(30));
create table views
(id       int
,`by`     int
,track    int);
create table tracks
(id       int
,uid      int
,title    varchar(30)
,time     datetime);
insert into users values
(567,'TrainingPuppy'),
(687,'BathroomMakeover'),
( 45,'PoshNachos'),
( 15,'SewingButtons');
insert into views values
(1,  5, 55),
(2,  5, 55),
(3, 67, 55),
(4,  6, 29),
(5,125,  2),
(6,  5,698),
(7,  5,698);
insert into tracks values
(  2, 15,'Worf is in the air','2016-02-11 22:57:35'),
( 29,567,'Stargold'          ,'2016-08-11 22:57:28'),
( 55,567,'No love liers'     ,'2016-10-11 22:57:51'),
(698,567,'Lofe'              ,'2016-11-11 22:57:44');
select id,
       uid,
       ucount as 'count',
       username,
       track,
       title,
       time
       
from   (select id,
               uid,
               ucount,
               username,
               track,
               title,
               time,
               @user_rank        := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
               @current_username := username
        
        from   (select tracks.id,
                       tracks.uid,
                       count(*) as ucount,
                       users.username,
                       views.track,
                       tracks.title,
                       tracks.time

                from    tracks
                join    views
                on      views.track = tracks.id

                join    users
                on      users.idu = tracks.uid

                group by tracks.id,
                         tracks.uid,
                         users.username,
                         views.track,
                         tracks.title,
                         tracks.time) user_counts
         
                -- initialize our variables
        join   (select @user_rank := 0, @current_username := 'abcdefg') r

        order by username, ucount desc) dt
                 
where   user_rank = 1

order by ucount desc, username


id
uid
count
username
track
title
time


55
567
3
TrainingPuppy
55
No love liers
2016-10-11 22:57:51

2
15
1
SewingButtons
2
Worf is in the air
2016-02-11 22:57:35
id	uid	count	username	track	title	time
55	567	3	TrainingPuppy	55	No love liers	2016-10-11 22:57:51
2	15	1	SewingButtons	2	Worf is in the air	2016-02-11 22:57:35