SQL get number of unique client by browser

Question

I'm using AWS Athena to parse my Application Load Balancer logs.

I'm trying to get a list of browsers and for each browsers, the number of unique user.

I've managed to get this list but the user count is not correct. I don't know how to group users by their IP.

1   Google Chrome       9000000
2   Apple Safari        8000000
3   Unknown             5000000
4   Mozilla Firefox     2000000
5   Internet Explorer     10000
6   Outlook               10000
7   Opera                    88
8   Edge                      7

Here is the query

SELECT DISTINCT
    CASE
    WHEN user_agent LIKE '%edge%'THEN 'Edge'
    WHEN user_agent LIKE '%MSIE%' THEN
    'Internet Explorer'
    WHEN user_agent LIKE '%Firefox%' THEN
    'Mozilla Firefox'
    WHEN user_agent LIKE '%Chrome%' THEN
    'Google Chrome'
    WHEN user_agent LIKE '%Safari%' THEN
    'Apple Safari'
    WHEN user_agent LIKE '%Opera%' THEN
    'Opera'
    WHEN user_agent LIKE '%Outlook%' THEN
    'Outlook'
    ELSE 'Unknown'
    END AS browser , COUNT(client_ip) AS Number
FROM alb_logs
WHERE parse_datetime(time,'yyyy-MM-DD''T''HH:mm:ss.SSSSSS''Z')
    BETWEEN parse_datetime('2018-01-01-00:00:00','yyyy-MM-DD-HH:mm:ss')
        AND parse_datetime('2018-07-18-00:00:00','yyyy-MM-DD-HH:mm:ss')
GROUP BY  CASE
    WHEN user_agent LIKE '%edge%'THEN 'Edge'
    WHEN user_agent LIKE '%MSIE%' THEN
    'Internet Explorer'
    WHEN user_agent LIKE '%Firefox%' THEN
    'Mozilla Firefox'
    WHEN user_agent LIKE '%Chrome%' THEN
    'Google Chrome'
    WHEN user_agent LIKE '%Safari%' THEN
    'Apple Safari'
    WHEN user_agent LIKE '%Opera%' THEN
    'Opera'
    WHEN user_agent LIKE '%Outlook%' THEN
    'Outlook'
    ELSE 'Unknown'
    END
ORDER BY  Number DESC

I'm missing some kind of group by client_ip, but the result would be wrong...

Answer 1

You need COUNT(DISTINCT client_ip) aggregation and also you don't need SELECT DISTINCT, like this

SELECT CASE WHEN user_agent ... END AS browser, COUNT(DISTINCT client_ip) AS Number
FROM alb_logs
WHERE ...
GROUP BY 1
ORDER BY 2 DESC