select case

when '{{quarter}}' = '2022 Q3' then '2022-07-01'

when '{{quarter}}' = '2022 Q4' then '2022-10-01'

when '{{quarter}}' = '2022 Q2' then '2022-04-01'

when '{{quarter}}' = '2022 Q1' then '2022-01-01'

when '{{quarter}}' = '2021 Q4' then '2021-10-01'

when '{{quarter}}' = '2021 Q3' then '2021-07-01'

end as start_date,

case

when '{{quarter}}' = '2022 Q3' then '2022-10-01'

when '{{quarter}}' = '2022 Q4' then '2023-01-01'

when '{{quarter}}' = '2022 Q2' then '2022-07-01'

when '{{quarter}}' = '2022 Q1' then '2022-04-01'

when '{{quarter}}' = '2021 Q4' then '2022-01-01'

when '{{quarter}}' = '2021 Q3' then '2021-10-01'

end as end_date

, validators as (

SELECT distinct(pool_address) as validator

from near.core.dim_staking_actions

, stake_record as (

select tx_hash

, block_timestamp

, date_trunc('day', block_timestamp) as date

, receiver_id as validator

, regexp_substr_all(to_varchar(logs), 'is(.{50})') as balance_array

, balance_array[array_size(balance_array) - 1] as dirty_amount

, regexp_substr(dirty_amount, '[0-9]+') as clean_amount

, clean_amount:: int /1e24 as total_stake

from near.core.fact_receipts as tx

inner join validators as v on tx.receiver_id = v.validator

where receipt_index = '0'

and contains(cast(logs as varchar), 'Contract total staked balance is')