inner join - How to add the first value of a column in SQL over another grouping variable

Question

I have a database with a table of the following kind:

customer_id	customer_type	customer_state	state_date
1	A	0	2020-01-01
1	A	1	2020-01-05
1	B	2	2020-01-06
2	X	0	2019-02-07
2	Y	0	2019-02-07
2	X	0	2019-02-07

question from:https://stackoverflow.com/questions/65842505/how-to-add-the-first-value-of-a-column-in-sql-over-another-grouping-variable

Answer 1

One possibility, using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY state_date) rn
    FROM yourTable
)

SELECT customer_id, customer_type, customer_state, state_date,
       MAX(CASE WHEN rn = 1 THEN customer_type END) OVER
           (PARTITION BY customer_id) first_type
FROM cte;