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)
Segentation
kpi