Best Query to Get Final Edge & Dept Level With Billion Transaction

Dear all

I have a scheme and sample data in below image:

My questions are:

  • Query to detect the final edge / final receiver of DEP1 is DEP4 ?
  • Query to get depth level from DEP1 to DEP4 is 3 edge (DEP2, DEP3, DEP4) ?

Anyone can help ?
Is it possible the query without loop ?
Thanks.

Regards
Muhammad Arif Nasution

Hi! I have some potential solutions. Let me know if you have questions about them:

  1. Query to detect the final edge / final receiver of DEP1 is DEP4

It is possible to do this with recursion (https://docs.tigergraph.com/gsql-ref/current/querying/operators-and-expressions#_recursive_subqueries) where the query checks if there is a next deposit node and calls itself on that next node. However, this may not be efficient as using a loop.

  1. Query to get depth level from DEP1 to DEP4 is 3 edge (DEP2, DEP3, DEP4)

If you use a sum accumulator (https://docs.tigergraph.com/gsql-ref/current/querying/accumulators#_sumaccum) that stores the depth of each deposit node as you travel to DEP4 in query 1) and adds 1 for each later node, the two questions can be solved at once.

Ex. DEP2.@depth = DEP1.@depth +1, DEP3.@depth = DEP2.@depth +1, …

Alternatively, TigerGraph has built in path finding algorithms and the single-source shortest path algorithm (https://docs.tigergraph.com/graph-ml/current/pathfinding-algorithms/single-source-shortest-path-unweighted) may be suitable for finding the shortest path from DEP1 to DEP4. To get the depth value, you can use the number of vertices in the path minus one.

See here for the code behind the algorithm: https://github.com/tigergraph/gsql-graph-algorithms/blob/master/algorithms/Path/shortest_path/tg_shortest_ss_no_wt.gsql

Hi kristine,

Thanks for the sharing, let me try and share the result.

Regards

@marifnst Found a repo with a few sample queries in that domain. Might not be ones that matches exactly what you’re trying to accomplish, but never hurts to have a few additional examples. https://github.com/TigerGraph-DevLabs/AMLSim_Python_Lab/tree/main/db_scripts/query