How can I find the top 10 countries that have generated the most revenue on my site?
You can use the windows functions to determine the right order of countries by the sum of their revenues. If you want more than 10 countries (top N) then replace 11 with N+1 (for instance for top 20 replace 11 with 21). Otherwise all the rest of the countries are marked as “other” and their revenues is summed under “Other”
If you want to segment by another property (not country) replace country with the name of the property.
SELECT country , revenues
FROM
( SELECT group_id ,
CASE WHEN group_id < 11 THEN country
ELSE "Other" END AS country ,
sum(revenues) revenues
FROM
( SELECT country , revenues ,
if(row_number<11, row_number, 11) group_id
FROM
( SELECT country , ROW_NUMBER() OVER() row_number, revenues
FROM
( SELECT ip_country as country , sum(amount) as revenues
FROM cooladata
WHERE date_range (context) and filters (context) and event_name = ‘purchase’
GROUP BY 1
ORDER BY 2 DESC ) ) )
GROUP BY 1, 2
ORDER BY group_id ASC)
