0

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. However, only the first step must be taken into consideration.

e.g. step2 was seen by both mobile and desktop, but was first seen by mobile at 2021-03-16 14:03:16. Likewise step4 was seen by both desktop and tablet, but first desktop.

To sum up, each step first seen by

step1 = mobile
step2 = mobile
step3 = mobile
step4 = desktop

Since the only device change happened at step4, 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?

Sample data:

+---------+-------+---------------------+---------+
| 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    |
+---------+-------+---------------------+---------+---------------------+
6
  • Hi, and welcome to the forum! What version of PostgreSQL are you running? It's always good to include this in any questions... Also, with your table and data, could you also please provide a fiddle here. Help us to help you!
    – Vérace
    Apr 8, 2021 at 10:31
  • @Vérace I'm using Postgres 10. The first seen step must be accounted for. Hence, the first seen step3 with 2021-03-16 14:03:55 by mobile. I want to assign one single value for all four steps. Since the only device change happens at step 4 mobile > desktop. All steps should only get this.
    – kimi
    Apr 8, 2021 at 10:51
  • Explained in more detail above.
    – kimi
    Apr 8, 2021 at 11:25
  • IS there a heirarchy for devices?
    – Vérace
    Apr 8, 2021 at 11:30
  • No, there is not.
    – kimi
    Apr 8, 2021 at 11:39

2 Answers 2

0

Given the data as is (as of 11:38 UTC, 2021-04-08), this might help (see PostgreSQL fiddle here):

CREATE TABLE tab
(
  user_id TEXT NOT NULL,
  step    TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  device  TEXT NOT NULL
);

Populate it (slightly rearranged from original data to put it in order - user_id, step & created_at - makes it easier to figure out what's going on):

INSERT INTO tab 
VALUES

( 'user1', 'step1', '2021-03-16 14:03:16', 'mobile'),
( 'user1', 'step1', '2021-03-16 14:04:02', 'desktop'),


( 'user1', 'step2', '2021-03-16 14:03:16', 'mobile'),
( 'user1', 'step2', '2021-03-16 14:03:47', 'desktop'),
( 'user1', 'step2', '2021-03-16 14:04:07', 'mobile'),


( 'user1', 'step3', '2021-03-16 14:03:55', 'mobile'),
( 'user1', 'step3', '2021-03-16 14:04:00', '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');

I'll show the steps that led me to the solution - might be helpful (remove surplus field as appropriate for your requirements):

SELECT 
  user_id, step, created_at, device, 
  FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  FIRST_VALUE(created_at) OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  CONCAT(
  (FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)),
  ' > ',
  (LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)))
  AS device_concatenated,
  ROW_NUMBER() OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at) AS rn
FROM tab
ORDER BY user_id, step, created_at;

Result (better viewed on fiddle):

user_id step    created_at  device  first_value first_value lead    device_concatenated rn
user1   step1   2021-03-16 14:03:16 mobile  mobile  2021-03-16 14:03:16 desktop mobile > desktop    1
user1   step1   2021-03-16 14:04:02 desktop mobile  2021-03-16 14:03:16     mobile >    2
user1   step2   2021-03-16 14:03:16 mobile  mobile  2021-03-16 14:03:16 desktop mobile > desktop    1
user1   step2   2021-03-16 14:03:47 desktop mobile  2021-03-16 14:03:16 mobile  mobile > mobile 2
user1   step2   2021-03-16 14:04:07 mobile  mobile  2021-03-16 14:03:16     mobile >    3
user1   step3   2021-03-16 14:03:55 mobile  mobile  2021-03-16 14:03:55 mobile  mobile > mobile 1
user1   step3   2021-03-16 14:04:00 mobile  mobile  2021-03-16 14:03:55 mobile  mobile > mobile 2
user1   step3   2021-03-16 14:04:07 mobile  mobile  2021-03-16 14:03:55     mobile >    3
user1   step4   2021-03-16 14:04:08 desktop desktop 2021-03-16 14:04:08 tablet  desktop > tablet    1
user1   step4   2021-03-16 14:04:09 tablet  desktop 2021-03-16 14:04:08     desktop >   2

Then, we use the above result as a sub-query as follows:

SELECT user_id, step, created_ts, device, dc AS device_concatenated
FROM
(
  SELECT 
    user_id, step, created_at, device, 
    FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at),
    FIRST_VALUE(created_at) OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at) AS created_ts,
    LEAD(device)            OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at),
    CONCAT(
    (FIRST_VALUE(device)   OVER (PARTITION BY user_id, step
                                 ORDER BY     user_id, step, created_at)),
    ' > ',
    (LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)))
  AS dc,
  ROW_NUMBER() OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at) AS rn
  FROM tab
  ORDER BY user_id, step, created_at
) AS t
WHERE rn = 1
ORDER BY user_id, step, device, created_ts;

Result:

user_id   step  created_ts             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 > mobile
  user1  step4  2021-03-16 14:04:08   desktop       desktop > tablet

Which is pretty close to the desired result. To obtain the desired result, we need to know how/why for step3, mobile > desktop is valid and mobile > mobile is not? Same goes for step4 - why is desktop > tablet not correct? There isn't even a single entry for mobile for step4... On receiving an explanation, I will edit as appropriate!

0
SELECT DISTINCT
       user_id,
       step,
       MIN(created_at) OVER (PARTITION BY user_id, step) created_at,
       FIRST_VALUE(device) OVER (PARTITION BY user_id, step ORDER BY created_at) 
       || ' > ' ||
       FIRST_VALUE(device) OVER (PARTITION BY user_id, step ORDER BY created_at DESC) device_concatenated 
FROM tab
ORDER BY user_id, step;

fiddle (thanks to Vérace for sample source data).

3
  • Thanks for the acknowledgement! Could also use LAST_VALUE()... but until the OP tells us exactly how he's deciding on the criteria for choosing the last one - or even the first...
    – Vérace
    Apr 8, 2021 at 16:17
  • @Vérace It seems that I cannot understand what does OP need in simply...
    – Akina
    Apr 8, 2021 at 16:22
  • The real question is: does the OP? :-)
    – Vérace
    Apr 8, 2021 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.