[ktamura]

The suggested approach most likely requires a lot of recursive backtracking. Of course, there's an efficient way to implement this, and that's what most commercial databases' path analytics features do. Here's one example by Oracle: https://docs.oracle.com/database/121/DWHSG/pattern.htm

I've always found it befuddling why so many developers want to use Solr/Elasticsearch for analytics heavylifting. It's probably because

1. SQL is not the most intuitive (although most pervasive) API for data analysis

2. Much of the data is already in Solr/Elasticsearch to make your data searchable/perform simple roll-ups and filtering, etc., so it'd be great if you can do more complex analytics against them as well

AS to why Solr/Elasticsearch is not ideal: the existence of superior alternatives that is OLAP databases.