germanDelegators of OP
Updated 2022-09-28
99
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
›
⌄
⌄
/* How many users claimed optimism airdrop? How much of total airdrop was claimed?
How many wallets are still holding tokens from airdrop? How much of users delegated their OPs?
Feel free to provide any information that you think is related to token price. */
WITH claims AS (
SELECT
FROM_ADDRESS as user_c,
EVENT_INPUTS:value AS volume,
a.tx_hash,
a.BLOCK_TIMESTAMP
FROM optimism.core.fact_transactions a
JOIN optimism.core.fact_event_logs b ON a.tx_hash = b.tx_hash
WHERE TO_ADDRESS = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'
AND TOPICS[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
AND a.BLOCK_TIMESTAMP > '2022-01-01'
AND STATUS = 'SUCCESS' )
SELECT
date_trunc('day',block_timestamp) as date,
COUNT(DISTINCT DELEGATOR) as n_delegators,
SUM(n_delegators) OVER (ORDER BY date) as cum_delegators
FROM optimism.core.fact_delegations
WHERE BLOCK_TIMESTAMP > '2022-05-01'
AND DELEGATOR IN (SELECT user_c FROM claims)
AND delegation_type = 'First Time Delegator'
GROUP BY date ORDER BY date
Run a query to Download Data