SELECT
    b.username,
    b.country,
    b.region,
    b.city,
    b.lat,
    b.lng,
    max(b.count)
FROM (
    SELECT
        a.username AS username,
        a.country AS country,
        a.region AS region,
        a.city AS city,
        a.lat AS lat,
        a.lng AS 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