SELECT
    b.username, b.country, b.region, b.city, b.lat, b.lng, max(count)
FROM (
    SELECT
        a.username,
        a.country,
        a.region,
        a.city,
        a.lat,
        a.lng,
        count(1) AS count
    FROM (
        SELECT
            username AS username,
            geoip(end_song_cleaned.ip_addr, "COUNTRY_CODE", "GeoIPCity.dat") AS country,
            geoip(end_song_cleaned.ip_addr, "REGION", "GeoIPCity.dat") AS region,
            geoip(end_song_cleaned.ip_addr, "CITY", "GeoIPCity.dat") AS city,
            geoip(end_song_cleaned.ip_addr, "LATITUDE", "GeoIPCity.dat") AS lat,
            geoip(end_song_cleaned.ip_addr, "LONGITUDE", "GeoIPCity.dat") AS lng
        FROM end_song_cleaned
        WHERE dt = 20101201
    ) a

    GROUP BY a.username, a.country, a.region, a.city, a.lat, a.lng
    ORDER BY username, count DESC
) b
GROUP BY
    b.username, b.country, b.region, b.city, b.lat, b.lng