SandeshTypes of stakers cummulative champs
Updated 2024-11-20
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
/*
Purpose:
This query analyzes staking activity on Arbitrum, categorizing users based on their staked amounts.
It retrieves daily counts of stakers, classified by different staking tiers, over the past month.
*/
-- Generate a list of distinct dates for the analysis period
WITH dates AS (
SELECT
DATE_TRUNC('hour', BLOCK_TIMESTAMP) AS date
FROM base.core.fact_transactions
WHERE date >= '2024-05-06'
-- AND date <= CURRENT_DATE
GROUP BY 1
),
-- Extract stake transactions from the token transfer data
stake AS (
SELECT
*,
'stake' AS action,
amount AS staked_amount
FROM base.core.ez_token_transfers
WHERE 1=1
AND block_timestamp >= '2024-05-06'
AND origin_function_signature = '0xa694fc3a'
AND contract_address = lower('0xEb6d78148F001F3aA2f588997c5E102E489Ad341')
AND to_address=lower('0x56f9341eE2d2ede918BAEAAA6D236EEcEdA44468')
),
-- Extract unstake transactions, with negative amounts to indicate withdrawal
unstake AS (
SELECT
*,
'unstake' AS action,
-amount AS staked_amount
QueryRunArchived: QueryRun has been archived