Get the start date and end date-CodePudding

I need to get the start date and end date. help me please I have such a table:

| login| date_start| date_end  |
|user1 | 2022-01-01| 2022-01-01|
|user1 | 2022-01-02| 2022-01-02|
|user1 | 2022-01-03| 2022-01-03|
|user2 | 2022-01-05| 2022-01-05|
|user2 | 2022-01-06| 2022-01-06|
|user1 | 2022-02-24| 2022-02-24|
|user1 | 2022-02-25| 2022-02-25|
|user1 | 2022-02-26| 2022-02-26|

expected result:

user1 2022-01-01 - 2022-01-03
user2 2022-01-05 - 2022-01-06
user1 2022-02-24 - 2022-02-26

CodePudding user response：

This is a gaps and islands problem. One approach uses the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY date_start) rn1,
              ROW_NUMBER() OVER (PARTITION BY login ORDER BY date_start) rn2
    FROM yourTable
)

SELECT login, MIN(date_start) AS date_start, MAX(date_end) AS date_end
FROM cte
GROUP BY login, rn1 - rn2
ORDER BY MIN(date_start);

Here is a working demo.