Compare to Row
Question:
Cats are fickle. Each cat would like to lose weight to be the equivalent weight of the cat weighing just less than it.
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
Cats:
| name | varchar |
| breed | varchar |
| weight | float |
| color | varchar |
| age | int |
Desired output:
| name | weight | weight_to_lose |
| Tigger | 3.80 | 0.00 |
| Molly | 4.20 | 0.40 |
| Ashes | 4.50 | 0.30 |
| Charlie | 4.80 | 0.30 |
| Smudge | 4.90 | 0.10 |
| Felix | 5.00 | 0.10 |
| Puss | 5.10 | 0.10 |
| Millie | 5.40 | 0.30 |
| Alfie | 5.50 | 0.10 |
| Misty | 5.70 | 0.20 |
| Oscar | 6.10 | 0.40 |
| Smokey | 6.10 | 0.00 |
Query Window:
Correct output but can you use 'lagleadmin'?
Good work!
lag() lets us compare to the previous rows [and lead() the next rows] [min() also works]
It is useful for looking for strange step ups/downs in data
Fun Fact: Lag
can take a default.
But it must be the same type. For doubles this means explicitly casting it as double precision.
next question
Answer:
select name, weight,
coalesce(weight - lag(weight, 1) over (order by weight), 0) as weight_to_lose
FROM cats order by weight