You caught me there, it's probably too simplistic what I would do:
- you need the list with the transfer-amount+URL+timestamp1 to the bot
- plus the list with the bot upvote-percentage+URL+timestamp2
- then you create a table with the columns for URL, timestamp1, transfer-amount, upvote-percentage
- then you fill the table with the first list
- and after that you update the table by adding the 2nd list where the URL is the same and timestamp2 > timestamp1, because the transfer comes before the upvote
- finally you delete all rows that have empty cells.
Done. But again: This is the approach of a lousy SQL amateur;-)
See, the problem is not this process, which is fairly straightforward – it's generating the list of transfer amounts and URLs along with bot up votes percentage and URL. In order to generate those lists in the first place, you have to do a fair amount of ugly digging and parsing.
It's that part that's really the issue. Figuring out what the signs of those things are and extracting them.
And then you have to do it for every single bot, which means that you have a fair number of transactions that are going to have to be hitting the server in order to straighten everything out.
It's a lot of data. And ultimately – I'm not sure that it really tells us anything that we don't already know.
It might actually be more efficient to simply query the lot of all posts made over the last week and have them give their active_votes attribute up and do all of the parsing on that. If nothing else it keeps the query simple.