How to update a date column based on proceeding order?-CodePudding

I have a table that contains three different status'

CLICKED > CLAIMED > BOUGHT (this is the correct order)

(see table below for example)

For each number_id, I need each of these status datetime to be in a separate COLUMN.

DROP TABLE TBL_A;
CREATE TABLE TBL_A
(
    number_id varchar(50), 
    deadline_date datetime, 
    status varchar,
    updated_at_datetime datetime
);

INSERT INTO TBL_A
VALUES (121144, '2021-12-30', 'clicked','2021-10-08'),
       (121144, '2021-12-30', 'claimed','2021-10-09'),
       (121144, '2021-12-30', 'bought','2021-10-10'),
       (121111, '2021-11-30', 'clicked','2021-09-08'),
       (121115, '2021-11-30', 'clicked','2021-07-08'),
       (121122, '2021-12-15', 'clicked','2021-08-09'),
       (121122, '2021-12-15', 'claimed','2021-08-10'),
       (121166, '2021-12-20', 'clicked','2021-07-09'),
       (121166, '2021-12-20', 'claimed','2021-08-09'),
       (120022, '2021-12-15', 'bought','2021-06-10'),
       (120023, '2021-12-20', 'bought','2021-06-09'),
       (120024, '2021-12-20', 'claimed','2021-06-09');
       
   
select 
NUMBER_ID,
deadline_date,
(array_agg(STATUS) within group(order by updated_at_datetime desc)[0])::varchar as last_status,
   coalesce(max(case when STATUS = 'clicked'  THEN updated_at_datetime END),'2999-12-31'::datetime) as clicked_date,
   coalesce(max(case when STATUS = 'claimed'   THEN updated_at_datetime END),'2999-12-31'::datetime) as claimed_date,
   coalesce(max(case when STATUS = 'bought' THEN updated_at_datetime END),'2999-12-31'::datetime) as bought_date
from TBL_A a
group by 1,2
order by number_id

In the last query, I need the query to have the LAST status based on the updated_at_datetime, and each of the status' update_at_datetime in their own COLUMN.

Currently, the query says if the status for that number_ID is NOT FOUND, then to default this value to = '2999-12-31'

However, now I need the query to be updated to say that IF the status for clicked is not found, to update the clicked_datetime based on the following status.

For example, IF update_at_datetime of the status CLICKED is not found, (instead of updating the clicked_datetime to '2999-12-31') then update this date to match update_at_datetime of the CLAIMED STATUS. IF the CLAIMED status is also not found, then update the clicked_datetime to match update_at_datetime of the bought STATUS.

CLICKED_DATETIME should always have a VALID date, since we can match it with the datetime of the status = 'claimed' and if status 'claimed' is also not found, update the clicked_datetime of the update_at_datetime of the status = 'bought'

IF update_at_datetime of the status of CLAIMED OR BOUGHT is not found then we can default this value to '2999-12-31'.

Can someone help me modify the last query to take this into account?

CodePudding user response：

Seems easy enough?
Just add more to the coalesce.

select NUMBER_ID, deadline_date
, (array_agg(STATUS) within group(order by updated_at_datetime desc)[0])::varchar as last_status
, coalesce(
    max(case when STATUS = 'clicked' THEN updated_at_datetime END)
  , max(case when STATUS = 'claimed' THEN updated_at_datetime END)
  , max(case when STATUS = 'reverted' THEN updated_at_datetime END)
  , '2999-12-31'::datetime
 ) as clicked_date
, coalesce(max(case when STATUS = 'claimed'   THEN updated_at_datetime END),'2999-12-31'::datetime) as claimed_date
, coalesce(max(case when STATUS = 'bought' THEN updated_at_datetime END),'2999-12-31'::datetime) as bought_date
from TBL_A a
group by number_id, deadline_date
order by number_id