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