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 Show Table Schema
Cats:
name | varchar |
breed | varchar |
weight | float |
color | varchar |
age | int |

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
Desired output:
name | weight | weight_to_lose |
Tigger | 3.8 | 0.0 |
Molly | 4.2 | 0.4 |
Ashes | 4.5 | 0.3 |
Charlie | 4.8 | 0.3 |
Smudge | 4.9 | 0.1 |
Felix | 5.0 | 0.1 |
Puss | 5.1 | 0.1 |
Millie | 5.4 | 0.3 |
Alfie | 5.5 | 0.1 |
Misty | 5.7 | 0.2 |
Oscar | 6.1 | 0.4 |
Smokey | 6.1 | 0.0 |
select name, weight, coalesce(weight - lag(weight, 1) over (order by weight), 0) as weight_to_lose FROM cats order by weight