Trying to wrap my mind around awful SQL query

Question

Can someone please help me understand what exactly this query does?

SELECT pp.Sedol
    ,MAX(MAX(Id)) OVER (
        PARTITION BY pp.Sedol
        ,MAX(pp.ValueDate)
        ) PriceId
FROM Prices pp
GROUP BY pp.Sedol

Answer 1

This is equivalent to:

with x as (
  select
    Sedol,
    max(id) max_id,
    Max(ValueDate) max_valuedate
  from
    Prices
  group by
    Sedol
) select
  Sedol,
  max(max_id) over (partition by Sedol, max_valuedate) PriceId
from
  x;

Although as Lamak says, I can't see any way this isn't going to just be equivalent to

SELECT Sedol, MAX(Id) PriceId FROM Prices GROUP BY Sedol

SQL Fiddle