First and Last Type and Initial Date Query, PostgreSQL

Language: Layout:

DROP TABLE IF EXISTS myTable;

CREATE TABLE myTable (
    "user" VARCHAR(1),
    "type" VARCHAR(7),
    "date" TIMESTAMP
);

INSERT INTO myTable ("user", "type", "date")
VALUES ('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');
        
        
SELECT "user", "type", "date" FROM myTable;

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"


absolute service time: 0,42 sec