Group by on column that repeats

Question

I'm having trouble putting this issue into words which is probably why I can't find an example so here is what I'd like to do.

I have a table like such

    | counter|      timestamp      |
    |   1    | 2018-01-01T11:11:01 |
    |   1    | 2018-01-01T11:11:02 |
    |   1    | 2018-01-01T11:11:03 |
    |   2    | 2018-01-01T11:11:04 |
    |   2    | 2018-01-01T11:11:05 |
    |   3    | 2018-01-01T11:11:06 |
    |   3    | 2018-01-01T11:11:07 |
    |   1    | 2018-01-01T11:11:08 |
    |   1    | 2018-01-01T11:11:09 |
    |   1    | 2018-01-01T11:11:10 |

what I'd like to do is group by each group of counters so if I do a query like

SELECT counter, MAX(timestamp) as st, MIN(timestamp) as et 
FROM table 
GROUP BY counter;

the result would be

    | counter |          st         |         et          |
    |   1     | 2018-01-01T11:11:01 | 2018-01-01T11:11:03 |
    |   2     | 2018-01-01T11:11:04 | 2018-01-01T11:11:05 |
    |   3     | 2018-01-01T11:11:06 | 2018-01-01T11:11:07 |
    |   1     | 2018-01-01T11:11:08 | 2018-01-01T11:11:10 |

instead of what actually happens which is

    | counter |          st         |         et          |
    |   1     | 2018-01-01T11:11:01 | 2018-01-01T11:11:10 |
    |   2     | 2018-01-01T11:11:04 | 2018-01-01T11:11:05 |
    |   3     | 2018-01-01T11:11:06 | 2018-01-01T11:11:07 |

So I'd like some what to combine group by and partition ideally without having nested queries

Answer 1

You have to designate groups with the same repeating values of counter. This can be done using two window functions lag() and cumulative sum():

select counter, min(timestamp) as st, max(timestamp) as et
from (
    select counter, timestamp, sum(grp) over w as grp
    from (
        select *, (lag(counter, 1, 0) over w <> counter)::int as grp
        from my_table
        window w as (order by timestamp)
        ) s
    window w as (order by timestamp)
    ) s
group by counter, grp
order by st

DbFiddle.