Compare to Row Part 2
Question:
The cats now want to lose weight according to their breed. Each cat would like to lose weight to be the equivalent weight of the cat in the same breed weighing just less than it.
Print a list of cats, their breeds, weights and the weight difference between them and the nearest lighter cat of the same breed.
Return: name, breed, weight, weight_to_lose
Order by: weight
Show Table
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Show Desired Output
Desired output:
| name | breed | weight | weight_to_lose |
| Tigger | British Shorthair | 3.80 | 0.00 |
| Molly | Persian | 4.20 | 0.00 |
| Ashes | Persian | 4.50 | 0.30 |
| Charlie | British Shorthair | 4.80 | 1.00 |
| Smudge | British Shorthair | 4.90 | 0.10 |
| Felix | Persian | 5.00 | 0.50 |
| Puss | Maine Coon | 5.10 | 0.00 |
| Millie | Maine Coon | 5.40 | 0.30 |
| Alfie | Siamese | 5.50 | 0.00 |
| Misty | Maine Coon | 5.70 | 0.30 |
| Oscar | Siamese | 6.10 | 0.60 |
| Smokey | Maine Coon | 6.10 | 0.40 |
Query Window:
Correct output but can you use 'lagleadmin'?
Show Answer
Answer:
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