Google Sheets query to extract the top three instances according to criteria

Question

I have a Google Spreadsheet with two sheets. In sheet "Source" I have a series of countries, cities and landmarks - these are,respectively, in columns A, B and C.

In sheet "Sheet for Query", there are two columns: (A) Country, which has a list of unique country names; and (B) Top 3 cities by Landmark. In column B, I would like to have a Query which gives me, for each country, the top three cities by number of landmark, i.e., the query just has to count the number of instances each city in each country appears and return, for each country, the names of the three cities that come up the most times

This is a sample sheet that I've created in order to demonstrate what I mean: https://docs.google.com/spreadsheets/d/1IPwtAHjwjV1A03o9URws-AtDKw3h9QS9UTT0P1PeVN0/edit?usp=sharing.

Thank you!

Answer 1

I've given this some thought and to 'just' count the number of instances and return the top 3 in each country is surprisingly difficult.

The grouping is straightforward with a query like this

=query(A:C," select A,B,count(C) where A<>'' group by A,B  order by A,count(C) desc label A 'Country',B 'City', Count(C) 'Landmarks'",1)

But I don't know of a way of getting the top 3 for each group without going through 2 further steps

(1) Number the results in each group (various ways of doing it but here is one)

=(E1=E2)*D1+1

where the country names after grouping are in column E.

(2) Filter the result for the number in column D being less than 4

=filter(E:G,D:D<4)

Answer 2

You don't specify what qualifies as top (so assuming those are the first listed - higher up the sheet), and you don't clarify number of landmark where there are no numbers in your sheet, but perhaps:

=textjoin(", ",,query(Source!A:C,"select B where A='"&A2&"' limit 3"))

in B2 of sheet for Query, copied down to suit.