Holding rewards query optimisations #2286

Ticket(s) / Related Merge Requests

• Issue card: #2286 (closed)
• Helm: helm-charts!60 (closed)

Summary of Changes

Implement BigQuery holding rewards calculations.

Testing Considerations

To be tested via CLI.

# Calculate
php cli.php rewards calculate

# Time and count results for ALL holders regardless of if they are registered on site
php cli.php BigQuery getHolders

# Time and count results for all holders who ARE registered on-site.
php cli.php BigQuery getHolders --onlyRegistered=true

# Verbose flag can be used on either of the `BigQuery getHolders` commands to output the rows
php cli.php BigQuery getHolders --onlyRegistered=true --verbose=true

Note, when deploying it needs the helm map with key and project_id populated for BigQuery, and we also need to be using production config for blockchain, so that we use mainnet, WITH ALCHEMY.

Deployment Considerations

Needs to be deployed to runners, should be monitored closely for timing in prod, and to ensure the addresses we're calculating for are coming back correct. Note this is behind feature flag engine-2966-holding-rewards, which is currently ON.

Would be good to dry run the calculations and check how many users were paid out to ensure none have been missed. We can check how many rows are iterated over using the commands above - the rewards manager should use the same amount of rows as returned from the below command - as this does the mapping and checks to whether the address is registered on-site. See other commands above in testing steps for additional commands to help check things are working correctly.

php cli.php BigQuery getHolders --onlyRegistered=true --verbose=true

Needs also:

• a valid key file putting on the path (help with generation)
• The config value setting as can be seen in settings.example.php

Regression Scope

Affects reward calculations.

Platform Affected (web, mobile, etc)

Engine-side change.

Developer Testing Completed

Manual and specs.

Screenshots / Screen Recording

Calculate

All holders

Only registered holders

Only registered holders verbose

Does this impact

• Localization
• Dark/light mode
• Guest mode

Definition of Done Checklist

• The Acceptance Criteria has been met
• Code is tested: Testing includes unit/spec, E2E/automated and manual testing
• Merge requests description has been filled out