Home > Enterprise >  create new sql column to get new value
create new sql column to get new value

Time:04-29

I have this transaction table how can I query to find if a person is a New Client,Returning-Member, Returning-Non-member. for instance id 1 is a new client when sequence = 1, They can only be a member/returning-member after a type(member) has been purchased for example id 1 and sequence 4 is a returning-Non-member because this is the first time they are buying a member. After that transaction seq 5 they are now a returning-member

while for id 2 never bought a member so first sequence will be new client but others will be returning-non-member until they buy a member.

Question

id Type Date Sequence
1 Member 2021-02-24 4
1 product 2021-01-03 2
2 service 2022-04-21 5
1 product 2021-02-01 3
2 service 2022-02-16 3
1 Member 2022-02-03 6
1 Service 2021-10-23 5
2 product 2022-01-03 2
1 service 2020-12-16 1
2 product 2022-03-30 4
2 service 2021-12-01 1
1 Member 2022-04-03 7

Result

id Type Date Sequence New column
1 Member 2021-02-24 4 Returning-Non-member
1 product 2021-01-03 2 Returning-Non-member
2 service 2022-04-21 5 Returning-Non-member
1 product 2021-02-01 3 Returning-Non-Member
2 service 2022-02-16 3 Returning-Non-member
1 Member 2022-02-03 6 Returning-Member
1 Service 2021-10-23 5 Returning-Member
2 product 2022-01-03 2 Returning-Non-Member
1 service 2020-12-16 1 New Client
2 product 2022-03-30 4 Returning-Non-Member
2 service 2021-12-01 1 New Client
1 Member 2022-04-03 7 Returning-Member

CodePudding user response:

You can do it in a pretty strait forward way with an additional subquery, but it's not the best way in terms of performance.

SELECT
    *,
    CASE 
        WHEN t.Sequence = 1 THEN 'New Client'
        WHEN 
            EXISTS (
                SELECT * 
                    FROM YourTable AS th 
                    WHERE 
                        th.Date < t.Date 
                        AND th.Id = t.Id 
                        AND th.Type ILIKE 'Member'
            ) THEN 'Returning-Member' 
        ELSE 'Returning-Non-Member'
    END AS Membership
    FROM YourTable AS t
  • Related