How to trace back a record all the way to origin using SQL

Question

We are a table called ticketing that tracks all the service tickets. One ticket can lead to another ticket which leads to another ticket indicated by the replaced_by_ticket_id field below

| ticket_id | is_current | replaced_by_ticket_id |
|-----------|------------|-----------------------|
| 134       | 0          | 240                   |
| 240       | 0          | 321                   |
| 321       | 1          | Null                  |
| 34        | 0          | 93                    |
| 25        | 0          | 16                    |
| 16        | 0          | 25                    |
| 93        | 1          | Null                  |

How do I write a query to get the number of tickets leading to the current ones (321 & 93)? I mean I could join the table by itself, but there is no way of knowing how many times to join. Plus different tickets have different number of levels.

Here is the expected result of the query

| ticket_id | total_tickets |
|-----------|---------------|
| 321       | 3             |
| 93        | 4             |

What is the best way to do it?

Answer 1

You can use a recursive query; the trick is to keep track of the original "current" ticket, so you can aggregate by that in the outer query.

So:

with cte as (
    select ticket_id, ticket_id as parent_id from ticketing where is_current = 1
    union all
    select c.ticket_id, t.ticket_id
    from ticket t
    inner join cte c on c.parent_id = t.replaced_by_ticket_id
)
select ticket_id, count(*) total_tickets
from cte
group by ticket_id