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.

Example  

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

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

-- Another solution is using joins

select reseller.username as email,
reseller.name,
tolaUserByCountry
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;