Questions on “OVER” Sql
These questions gives you knowledge of the Over Command.
Note: To Understand them you will need to know The following commands: nth_value, ntile, lag, lead.
CATS
- Find the total weight of cats grouped by age. But only return those groups with a total weight larger than 12?
select age, sum(weight) as "total_weight"
from cats
group by age
having sum(weight)>12
2.Group the cats into quartiles by their weight.
Return: name, weight, weight_quartile
Order by: weight
select name,weight,
NTILE(4) over(order by weight) as weight_quartile
from cats 
3.Print a list of cats, their weights and the weight difference between them and the nearest lighter cat ordered by weight.
Return: name, weight, weight_to_lose
Order by: weight
select name, weight,
coalesce(weight - lag(weight, 1) over (order by weight), 0) as weight_to_lose
from cats 
4.Print a list of cats, their breeds, weights and the weight difference between them and the nearest lighter cat of the same breed.
Get Akheelmohammad’s stories in your inbox
Join Medium for free to get updates from this writer.
Return: name, breed, weight, weight_to_lose
Order by: weight
select name, breed,weight,
coalesce(weight - lag(weight, 1) over (partition by breed order by weight), 0) as weight_to_lose
from cats
order by weight,name
5.Print cat name, color and the minimum weight of cats with that color.
Return: name, color, lowest_weight_by_color
Order by: color, name
select name,color,
min(weight) over(partition by color order by weight) as weight_by_color
from cats
order by color,name
6.Print a list of cats, their weights and either the next heaviest cat’s weight or ‘fattest cat’
Return: name, weight, breed, next_heaviest
Order by: weight
select name, weight, breed, coalesce(cast(lead(weight) over (partition by breed order by weight) as varchar),'fattest cat') as next_heaviest
from cats
order by weight
7.Print a list of breeds, and the second lightest weight of that breed
Return: breed, imagined_weight
Order by: breed
select distinct(breed)
nth_value(weight, 2)
over(partition by breed order by weight RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as imagined_weight
from cats order by breed;
Hope you like the questions and able to understand😊
These questions are from: http://www.windowfunctions.com/questions/grouping/
