The existence of LAG and LEAD have solved some seriously iffy requirements twice in the past year now. One time for measuring / determining from two consecutive GPS points some details about speed, route and distance (omitting some details here), LAG allowed to see and compare the previous points recorded and is IMO absolutely vital for PostGIS.

second time for calculating changes between two rows and combining that with some business rules, the existence of LAG was absolutely crucial for keeping it all in the database.

RANK / PARTITION BY is something that when you need it you really really need it. Like if you join a bunch of rows to a single column but would like to just have the first or last of those rows for results and disregard all the other rows? RANK and order by