jkhuhnke11Osmosis Big Project/chain_stakes
Updated 2022-12-19
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
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH delegates AS (
SELECT
delegator_address AS user_address,
'osmosis' AS protocol,
currency AS token_contract,
t.project_name AS token_symbol,
count(*) AS n_stakes,
SUM(amount / POW(10, s.decimal)) AS stake_token_volume,
SUM((amount / POW(10, s.decimal))*price) AS stake_usd_volume
FROM osmosis.core.fact_staking s
INNER JOIN osmosis.core.dim_tokens t ON s.currency = t.address
INNER JOIN osmosis.core.dim_prices p ON date_trunc('hour', s.block_timestamp) = p.recorded_at
AND t.project_name = p.symbol
WHERE s.block_timestamp :: date >= CURRENT_DATE - 90
AND action = 'delegate'
GROUP BY delegator_address, protocol, currency, token_symbol
),
undelegates AS (
SELECT
delegator_address AS user_address,
'osmosis' AS protocol,
currency AS token_contract,
t.project_name AS token_symbol,
count(*) AS n_unstakes,
SUM(amount / POW(10, s.decimal)) AS unstake_token_volume,
SUM((amount / POW(10, s.decimal))*price) AS unstake_usd_volume
FROM osmosis.core.fact_staking s
INNER JOIN osmosis.core.dim_tokens t ON s.currency = t.address
INNER JOIN osmosis.core.dim_prices p ON date_trunc('hour', s.block_timestamp) = p.recorded_at
AND t.project_name = p.symbol
WHERE s.block_timestamp :: date >= CURRENT_DATE - 90
AND action = 'undelegate'
GROUP BY delegator_address, protocol, currency, token_symbol
)
SELECT
user_address,
Run a query to Download Data