Determine status depending on date and product

Database
N/A

Determine status depending on date and product

Hi all

 

Long time viewer of this forum, have learned plenty and applied them to various projects.

 

I'm a bit stuck and couldn't find the answers so was hoping you can help me (and anyone else who wants something similar) to determine a status depending on product.

 

So my data looks like this

 

cust_id      dt_opn          Product       status
123         01/01/2015     milk
123        01/01/2015      bread
123        01/02/2015      milk
123        01/05/2015       cereal

 

The status column needs to be populated depending on the open date and product criteria,

 

For example for the above data set, the below status is correct

 

cust_id      dt_opn          Product       status
123         01/01/2015     milk              New customer
123        01/01/2015      bread          New customer
123        01/02/2015      milk             existing customer
123        01/05/2015       cereal          new product

 

so we only have three products for our work, (these are not real examples)

 

New Customer: When a customer takes a product from us for the first time they will be a new customer and no matter how many products they take on that day, they will all be determined by new customer status.

 

Existing Customer: When a customer on a different day takes a product which they have previously taken then this will be classed as exisitng customer status

 

New product: When a customer takes a product on a different day to opening and that product is not the same as any previous products then they will be classed as new product status

 

I'm hoping this makes sense to everyone. I've tried the row_number options and also coalesce but i just can't figure out how i can get this correctly determined.

 

Please any help would be much appreciated.

 

 

 


Accepted Solutions
N/A

Re: Determine status depending on date and product

You can apply your logic using a CASE plus OLAP-functions:

SELECT t.*,
   CASE
-- first day of a customer WHEN Min(dt_opn) Over (PARTITION BY cust_id) = dt_opn THEN 'New Customer'
-- first day a customer purchased a product WHEN Row_Number() Over (PARTITION BY cust_id, Product ORDER BY dt_opn) = 1 THEN 'New Product' ELSE 'Existing Customer' end FROM tab AS t

 

1 ACCEPTED SOLUTION
6 REPLIES

Re: Determine status depending on date and product

--Your question looks like an "event sourcing" data structure
--You could consider to calculate the status at the moment of insertin and insert it with the record. But that is trivial. (drawback: imagine a record is later on inserted between 2 other dates...)
--This solution "calculates" the status dynamically (e.g. in a view)

table ChainOfEvents(Cust_Id, Dt_Open, Product)
123 1 milk
123 1 bread
123 2 milk
123 3 cereal
--I've permitted myself to use an integer instead of a date, but basically solution stays the same.

SELECT T.*, COALESCE(COALESCE(NC.Status,EC.Status),'New Product') AS Status
FROM ChainOfEvents T
LEFT JOIN (
SELECT Cust_Id, MIN(Dt_Open) AS Min_Dt_Open, 'New Customer' AS Status
FROM ChainOfEvents
GROUP BY Cust_Id) NC
ON (T.Cust_Id = NC.Cust_id
AND T.DT_Open = NC.Min_Dt_Open)
LEFT JOIN (
SELECT Cust_Id, Dt_Open, 'Existing Customer' AS Status
FROM ChainOfEvents
QUALIFY row_number() over (partition by Cust_Id order by Dt_Open ASC ) = 2
GROUP BY Cust_Id, Dt_Open) EC
ON (T.Cust_Id = EC.Cust_id
AND T.DT_Open = EC.Dt_Open)
ORDER BY T.Dt_Open;

N/A

Re: Determine status depending on date and product

Thanks for that solution, i've tried it but it doesn't quite do what i need it to do.

 

in the sql you have put any products in the 2nd row as 'existing' however one customer could take 2 different products on the day they opened account so they both will be new customer.

 

If they only take one product on opening day then it is new customer, but then on another day if they take a different product which they havent taken before, then they will be 'new product'. once they have exhausted all the products or they take a product which they have already taken, then they will be 'existing'.

 

hope that makes sense.

N/A

Re: Determine status depending on date and product

You can apply your logic using a CASE plus OLAP-functions:

SELECT t.*,
   CASE
-- first day of a customer WHEN Min(dt_opn) Over (PARTITION BY cust_id) = dt_opn THEN 'New Customer'
-- first day a customer purchased a product WHEN Row_Number() Over (PARTITION BY cust_id, Product ORDER BY dt_opn) = 1 THEN 'New Product' ELSE 'Existing Customer' end FROM tab AS t

 

Re: Determine status depending on date and product

My bad. I've missread the question.

 

Dudu

N/A

Re: Determine status depending on date and product

No thats not right.

A new customer taking any product for first time would be new customer

Same customer buys a different product will be classed as New product status

Existing customer is anyone who is taking a product which they have taken before aswell.
I.e. took out milk on account opening, now has taken milk again
N/A

Re: Determine status depending on date and product

Have just tried this solution in my request and it works brilliantly. So Simple yet so effective.

 

Thanks very much for providing this solution:)