CODEWITHSUNDEEP
sqlpostgresql
nz426
nz426

Reputation: 91

Return row with the highest count (like city with most transactions, when there are multiple cities)

I have a table with the following rows: customer_id city transaction_id date

I want to pull the city which has the highest number of transactions (count(transaction_id)) over a certain time period. A customer can have transactions in multiple cities.

What's the right syntax to achieve this?

UPDATE: customer A can have 5 transactions in NYC and 10 transactions in Boston. I just want the customer record of the 10 transactions in Boston to be returned. How do I do this and what would have to cities that have the same number of transactions per customer_id?

Upvotes: 1

Views: 604

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

if you need just top city name count then you can use below approach

select city,count(transaction_id) as cnt
From YourTable
where date=condition --- that you need
group by city
order by cnt desc
limit 1

if you need customerwise highest city name then use row_number() 

with cte as
(
 select customerid,city,count(*) as cnt
 from table_name group by customerid,city
), cte1 as
(
select * ,row_number()over(partition by cusomerid order by cnt desc) rn
 from cte
) select * from cte1 where rn=1

Upvotes: 2

Related Questions