Is there a way to collapse ordered rows by terminal values with postgres window clause

Question

I have a table foo:

some_fk	some_field	some_date_field
1	A	1990-01-01
1	B	1990-01-02
1	C	1990-03-01
1	X	1990-04-01
2	B	1990-01-01
2	B	1990-01-05
2	Z	1991-04-11
2	C	1992-01-01
2	B	1992-02-01
2	Y	1992-03-01
3	C	1990-01-01

some_field has 6 possible values: [A,B,C,X,Y,Z] Where [A,B,C] signify opening or continuation events and [X,Y,Z] signify closing events. How do I get each span of time between the first opening event and closing event of each span, partitioned by some_fk, as shown in the table below:

some_fk	some_date_field_start	some_date_field_end
1	1990-01-01	1990-04-01
2	1990-01-01	1991-04-11
2	1992-01-01	1992-03-01
3	1990-01-01	NULL

*Note that a non-terminated time span ends with NULL

I do have a solution that involves 3 common table expressions, but I'm wondering if there is a (better/more elegant/canonical) way to do this in PostgreSQL without nested queries.

My approach was something like:

WITH ranked AS (
  SELECT
    RANK() OVER (PARTITION BY some_fk ORDER BY some_date_field) AS "rank"
    some_fk,
    some_field,
    some_date_field
  FROM foo    
), openers AS (
  SELECT * FROM ranked WHERE some_field IN ('A','B','C')
), closers AS (
  SELECT
    *,
    LAG("rank") OVER (PARTITION BY some_fk ORDER BY "rank") AS rank_lag
  FROM ranked WHERE some_field IN ('X','Y','Z')
)
SELECT DISTINCT
  openers.some_fk,
  FIRST_VALUE(openers.some_date_field) OVER (PARTITION BY some_fk ORDER BY "rank")
    AS some_date_field_start,
  closers.some_date_field AS some_date_field_end
FROM openers
JOIN closers
  ON openers.some_fk = closers.some_fk
WHERE openers.some_date_field BETWEEN COALESCE(closers.rank_lag, 0) AND closers.rank

... but I feel there must be a better way.

Thanks in advance for the help.

Answer 1

Another approach is to create a grouping ID by creating a running sum of the closing events. Then in an outer SQL you can Group By and pick min() and max() dates.

Select some_fk,min(some_date) as some_date_field_start, max(some_date) as some_date_field _end
From (
    Select some_fk,some_date,
      Sum(Case When some_field in ('X','Y','Z') Then 1 Else 0 End)
        Over (Partition By some_fk Order By some_date
        Rows Between Unbounded Preceding And 1 Preceding)
      as some_grouping
    From foo
)
Group By some_fk,some_grouping
Order By some_fk,some_grouping

This seems a little simpler at least to me.