Not sure if I’m headed in the right direction, just sharing what I’ve come across so far.
It seems that the solution may be affected by where I store the data. I’m leaning towards this perspective based on how others solved similar problems using Google sheets see: Tutorial - QUERY: "The most powerful function" in Google Sheets (part 1) - #4 by ThinhDinh
Maybe something similar can be done with airtable. I’m not sure if I can pull this off using glide tables. If I hit a deadend, I’ll switch my backend to support the use case.