CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Patthebug
Patthebug

Reputation: 4787

Get the next record after a particular value in a column in Amazon Redshift

I have some data which is as follows:

Session_id,Created_at, pagetype
1,2018-08-08,A
1,2018-08-08,B
1,2018-08-08,C
1,2018-08-08,A
1,2018-08-08,D
1,2018-08-08,A
2,2018-08-18,D
2,2018-08-18,C
2,2018-08-18,A
2,2018-08-18,B
2,2018-08-18,B

I would like to fetch the current row and the next row whenever the value in the column Pagetype is A, in any given session. I would like the current row even when there's no next row in the current session. So the resultset would look like this:

Session_id,Created_at, pagetype
1,2018-08-08,A
1,2018-08-08,B
1,2018-08-08,A
1,2018-08-08,D
1,2018-08-08,A
2,2018-08-18,A
2,2018-08-18,B

I would share an initial query to do this, but I have no idea on how to start this. I'm sure there are some window functions available to to this job.

Any help would be much appreciated.

Upvotes: 0

Views: 858

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use lag to get the previous row's value. (The assumption is created_at is a datetime type, otherwise use a different column to break ties to get stable results)

select *
from (select t.*,lag(pagetype) over(partition by session_id order by created_at) as prev_pagetype
      from tbl t
     ) t 
where pagetype = 'A' or (prev_pagetype = 'A' and pagetype <> 'A')

Upvotes: 3

Related Questions