In thinking more about this one, one thing I wanted to pass along was a recent conversation I had with @dsiffredi on a similar type of problem where the initial data transformation made all the difference in the ultimate solution. I find the best place to start on challenging problems like this one is by considering “what would be the ideal organization/structure of this data to answer the question being asked?” . Like Daniel’s problem below, we started with a wide and short structure (lots of columns, relatively few rows), and ended up with a narrow and long structure (fewer columns, more records), which will often be the right direction in which to head.
Anyway, just some food for thought as you work through the solution…
- Brian