I want to assign a value to a new column based on a condition for other rows associated with a user_id.
A user can see the same step with different devices at different times.
e.g. when a device changes from desktop to mobile, then assign desktop > mobile to all records for that user_id.
How can I get the first step per user and device as in the expected result below?
+---------+-------+---------------------+---------+
| user_id | step | created_at | device |
+---------+-------+---------------------+---------+
| user1 | step1 | 2021-03-16 14:03:16 | mobile |
| user1 | step2 | 2021-03-16 14:04:07 | mobile |
| user1 | step2 | 2021-03-16 14:03:47 | desktop |
| user1 | step3 | 2021-03-16 14:03:55 | mobile |
| user1 | step3 | 2021-03-16 14:04:00 | mobile |
| user1 | step1 | 2021-03-16 14:04:02 | desktop |
| user1 | step2 | 2021-03-16 14:03:16 | mobile |
| user1 | step3 | 2021-03-16 14:04:07 | mobile |
| user1 | step4 | 2021-03-16 14:04:08 | desktop |
| user1 | step4 | 2021-03-16 14:04:09 | tablet |
+---------+-------+---------------------+---------+
The expected result:
+---------+-------+---------------------+---------+---------------------+
| user_id | step | created_at | device | device_concatenated |
+---------+-------+---------------------+---------+---------------------+
| user1 | step1 | 2021-03-16 14:03:16 | mobile | mobile > desktop |
| user1 | step2 | 2021-03-16 14:03:16 | mobile | mobile > desktop |
| user1 | step3 | 2021-03-16 14:03:55 | mobile | mobile > desktop |
| user1 | step4 | 2021-03-16 14:04:08 | desktop | mobile > desktop |
+---------+-------+---------------------+---------+---------------------+
