CODEWITHSUNDEEP
sqlgoogle-bigquerypartition-by
allamirope
allamirope

Reputation: 31

SQL/BigQuery: return only the last occurence

I have a table about conversations and different actions for each of them.

conv_id action_id action date
1 11 message 10:00
1 12 message 10:05
1 13 close 10:10
2 21 message 10:00
2 22 message 10:05
2 23 close 10:10
2 24 message 10:15
3 31 message 10:00
3 32 message 10:05
3 33 close 10:10
3 34 something else 10:15

How can I select the last action of each conversation and keep it in a new column? I am only interested in actions “message” and “close”, the others can be ignored. So the expected outcome will look like this:

conv_id action_id action date state
1 11 message 10:00 closed
1 12 message 10:05 closed
1 13 close 10:10 closed
2 21 message 10:00 open
2 22 message 10:05 open
2 23 close 10:10 open
2 24 message 10:15 open
3 31 message 10:00 closed
3 32 message 10:05 closed
3 33 close 10:10 closed
3 34 something else 10:15 closed

A few details that I was struggling to include in my code:

here is the code to get the table:

-- creating a table
CREATE TABLE conversations (
  conv_id INTEGER NOT NULL,
  action_id INTEGER PRIMARY KEY,
  action TEXT NOT NULL,
  date DATETIME NOT NULL
);
-- inserting some values
INSERT INTO conversations VALUES (1,11,'message','10:00');
INSERT INTO conversations VALUES (1,12,'message','10:05');
INSERT INTO conversations VALUES (1,13,'close','10:10');
INSERT INTO conversations VALUES (2,21,'message','10:00');
INSERT INTO conversations VALUES (2,22,'message','10:05');
INSERT INTO conversations VALUES (2,23,'close','10:10');
INSERT INTO conversations VALUES (2,24,'message','10:15');
INSERT INTO conversations VALUES (3,31,'message','10:00');
INSERT INTO conversations VALUES (3,32,'message','10:05');
INSERT INTO conversations VALUES (3,33,'close','10:10');
INSERT INTO conversations VALUES (3,34,'something else','10:15');

Thank you!

Upvotes: 0

Views: 296

Answers (1)

Jaytiger
Jaytiger

Reputation: 12254

USING LAST_VALUE window function,

SELECT *, 
       CASE LAST_VALUE(IF(action IN ('message', 'close'), action, NULL) IGNORE NULLS) OVER w
         WHEN 'close' THEN 'closed'
         WHEN 'message' THEN 'open'
       END AS state  
  FROM conversations
WINDOW w AS (PARTITION BY conv_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
 ORDER BY conv_id, date;

Query results:

conv_id action_id action date state
1 11 message 10:00 closed
1 12 message 10:05 closed
1 13 close 10:10 closed
2 21 message 10:00 open
2 22 message 10:05 open
2 23 close 10:10 open
2 24 message 10:15 open
3 31 message 10:00 closed
3 32 message 10:05 closed
3 33 close 10:10 closed
3 34 something else 10:15 closed

Upvotes: 2

Related Questions