-- Stakes in Near-- with stakes as ( with daily as ( select date_trunc('quarter',"D") as "D1", avg("D Stakes") as "AVG D Stakes" from ( select date_trunc('day',block_timestamp) as "D", count (distinct tx_hash) as "D Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), weekly as ( select date_trunc('quarter',"W") as "W1", avg("W Stakes") as "AVG W Stakes" from ( select date_trunc('week',block_timestamp) as "W", count (distinct tx_hash) as "W Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), monthly as ( select date_trunc('quarter',"M") as "M1", avg("M Stakes") as "AVG M Stakes" from ( select date_trunc('month',block_timestamp) as "M", count (distinct tx_hash) as "M Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), quarter as ( select date_trunc('quarter',block_timestamp) as "Q1", count (distinct tx_hash) as "Q Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) Select "Q1" as "Date1", "AVG D Stakes" as "AVG Daily Stakes", "AVG W Stakes" as "AVG Weekly Stakes", "AVG M Stakes" as "AVG Monthly Stakes", "Q Stakes" as "Quarterly Stakes" from quarter join daily on "Q1"="D1" join weekly on "Q1"="W1" join monthly on "Q1"="M1"), -- Stakers in Near, i reutilize the same values as stakes only change the final values for stakers stakers as ( with daily as ( select date_trunc('quarter',"D") as "D1", avg("D Stakes") as "AVG D Stakes" from ( select date_trunc('day',block_timestamp) as "D", count( distinct tx_signer) as "D Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), weekly as ( select date_trunc('quarter',"W") as "W1", avg("W Stakes") as "AVG W Stakes" from ( select date_trunc('week',block_timestamp) as "W", count( distinct tx_signer) as "W Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), monthly as ( select date_trunc('quarter',"M") as "M1", avg("M Stakes") as "AVG M Stakes" from ( select date_trunc('month',block_timestamp) as "M", count( distinct tx_signer) as "M Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), quarter as ( select date_trunc('quarter',block_timestamp) as "Q1", count( distinct tx_signer) as "Q Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) Select "Q1" as "Date2", "AVG D Stakes" as "AVG Daily Stakers", "AVG W Stakes" as "AVG Weekly Stakers", "AVG M Stakes" as "AVG Monthly Stakers", "Q Stakes" as "Quarterly Stakers" from quarter join daily on "Q1"="D1" join weekly on "Q1"="W1" join monthly on "Q1"="M1"), -- NEAR STAKED Again i reutilize the same values only change the value for the near amount stakednear as ( with daily as ( select date_trunc('quarter',"D") as "D1", avg("D Stakes") as "AVG D Stakes" from ( select date_trunc('day',block_timestamp) as "D", sum (stake_amount/pow(10,23)) as "D Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), weekly as ( select date_trunc('quarter',"W") as "W1", avg("W Stakes") as "AVG W Stakes" from ( select date_trunc('week',block_timestamp) as "W", sum (stake_amount/pow(10,23)) as "W Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), monthly as ( select date_trunc('quarter',"M") as "M1", avg("M Stakes") as "AVG M Stakes" from ( select date_trunc('month',block_timestamp) as "M", sum (stake_amount/pow(10,23)) as "M Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) group by 1 ), quarter as ( select date_trunc('quarter',block_timestamp) as "Q1", sum (stake_amount/pow(10,23)) as "Q Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Stake' group by 1 ) Select "Q1" as "Date3", "AVG D Stakes" as "AVG Daily Staked Near", "AVG W Stakes" as "AVG Weekly Staked Near", "AVG M Stakes" as "AVG Monthly Staked Near", "Q Stakes" as "Quarterly Staked Near" from quarter join daily on "Q1"="D1" join weekly on "Q1"="W1" join monthly on "Q1"="M1"), -- UNSTAKES I CHANGE THIS TIME STAKE QUERY ONLY ACTION AND VALUES unstakes as ( with daily as ( select date_trunc('quarter',"D") as "D1", avg("D Stakes") as "AVG D Stakes" from ( select date_trunc('day',block_timestamp) as "D", count (distinct tx_hash) as "D Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), weekly as ( select date_trunc('quarter',"W") as "W1", avg("W Stakes") as "AVG W Stakes" from ( select date_trunc('week',block_timestamp) as "W", count (distinct tx_hash) as "W Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), monthly as ( select date_trunc('quarter',"M") as "M1", avg("M Stakes") as "AVG M Stakes" from ( select date_trunc('month',block_timestamp) as "M", count (distinct tx_hash) as "M Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), quarter as ( select date_trunc('quarter',block_timestamp) as "Q1", count (distinct tx_hash) as "Q Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) Select "Q1" as "Date4", "AVG D Stakes" as "AVG Daily Unstakes", "AVG W Stakes" as "AVG Weekly Unstakes", "AVG M Stakes" as "AVG Monthly Unstakes", "Q Stakes" as "Quarterly Unstakes" from quarter join daily on "Q1"="D1" join weekly on "Q1"="W1" join monthly on "Q1"="M1"), -- UNSTAKES unstakers as ( with daily as ( select date_trunc('quarter',"D") as "D1", avg("D Stakes") as "AVG D Stakes" from ( select date_trunc('day',block_timestamp) as "D", count( distinct tx_signer) as "D Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), weekly as ( select date_trunc('quarter',"W") as "W1", avg("W Stakes") as "AVG W Stakes" from ( select date_trunc('week',block_timestamp) as "W", count( distinct tx_signer) as "W Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), monthly as ( select date_trunc('quarter',"M") as "M1", avg("M Stakes") as "AVG M Stakes" from ( select date_trunc('month',block_timestamp) as "M", count( distinct tx_signer) as "M Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), quarter as ( select date_trunc('quarter',block_timestamp) as "Q1", count( distinct tx_signer) as "Q Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) Select "Q1" as "Date5", "AVG D Stakes" as "AVG Daily Unstakers", "AVG W Stakes" as "AVG Weekly Unstakers", "AVG M Stakes" as "AVG Monthly Unstakers", "Q Stakes" as "Quarterly Unstakers" from quarter join daily on "Q1"="D1" join weekly on "Q1"="W1" join monthly on "Q1"="M1"), unstakednear as ( with daily as ( select date_trunc('quarter',"D") as "D1", avg("D Stakes") as "AVG D Stakes" from ( select date_trunc('day',block_timestamp) as "D", sum (stake_amount/pow(10,23)) as "D Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), weekly as ( select date_trunc('quarter',"W") as "W1", avg("W Stakes") as "AVG W Stakes" from ( select date_trunc('week',block_timestamp) as "W", sum (stake_amount/pow(10,23)) as "W Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), monthly as ( select date_trunc('quarter',"M") as "M1", avg("M Stakes") as "AVG M Stakes" from ( select date_trunc('month',block_timestamp) as "M", sum (stake_amount/pow(10,23)) as "M Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) group by 1 ), quarter as ( select date_trunc('quarter',block_timestamp) as "Q1", sum (stake_amount/pow(10,23)) as "Q Stakes" from near.core.dim_staking_actions where block_timestamp >= '2021-10-01' and block_timestamp<'2023-01-01' and action = 'Unstake' group by 1 ) Select "Q1" as "Date6", "AVG D Stakes" as "AVG Daily Unstaked Near", "AVG W Stakes" as "AVG Weekly Unstaked Near", "AVG M Stakes" as "AVG Monthly Unstaked Near", "Q Stakes" as "Quarterly Unstaked Near" from quarter join daily on "Q1"="D1" join weekly on "Q1"="W1" join monthly on "Q1"="M1") Select Case when "Date1" = '2021-10-01' then 'a Q4 2021' when "Date1" = '2022-01-01' then 'b Q1 2022' when "Date1" = '2022-04-01' then 'c Q2 2022' when "Date1" = '2022-07-01' then 'd Q3 2022' when "Date1" = '2022-10-01' then 'e Q4 2022' end as "Quarter", "AVG Daily Stakes", "AVG Weekly Stakes", "AVG Monthly Stakes", "Quarterly Stakes", "AVG Daily Stakers", "AVG Weekly Stakers", "AVG Monthly Stakers", "Quarterly Stakers", "AVG Daily Staked Near", "AVG Weekly Staked Near", "AVG Monthly Staked Near", "Quarterly Staked Near", "AVG Daily Unstakes", "AVG Weekly Unstakes", "AVG Monthly Unstakes", "Quarterly Unstakes", "AVG Daily Unstakers", "AVG Weekly Unstakers", "AVG Monthly Unstakers", "Quarterly Unstakers", "AVG Daily Unstaked Near", "AVG Weekly Unstaked Near", "AVG Monthly Unstaked Near", "Quarterly Unstaked Near" from stakes join stakers on "Date1"="Date2" join stakednear on "Date1"="Date3" join unstakes on "Date1"="Date4" join unstakers on "Date1"="Date5" join unstakednear on "Date1"="Date6" order by 1

NEAR - 6. NEAR Foundation Transparency Report: Q3, 2022