How to calculate top frequent value in SQL

Question

I have a table which has three columns: currency, exchange that trades this currency, date

Currency  Exchange Date
USD       NewYork  01/12/20
USD       NewYork  01/11/20
USD       NewYork  01/10/20
USD       Montreal 01/10/20
CAD       Montreal 01/07/20
CAD       Montreal 01/06/20
CAD       Beijing  01/06/20

I need to answer the question which exchange is a leader for this particular currency.

That means for given currency, calculate how many records for exchange and return only maximum In other words, the result of the query should be something like

Currency Exchange Frequency 
USD      NewYork  3
CAD      Montreal 2 

Answer 1

With first_value() and max() window functions:

select distinct currency,
       first_value(exchange) over (partition by currency order by count(*) desc) exchange,
       max(count(*)) over (partition by currency) frequency
from tablename
group by currency, exchange

See the demo.
Results:

> currency | exchange    | frequency
> :------- | :---------- | --------:
> CAD      | Montreal    |         2
> USD      | NewYork     |         3