Combine different rows of same table - Postgres

DBFiddle

This might not be the most efficient way to do this (I imagine a recursive query might be), but I find these subqueries easier to maintain:

WITH ordered_table AS (
  SELECT row_number() OVER(ORDER BY USER_ID,INTERVAL_TYPE_ID,INTERVAL_TIMESTAMP ASC) row_num, *
  FROM INTERVAL_TABLE
  ORDER BY row_num
),

_inits AS (
  SELECT
    t1.USER_ID,
    t1.INTERVAL_TYPE_ID      AS INTERVAL_TYPE,
    t1.INTERVAL_TIMESTAMP    AS INTERVAL_TIMESTAMP_INIT,
    CASE
      WHEN t1.ENTRY_TYPE = 'INIT_INTERVAL' 
       AND t2.ENTRY_TYPE = 'END_INTERVAL' 
       AND t1.USER_ID          = t2.USER_ID
       AND t1.INTERVAL_TYPE_ID = t2.INTERVAL_TYPE_ID      
      THEN t2.INTERVAL_TIMESTAMP 
    END                      AS INTERVAL_TIMESTAMP_END,
    t1.INTERVAL_ID           AS INTERVAL_ID_INIT,
    CASE
      WHEN t1.ENTRY_TYPE = 'INIT_INTERVAL' 
       AND t2.ENTRY_TYPE = 'END_INTERVAL' 
       AND t1.USER_ID          = t2.USER_ID
       AND t1.INTERVAL_TYPE_ID = t2.INTERVAL_TYPE_ID
      THEN t2.INTERVAL_ID 
    END                      AS INTERVAL_ID_END
  FROM      ordered_table AS t1
  LEFT JOIN ordered_table AS t2 ON (
    t1.row_num = t2.row_num - 1 AND
    t1.USER_ID = t2.USER_ID AND
    t1.INTERVAL_TYPE_ID = t2.INTERVAL_TYPE_ID
  )
  WHERE t1.ENTRY_TYPE = 'INIT_INTERVAL'
),

_ends AS (
  SELECT
    t2.USER_ID,
    t2.INTERVAL_TYPE_ID      AS INTERVAL_TYPE,
    NULL::timestamp          AS INTERVAL_TIMESTAMP_INIT,
    CASE
      WHEN (
          t1.ENTRY_TYPE = 'END_INTERVAL' AND
          t2.ENTRY_TYPE = 'END_INTERVAL'
        ) 
        OR (t1.ENTRY_TYPE IS NULL) -- case when first record for USER_ID and INTERVAL_TYPE_ID is an END
      THEN t2.INTERVAL_TIMESTAMP 
    END                      AS INTERVAL_TIMESTAMP_END,
    NULL::int                AS INTERVAL_ID_INIT,
    t2.INTERVAL_ID           AS INTERVAL_ID_END
  FROM       ordered_table AS t1
  RIGHT JOIN ordered_table AS t2 ON (
    t1.row_num = t2.row_num - 1 AND
    t1.USER_ID = t2.USER_ID AND
    t1.INTERVAL_TYPE_ID = t2.INTERVAL_TYPE_ID
  )
  WHERE t2.ENTRY_TYPE = 'END_INTERVAL'
)

SELECT * FROM (
  SELECT * FROM _inits
  UNION ALL
  SELECT * FROM _ends
) qry
WHERE 
  COALESCE(interval_timestamp_init, interval_timestamp_end) IS NOT NULL
ORDER BY 
  USER_ID, 
  INTERVAL_TYPE, 
  COALESCE(interval_timestamp_init, interval_timestamp_end)

Basically, INITs will always be listed. They will either have an associated END or a null. So almost all the content from _inits will be there.

Because the ENDs were already captured by the INITs, we only need to capture the ones that don't have an INIT (they were preceded by an END).

Because they are outer joins, you simply can remove the cases where INIT and END both are NULL and apply proper ordering.

This query gives the output you need:

WITH Intervals AS
(
    WITH Events AS
    (
        WITH OrderedEvents AS
        (
            SELECT INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, INTERVAL_TIMESTAMP, ENTRY_TYPE, row_number() over (partition by USER_ID, INTERVAL_TYPE_ID order by INTERVAL_TIMESTAMP ASC) AS EVENT_ORDER FROM INTERVAL_TABLE
            UNION ALL
            SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'INIT_INTERVAL' AS ENTRY_TYPE, 0 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID
            UNION ALL
            SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'END_INTERVAL' AS ENTRY_TYPE, COUNT(*) + 1 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID
        )
        SELECT Events1.USER_ID, Events1.INTERVAL_TYPE_ID, Events1.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_INIT, Events2.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_END, Events1.INTERVAL_ID AS INTERVAL_ID_INIT, Events2.INTERVAL_ID  AS INTERVAL_ID_END, Events1.ENTRY_TYPE AS ENTRY_TYPE1, Events2.ENTRY_TYPE AS ENTRY_TYPE2
        FROM OrderedEvents Events1 INNER JOIN
        OrderedEvents Events2
        ON Events1.USER_ID = Events2.USER_ID AND Events1.INTERVAL_TYPE_ID = Events2.INTERVAL_TYPE_ID AND Events1.EVENT_ORDER + 1 = Events2.EVENT_ORDER
    )
    SELECT USER_ID, INTERVAL_TYPE_ID,

      CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_TIMESTAMP_INIT
           WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN INTERVAL_TIMESTAMP_INIT
           WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN NULL
      END AS INTERVAL_TIMESTAMP_INIT,

      CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_TIMESTAMP_END
           WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN NULL
           WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_TIMESTAMP_END
      END AS INTERVAL_TIMESTAMP_END,

      CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_ID_INIT
           WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN INTERVAL_ID_INIT
           WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN NULL
      END AS INTERVAL_ID_INIT,

      CASE WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_ID_END
           WHEN ENTRY_TYPE1 = 'INIT_INTERVAL' AND ENTRY_TYPE2 = 'INIT_INTERVAL' THEN NULL
           WHEN ENTRY_TYPE1 = 'END_INTERVAL' AND ENTRY_TYPE2 = 'END_INTERVAL' THEN INTERVAL_ID_END
      END AS INTERVAL_ID_END

    FROM Events
)
SELECT * FROM Intervals WHERE INTERVAL_ID_INIT IS NOT NULL OR INTERVAL_ID_END IS NOT NULL;

At first, we build OrderedEvents CTE that groups entries by USER_ID and INTERVAL_TYPE_ID, sorts them by INTERVAL_TIMESTAMP within each group and assign numeric order to each event. Also for each group we add INIT_INTERVAL as first event and END_INTERVAL as last event to cover cases when group starts with END_INTERVAL or finishes with INIT_INTERVAL:

WITH OrderedEvents AS
(
    SELECT INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, INTERVAL_TIMESTAMP, ENTRY_TYPE, row_number() over (partition by USER_ID, INTERVAL_TYPE_ID order by INTERVAL_TIMESTAMP ASC) AS EVENT_ORDER FROM INTERVAL_TABLE
    UNION ALL
    SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'INIT_INTERVAL' AS ENTRY_TYPE, 0 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID
    UNION ALL
    SELECT NULL AS INTERVAL_ID, USER_ID, INTERVAL_TYPE_ID, NULL AS INTERVAL_TIMESTAMP, 'END_INTERVAL' AS ENTRY_TYPE, COUNT(*) + 1 AS EVENT_ORDER FROM INTERVAL_TABLE GROUP BY USER_ID, INTERVAL_TYPE_ID
)
SELECT * FROM OrderedEvents ORDER BY user_id, interval_type_id, event_order;

This query gives following results for the provided data:

Then we intersect OrderedEvents with itself on USER_ID and INTERVAL_TYPE_ID and select pairs of neighbor events (Events1.EVENT_ORDER + 1 = Events2.EVENT_ORDER):

WITH OrderedEvents AS
(
    ...
)
SELECT Events1.USER_ID, Events1.INTERVAL_TYPE_ID, Events1.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_INIT, Events2.INTERVAL_TIMESTAMP AS INTERVAL_TIMESTAMP_END, Events1.INTERVAL_ID AS INTERVAL_ID_INIT, Events2.INTERVAL_ID  AS INTERVAL_ID_END, Events1.ENTRY_TYPE AS ENTRY_TYPE1, Events2.ENTRY_TYPE AS ENTRY_TYPE2
FROM OrderedEvents Events1 INNER JOIN
OrderedEvents Events2
ON Events1.USER_ID = Events2.USER_ID AND Events1.INTERVAL_TYPE_ID = Events2.INTERVAL_TYPE_ID AND Events1.EVENT_ORDER + 1 = Events2.EVENT_ORDER

This query gives following results:

Now we should transform these pairs of neighbor events to intervals based on the logic you described. Previous output has columns entry_type1 and entry_type2 which could take values of INIT_INTERVAL or END_INTERVAL. The possible combinations are:

• <INIT_INTERVAL, END_INTERVAL> - this is the most natural case when INIT_INTERVAL is followed by END_INTERVAL. We take event values as is.
• <INIT_INTERVAL(1), INIT_INTERVAL(2)> - the case of two consecutive INIT_INTERVAL. We force ending of the interval by taking <INIT_INTERVAL(1), NULL>. INIT_INTERVAL(2) will be taken with the next pair when it will be in the first entry.
• <END_INTERVAL(1), END_INTERVAL(2)> - the case of two consecutive END_INTERVAL. We force start of the interval by taking <NULL, END_INTERVAL(2)>. END_INTERVAL(1) is processed either by case #1 or by the current case when it is the second entry in the pair.
• <END_INTERVAL, INIT_INTERVAL> - such pairs are just skipped. END_INTERVAL is taken either by case #1 or case #3. INIT_INTERVAL is taken either by case #1 or case #2.

All this logic is put into set of CASE expressions. There are 4 such expressions with duplicated conditions, because we conditionally select 4 different columns (INTERVAL_TIMESTAMP_INIT, INTERVAL_TIMESTAMP_END, INTERVAL_ID_INIT and INTERVAL_ID_END) which could not be done with one CASE expression.

The final output is the same as you described:

You can use the INTERVAL_ID (or a new column with a generated row_number) to join two instances of the same table, using as predicate something like this:

on a.INTERVAL_ID=b.INTERVAL_ID + 1

This way, you can compare and get in 1 line each record with the next one.