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
  ( SELECT group_id ,
           CASE  WHEN group_id < 11 THEN country
               ELSE "Other"  END AS country ,
           sum(revenues) revenues
     ( SELECT country ,  revenues ,
              if(row_number<11, row_number, 11) group_id
        ( SELECT country , ROW_NUMBER() OVER() row_number,   revenues
           ( 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)