CODEWITHSUNDEEP
sqlamazon-redshift
user12562215
user12562215

Reputation: 145

SQL: Find the status change date for a user in a given time period

I have two tables: Table A has today's data and Table B has the historical snapshot of the data captured on a daily basis.

**Table A**
User  Status  Date (which is current_date) 
A1    A       12/23/2019
B1    B       12/23/2019

**Table B**
User  Status  Date
A1    B       12/20/2019
A1    A       12/21/2019
A1    B       12/22/2019
A1    A       12/23/2019
B1    A       12/20/2019
B1    B       12/21/2019
B1    A       12/22/2019
B1    B       12/23/2019

What I am looking for are: in 2019

  1. For users who are currently on status B, what are the dates when the status was changed to B from A (as users can switch between status multiple times so I want all dates when that happened, if the change happened in 2019)
  2. For users who are currently on status B, what is the most recent date the status was changed to B from A (a subset of users from list above)
  3. For users who are currently on status A and was on status B earlier this year, what is the date when it was changed to B (if the user was A before B) and when it was changed to A

Thanks!

Upvotes: 0

Views: 358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Basically, you want lag() and last_value(). Table A does not seem necessary.

The basic logic to get what you want is:

select b.*
from (select b.*,
             last_value(status) over (partition by user order by date) as last_status,
             lag(status) over (partition by user order by date) as prev_status
      from b
     ) b
where date >= '2019-01-01' and
      date < '2020-01-01' and
      ((last_status = 'B' and status = 'B' and prev_status = 'A') or
       (last_status = 'B' and status = 'B' and prev_status = 'A') or
       (last_status = 'A' and status = 'A' <> prev_status)
      )

Upvotes: 1

Related Questions