sql - Combine multiple rows with different dates with overlapping variables (to capture first and last change dates)

Question

I have the following data represented in a table like this:

User	Type	Date
A	Mobile	2019-01-10
A	Mobile	2019-01-20
A	Desktop	2019-03-01
A	Desktop	2019-03-20
A	Email	2021-01-01
A	Email	2020-01-02
A	Desktop	2021-01-03
A	Desktop	2021-01-04
A	Desktop	2021-01-05

Answer 1

Consider using a LAG window function and conditional aggregation join via multiple CTEs and self-joins:

WITH sub AS (
  SELECT "user"
       , "type"
       , "date"
       , CASE 
            WHEN LAG("type") OVER(PARTITION BY "user" ORDER BY "date") = "type"
            THEN 0
            ELSE 1
         END "shift"
  FROM myTable 
), agg AS (
   SELECT "user"
         , MIN(CASE WHEN shift = 1 THEN "date" END) AS min_shift_dt
         , MAX(CASE WHEN shift = 1 THEN "date" END) AS max_shift_dt
   FROM sub
   GROUP BY "user"
)


SELECT agg."user"
     , s1."type" AS first_type
     , s1."date" AS first_type_initial_date
     , s2."type" AS last_type
     , s2."date" AS last_type_initial_date
FROM agg
INNER JOIN sub AS s1
  ON agg."user" = s1."user"
  AND agg.min_shift_dt = s1."date"
  
INNER JOIN sub AS s2
  ON agg."user" = s2."user"
  AND agg.max_shift_dt = s2."date"

Online Demo

user	first_type	first_type_initial_date	last_type	last_type_initial_date
A	Mobile	2019-01-10 00:00:00	Desktop	2021-01-03 00:00:00