Stake added within interval (OciSwap staking challenge)

query.sql

-- Validators leaderboard
WITH validator_data AS (
    SELECT t.validator_id, t.name
    FROM public.validator_data_substates t
    WHERE name IS NOT NULL
      AND down_state_version IS NULL
    ORDER BY t.validator_id
),
staked_amounts AS (
    SELECT v.name, t.inferred_action_amount / pow(10, 18) as amount
     FROM operation_groups t
     JOIN validator_data v ON (v.validator_id = t.inferred_action_validator_id)
     JOIN ledger_transactions lt on t.state_version = lt.state_version
     WHERE inferred_action_type = 'STAKE_TOKENS'
     AND lt.round_timestamp >= '2022-07-21 19:00:00 +00:00' AND  lt.round_timestamp < '2022-07-23 19:00:00 +00:00'
     ORDER BY t.state_version desc
              )

SELECT 'TOTAL' as name, to_char(SUM(amount), '999G999G999D9') as amount, count(*) as stakes
FROM staked_amounts t
UNION ALL
(select name, to_char(SUM(amount), '999G999G999D9'), count(*)
 from staked_amounts
 group by name
 order by sum(amount) desc
 );

-- Added Stake per Validator
WITH stake_actions AS (
    SELECT a.address,  t.inferred_action_amount / pow(10, 18) as amount
     FROM operation_groups t
     JOIN ledger_transactions lt on t.state_version = lt.state_version
     JOIN accounts a on t.inferred_action_from_account_id = a.id
     WHERE inferred_action_type = 'STAKE_TOKENS'
       AND t.inferred_action_validator_id = (SELECT validator_id FROM validator_data_substates WHERE name = '🐱 Ocinode' AND down_state_version IS NULL)
     AND lt.round_timestamp >= '2022-07-21 19:00:00 +00:00' AND  lt.round_timestamp < '2022-07-23 19:00:00 +00:00'
     ORDER BY t.state_version desc
)
SELECT address, to_char(SUM(amount), '999G999G999D9') as amount from stake_actions
GROUP BY address
ORDER BY SUM(amount) desc