CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

Return the second most recent value in a specific windows time frame for each individual row

Imagine the following table : 

UserName    paymentType      date(YYYY-MM-DD)          Status   
x           transaction      2016-09-16                1         
x           UpdateStatus     2016-10-22                2        
x           transaction      2017-01-08                2        
x           transaction      2017-06-12                2         
x           UpdateStatus     2017-08-27                3
x           transaction      2018-11-08                3
x           transaction      2018-11-08                3

I would like to be able to query the list of all UpdateStatus paymentType event with the previous status associated for each status update event. Something like this : 

UserName  paymentType    date(YYYY-MM-DD)     Status    Previous_status          
x         UpdateStatus   2016-10-22           2         1       
x         UpdateStatus   2017-08-27           3         2

I know that I need to use a sub query to query the whole table first them filtering only where paymentType='UpdateStatus' for the global query. However I don't how I can get the previous status associated with each status update. I think I need to use a windows time frame function somewhere. Basically the previous_status need to be the last status where date is inferior to the UpdateStatus date event for each row.

Here is the query I was able to build so far... 

SELECT
  UserName,
  paymentType,
  date,
  status,
  Sub.Previous_status
FROM
  `Payment_table` as p
JOIN (
  SELECT 
  UserName,
  < get the Previous_status here > as previous_status 
  FROM
  `Payment_table` as s
  ) AS Sub ON 
  p.UserName=sub.UserName
WHERE
  paymentType = 'UpdateStatus'

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use lag():

select pt.*
from (select pt.*, lag(status) over (order by date) as prev_status
      from payment_table pt
     ) pt
where paymentType = 'UpdateStatus';

You need to do the lag() in the subquery, so it is not affected by the filter.

Upvotes: 2

Related Questions