CODEWITHSUNDEEP
sqlgoogle-bigquery
user2101699
user2101699

Reputation: 257

SQL group only following rows

I have a table with the following fields:

I wanna get a list of the gates that the users (user_id, gate_id) have passed ordered by time. buy sometimes a user passes through a gate multiple times one right after the other. I wanna ignore these cases.

for example if we have:

user_id gate_id
1 13
1 13
1 11
1 13

it will ignore only the following duplicated and return:

user_id gate_id
1 13
1 11
1 13

How can i do such thing? Thanks!

Upvotes: 0

Views: 68

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220902

Use the LAG window function to find the previous gate_id per user_id ordered by your time column, and then filter out the undesired ones:

SELECT user_id, gate_id
FROM (
  SELECT 
    user_id, 
    gate_id, 
    lag(gate_id) OVER (
      PARTITION BY user_id 
      ORDER BY time
    ) AS previous_gate_id
  FROM t
) t
WHERE previous_gate_id IS NULL OR previous_gate_id <> gate_id

Upvotes: 2

Related Questions