Query to write latest changed customer id

Question

Тhe customer id get updated regularly. There is a table that records this change.

Customer id, Customer’s Previous id, Customer’s Successor id
100,95,102
101,96,105
102,100,104
103,99,106
107,88,111

We need from this data for each customer id, that latest changed customer id. A sample of that from above data would be:

Customer id, Latest Customer id
100,104
101,105
102,104
103,106
107,111

you could create a table where you store the latest customer id with the last updated date — Noob Coder, Mar 02 '20 at 09:59
If you have any date column on table which gets updated. Just use order by date. — Pankaj_Dwivedi, Mar 02 '20 at 10:00
That's a question from one of our coding interview rounds :( — Hitesh Kaushal, Oct 22 '20 at 07:39

score 3 · Answer 1 · answered Mar 02 '20 at 10:08

Here is one way to do it with a recursive cte:

with cte(customer_id, next_customer_id, lvl) as (
    select customer_id, next_customer_id, 0
    from mytable t
    where not exists (select 1 from mytable t1 where t1.customer_id = t.prev_customer_id)
    union all
    select c.customer_id, t.next_customer_id, lvl + 1
    from cte c
    inner join mytable t on t.customer_id = c.next_customer_id
)
select * 
from cte c
where lvl = (select max(c1.lvl) from cte c1 where c1.customer_id = c.customer_id)
order by customer_id

The anchor of the CTE selects the records for which no "previous" record exists; then, the recursive members walks the relationship towards the "next" records. Finally, the outer query filters on the "last" value per customer.

Demo on DB Fiddle:

customer_id | next_customer_id | lvl
----------: | ---------------: | --:
        100 |              104 |   1
        101 |              105 |   0
        103 |              106 |   0
        107 |              111 |   0

Note that tuple (102,104) is not showing in the resultset, because it actually belongs to the hierarchy of customer 100, whose path is 100 > 102 > 104.

@pushpakKumar . . . A recursive CTE is the general solution to this problem. — Gordon Linoff, Mar 02 '20 at 12:13

Arun Palanisamy · Accepted Answer · 2020-03-02T12:44:21.330

0

You can also use LEFT JOIN if ID is updated only once

SELECT A.CID,COALESCE(B.SCID,A.SCID) AS LCID
FROM #TABLEA A LEFT JOIN #TABLEA B ON A.CID=B.PCID

CHECK DEMO HERE

Output

+---------------+-------------------+
| Customer ID   | Latest Customer id|
+---------------+-------------------+
| 100           | 104               |
| 101           | 105               |
| 102           | 104               |
| 103           | 106               |
| 107           | 111               |
+---------------+-------------------+

Note: Here I have refereed Customer ID as CID, Previous Customer ID as PCID & Successor Customer ID as SCID

edited Mar 02 '20 at 12:44

answered Mar 02 '20 at 10:13

Arun Palanisamy

5,281
6
28
53

1

@PushpakKumar . . . This may work for your sample data but it is not going to work if there are multiple changes over time. – Gordon Linoff Mar 02 '20 at 12:12
@GordonLinoff , Yes that's true. I was under impression that it will be updated once for each ID. now i have added the note. Thanks for pointing out ! – Arun Palanisamy Mar 02 '20 at 12:45

Query to write latest changed customer id

2 Answers2