Joining on windowed function to show the next date in relation to ID

I have a query I am putting together, but I am struglling to join the sequence generated by a windowed function in POSTGRESQL

 SELECT e.start_time, ne.start_time, e.end_time
    FROM
        (SELECT e.ID, e.start_time, e.end_time, ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY e.start_time) AS "SEQ"
        FROM events e
        )e
    LEFT JOIN 
        (SELECT ne.ID, ne.start_time, ne.end_time, ROW_NUMBER() OVER (PARTITION BY ne.ID ORDER BY ne.start_time) AS "nSEQ" 
        FROM events ne
        )ne
    ON e.ID = ne.ID
    AND e.SEQ = ne.nSEQ - 1

In SQL server this would have done just fine, but it appears POSTGRESQL does not like this way of joining for some reason. Could someone advise on another approach?

I expect to see the preceding event_start and the next event_start on the same row relative to the ID

Solution

You didn't tell us the error you get, but I guess it's something about an invalid identifier.

You are mixing quoted identifiers (= case sensitive) with non-quoted identifiers (= case insensitive).

As documented in the manual (and required by the SQL standard), "nSEQ" is a different name than nSEQ. My recommendation is: remove double quotes from all your SQL statements entirely, then you will never have a problem.

The following should work just fine:

SELECT e.start_time, ne.start_time, e.end_time
FROM (
  SELECT e.ID, e.start_time, e.end_time, 
         ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY e.start_time) AS SEQ --<< no quotes!
  FROM events e
) e 
  LEFT JOIN (
    SELECT ne.ID, ne.start_time, ne.end_time, 
           ROW_NUMBER() OVER (PARTITION BY ne.ID ORDER BY ne.start_time) AS nSEQ --<< no quotes
    FROM events ne
  ) ne ON e.ID = ne.ID AND e.SEQ = ne.nSEQ - 1;

Unrelated, but: that is a quite inefficient way of getting the "next" value. Use the lead() function instead and you can get rid of the whole join completely:

SELECT e.start_time, 
       lead(e.end_time) over (PARTITION BY e.ID ORDER BY e.start_time) as next_end_time, 
       e.end_time, 
FROM events e