When customer_status is 1 and not more blank select the previous customer_tag value and create a new column showing the previous tag

Sample table

date         customer_id  customer_status  customer_tag
2020-01-01   9493                          inactive_customer
2020-01-02   9493                          inactive_customer
2020-01-03   9493                          inactive_customer
2020-01-04   9493         1                active_customer
2020-01-05   9493         1                active_customer

Desired output table

date         customer_id  customer_status  customer_tag          previous_customer_tag
2020-01-01   9493                          inactive_customer     
2020-01-02   9493                          inactive_customer
2020-01-03   9493                          inactive_customer
2020-01-04   9493         1                active_customer       inactive_customer
2020-01-05   9493         1                active_customer       inactive_customer

CodePudding user response：

I made the following assumptions:

1. You need to update your existing table accordingly and don't just need a SELECT statement here.
2. customer_status column can be NULL.
3. previous_customer_tag column can be NULL.

Furthermore, I changed the name of date column to customer_date because date is a keyword.

Try this:

ALTER TABLE CUSTOMERS
ADD COLUMN previous_customer_tag varchar(255) NULL;

UPDATE CUSTOMERS 
SET previous_customer_tag = CASE WHEN customer_status = 1 THEN 'inactive_customer' ELSE NULL END;

Here is the complete solution implemented by me. I hope it helps. Please mark this answer as correct if it helps you. Thanks!

CodePudding user response：

You can use lag window function and conditionally use it's value as previous_customer_tag with if:

-- sample data
WITH dataset ( date, customer_id, customer_status, customer_tag) AS (
    VALUES
(date '2020-01-01', 9493, null, 'inactive_customer'),
(date '2020-01-02', 9493, null, 'inactive_customer'),
(date '2020-01-03', 9493, null, 'inactive_customer'),
(date '2020-01-04', 9493, 1, 'active_customer'),
(date '2020-01-05', 9493, 1, 'active_customer')
) 

--query
select *,
    if(
        customer_status = 1,
        lag(customer_tag) over (partition by customer_id order by date)
    ) previous_customer_tag
from dataset

Output: