SQL to get 2 adjacent actions from the flag

Question

hope you are doing well!

I have an dummy data as below.

I want to get 2 adjacent actions from the flag by each user.

Here's the chart to describe my thought.

Here's what I want:

How can I implement SQL(I use Google Bigquery)? Hope someone can light me up. Thanks a million!

score 1 · Accepted Answer · answered Feb 04 '21 at 11:52

You seem to want lag(). I would leave the "action sequence" as two separate columns:

select user, prev_action, action, flag
from (select t.*,
             lag(action) over (partition by user order by sequence) as prev_action
      from t
     ) t
where prev_action is not null;

score 1 · Answer 2 · answered Feb 06 '21 at 02:05

Consider below option

select user, actions.action_sequence, flag  from (
  select *, (
    select as struct count(1) actions_count,
      string_agg(action, ' >> ' order by sequence) action_sequence
    from unnest(arr)
    ) actions
  from (
    select *, array_agg(struct(action, sequence)) 
      over(partition by user order by sequence desc range between current row and 1 following) arr
    from src_table
  ) 
)
where flag != '' 
and actions.actions_count = 2
# order by user, sequence

if applied to sample data in your question - output is

Note - above solution is reusable for any numbers of sequences you want to analyze - unlike the solutions in other answers which are locked to just two

In this solution - you can just change numbers (1 and 2 respectively) in below lines to whatever you need and no other changes will be required :o)

over(partition by user order by sequence desc range between current row and 1 following) arr

and

and actions.actions_count = 2

For example if you change those to respectively 2 and 3 - output will be

Thanks for your reply. I leart a lot from the answer! – LucasLee Feb 07 '21 at 10:28 — LucasLee, Feb 07 '21 at 10:28

score 0 · Answer 3 · answered Feb 04 '21 at 11:32

Try navigation function LAG:

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | NULL             |
| Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       |
| Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      |
| Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      |
| Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL             |
| Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    |
| Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  |
| Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    |
+-----------------+-------------+----------+------------------+

SQL to get 2 adjacent actions from the flag

3 Answers3

Linked