NavidCopy of Untitled Query
Updated 2022-11-18
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 token_prices as (
select
date(hour) as day,
TOKEN_ADDRESS,
max(decimals) as decimals,max(SYMBOL) as SYMBOL,
avg(PRICE) as usd
from
ethereum.core.fact_hourly_token_prices
where price<1e20
group by day,TOKEN_ADDRESS
)
,ftx_adds as (select address from ethereum.core.dim_labels
where label_type='cex' and label='ftx')
, eth_outcomes as (
select date_trunc('day', BLOCK_TIMESTAMP) as day, ETH_TO_ADDRESS as receiver, ifnull(AMOUNT,0) as volume, ifnull(AMOUNT_USD,0) as volume_usd, TX_HASH
from ethereum.core.ez_eth_transfers
where ETH_FROM_ADDRESS in (select address from ftx_adds) and ETH_TO_ADDRESS not in (select address from ftx_adds)
)
, token_outcomes as (
select date_trunc('day', BLOCK_TIMESTAMP) as day, TO_ADDRESS as receiver, ifnull(RAW_AMOUNT/pow(10,b.DECIMALS),0) as volume, ifnull(RAW_AMOUNT/pow(10,b.DECIMALS)*b.usd,0) as volume_usd, TX_HASH, b.SYMBOL
from ethereum.core.fact_token_transfers a left join token_prices b on a.CONTRACT_ADDRESS=b.TOKEN_ADDRESS and date(BLOCK_TIMESTAMP)=b.day
where FROM_ADDRESS in (select address from ftx_adds) and TO_ADDRESS not in (select address from ftx_adds)
), eth_incomes as (
select date_trunc('day', BLOCK_TIMESTAMP) as day, ETH_TO_ADDRESS as receiver, ifnull(AMOUNT,0) as volume, ifnull(AMOUNT_USD,0) as volume_usd, TX_HASH
from ethereum.core.ez_eth_transfers
where ETH_FROM_ADDRESS not in (select address from ftx_adds) and ETH_TO_ADDRESS in (select address from ftx_adds)
), token_incomes as (
select date_trunc('day', BLOCK_TIMESTAMP) as day, TO_ADDRESS as receiver, ifnull(RAW_AMOUNT/pow(10,b.DECIMALS),0) as volume, ifnull(RAW_AMOUNT/pow(10,b.DECIMALS)*b.usd,0) as volume_usd, TX_HASH, b.SYMBOL
from ethereum.core.fact_token_transfers a left join token_prices b on a.CONTRACT_ADDRESS=b.TOKEN_ADDRESS and date(BLOCK_TIMESTAMP)=b.day
where FROM_ADDRESS not in (select address from ftx_adds) and TO_ADDRESS in (select address from ftx_adds)
), outcomes as (
select day, receiver, volume, volume_usd, TX_HASH, 'ETH' as SYMBOL
from eth_outcomes
union all
select day, receiver, volume, volume_usd, TX_HASH, SYMBOL
from token_outcomes
Run a query to Download Data