There are 2 tables in SQL Customer and Transaction, Display the customer details who has done maximum number of transactions

Question

Following are the table Attributes:

1. Customer Table: Customer ID, Customer Name, Address, Email ID and Mobile Number.
2. Transaction Table: Transaction ID, Transaction Type, Amount and Customer ID.

Answer 1

You can join the customer table with an aggregate query that computes their count of transaction:

select c.*, t.cnt
from customers c
inner join (select customerid, count(*) cnt from transactions group by customerid) t
    on t.customerid = c.customerid
order by t.cnt desc
limit 1

If you don't need to display the count of transactions in the resultset, you can also use a correlated subquery for filtering:

select c.*
from customers c
order by (select count(*) from transactions t where t.customerid = c.customerid) desc
limit 1

If you want to allow top ties, then one option is to use window functions (available in MySQL 8.0 only):

select c.*
from customers c
inner join (
    select customerid, count(*) cnt, rank() over(order by count(*) desc) rn
    from transactions 
    group by customerid
) t on t.customerid = c.customerid
where t.rn = 1