Sql Complex queries [Over and Partition by]

 Over and Partition by clause 

Useful when want to select aggregated results with non-aggregated columns, group by not useful in this case.


User table having username, name, and country field,  here we want user count by country 

select username as email,
count(country) over
(partition by country) as tolaUserByCountry,
from User
order by tolaUserByCountry desc;

-- Another solution is using joins

select reseller.username as email,
from User
join (select country, count(country)
as tolaUserByCountry from User group by country ) cr
on reseller.country = cr.country order by tolaUserByCountry desc ;

Row Number

select transid, row_number() over (order by chargedamount desc )
from paymentdetails limit 5;

top 5 records of with charged amount decreasing order.

Can find nth amount.

select pd.transid, pd.chargedamount
from (select transid, chargedamount, row_number() over (order by chargedamount desc ) as id
from paymentdetails) as pd
where pd.id = 4;

There may be cases where find the best from each country or location or something else.

select pd.transid, pd.chargedamount , pd.country
from (select transid, chargedamount,country, row_number() over (partition by country order by chargedamount desc ) as id
from paymentdetails) as pd
where pd.id = 1;


Popular posts from this blog

Stay Updated As Software Engineer

12 Must-Know LeetCode+ Links for Coding Excellence

Getting Master in Hibernate and JPA