Postgresql OVER and GROUP

Question

I have a problem with grouping

I have a table(a lot more stuff in it, but not relevant) that looks something like:

id user
0 1
1 1
2 1
3 2
4 2
5 2
6 1
7 1

I'm trying to get the following value:

user start end
1 0 2
2 3 5
1 6 7

Basicaly, i need the first and last occurance of user, while not messing with order. I know i need to use OVER (PARTITION BY ...), But i've never used it and not sure how to build this query. if i "partition by user", it ignores order. And if i "partition by id, user" it again returns wrong.

Example of what i tried(doesn't even try to get what i need, but is a midpoint that shows me how to do it once i figure out "over" part):

SELECT user, count(user) over (partition by user):
user count
1 5
1 5
1 5
2 3
2 3
2 3
1 5
1 5

SELECT user, count(user) over (partition by id, user):
user count
1 1
1 1
1 1
2 1
2 1
2 1
1 1
1 1

Answer 1

Use a difference of row numbers approach to classify consecutive rows with the same user into one group and start over a new group when a new user is encountered. Thereafter, use group by to get the start and end of each group.

SELECT USER,MIN(ID) AS START,MAX(ID) AS END
FROM (SELECT user,id, row_number() over(order by id) 
                     - row_number() over (partition by user order by id) as grp
      FROM tablename
      ) T
GROUP BY USER,GRP