jkhuhnke11Osmosis Big Project/token_accumulation
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 ins AS (
SELECT
sender AS user_address,
currency AS token_contract,
t.project_name AS token_symbol,
count(*) AS n_xfer_in,
SUM(amount / POW(10, l.decimal)) AS xfer_in_token_volume,
SUM((amount / POW(10, l.decimal))*price) AS xfer_in_usd_volume
FROM osmosis.core.fact_transfers l
INNER JOIN osmosis.core.dim_tokens t ON l.currency = t.address
INNER JOIN osmosis.core.dim_prices p ON date_trunc('hour', l.block_timestamp) = p.recorded_at
AND t.project_name = p.symbol
WHERE block_timestamp :: date >= CURRENT_DATE - 90
AND (transfer_type = 'IBC_TRANSFER_IN' OR transfer_type = 'OSMOSIS')
GROUP BY user_address, token_contract, token_symbol
),
outs AS (
SELECT
sender AS user_address,
currency AS token_contract,
t.project_name AS token_symbol,
count(*) AS n_xfer_out,
SUM(amount / POW(10, l.decimal)) AS xfer_out_token_volume,
SUM((amount / POW(10, l.decimal))*price) AS xfer_out_usd_volume
FROM osmosis.core.fact_transfers l
INNER JOIN osmosis.core.dim_tokens t ON l.currency = t.address
INNER JOIN osmosis.core.dim_prices p ON date_trunc('hour', l.block_timestamp) = p.recorded_at
AND t.project_name = p.symbol
WHERE block_timestamp :: date >= CURRENT_DATE - 90
AND transfer_type = 'IBC_TRANSFER_OUT'
GROUP BY user_address, token_contract, token_symbol
)
SELECT
user_address,
token_contract,
token_symbol,