How to compare dates of two different columns for different records?

user_3461502 0
Viewed 308

I have a table that contains two different status per user, "claimed" and "returned". I want to compare the 'status_datetime_update' date of the 'returned' status and check if it is greater or less than the expiration_datetime of the previous status ('claimed').

I know transaction_id 1111 and 1244 are less than and transaction_id 3312 is greater than.

Is there a way I can do this?

enter image description here

create or replace table TBL_A (
    user_name varchar, country varchar, status varchar, transaction_id varchar, status_datetime_update date, expiration_datetime date
);
insert into TBL_A values 
('Rachel', 'USA', 'CLAIMED','1111', '2021-10-10', '2021-10-15'),
('Rachel', 'USA', 'RETURNED','1111', '2021-10-14','2021-10-15'),
('Ross', 'PAN', 'CLAIMED','3312','2021-10-12','2021-10-20'),
('Ross', 'PAN', 'RETURNED','3312', '2021-10-25','2021-10-20'),
('Chandler', 'UK', 'CLAIMED','1244','2021-09-11','2021-09-14'),
('Chandler', 'UK', 'RETURNED', '1244','2021-09-12','2021-09-14');
snowflake-schema snowflake-cloud-data-platform sql
1 Answers

Something like this?

select *, 
LAG( expiration_datetime ) OVER (PARTITION BY transaction_id ORDER BY status) <= status_datetime_update expired
from tbl_a;

By the way, your rows already contain the same expiration_datetime value. Why not to use it?

user_125509650
Related