misaghlbmetamask bridge
Updated 2022-12-06
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
›
⌄
with prices as (
SELECT date(HOUR) as pdate, symbol, decimals, avg(price) as avg_price
from ethereum.core.fact_hourly_token_prices
GROUP by pdate, symbol, decimals
),
raw_eth as (
select 'Ethereum' as blockchain,
BLOCK_TIMESTAMP,
SYMBOL,
t1.FROM_ADDRESS,
AMOUNT_USD as vol
from ethereum.core.ez_token_transfers t1
where t1.block_number >=15741181 and t1.ORIGIN_TO_ADDRESS=lower('0x82E0b8cDD80Af5930c4452c684E71c861148Ec8A')
and t1.BLOCK_TIMESTAMP>='2022-10-13'
),
raw_poly as (
select 'Polygon' as blockchain,
BLOCK_TIMESTAMP,
case when contract_address='0x2791bca1f2de4661ed88a30c99a7a9449aa84174' then 'USDC'
when contract_address='0xc2132d05d31c914a87c6611c10748aeb04b58e8f' then 'USDT'
when contract_address='0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' then 'DAI'
when contract_address='0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' then 'WETH'
when contract_address='0x2c89bbc92bd86f8075d1decc58c7f4e0107f286b' then 'WAVAX'
when contract_address is null then 'MATIC' end as symbol,
t1.FROM_ADDRESS,
RAW_AMOUNT as vol
from polygon.core.fact_token_transfers t1
where t1.block_number >=15741181 and t1.ORIGIN_TO_ADDRESS=lower('0x82E0b8cDD80Af5930c4452c684E71c861148Ec8A')
and t1.BLOCK_TIMESTAMP>='2022-10-13'
),
raw_avalanche as (
select 'Avalanche' as blockchain,
BLOCK_TIMESTAMP,
symbol,
t1.FROM_ADDRESS,
RAW_AMOUNT as vol
from avalanche.core.fact_token_transfers t1
join crosschain.core.dim_asset_metadata l on contract_address = TOKEN_ADDRESS
where t1.block_number >=15741181 and t1.ORIGIN_TO_ADDRESS=lower('0x82E0b8cDD80Af5930c4452c684E71c861148Ec8A')
and t1.BLOCK_TIMESTAMP>='2022-10-13'
),
raw_bsc as (
select 'BSC' as blockchain,
BLOCK_TIMESTAMP,
symbol,
t1.FROM_ADDRESS,
RAW_AMOUNT as vol
from bsc.core.fact_token_transfers t1
join crosschain.core.dim_asset_metadata l on contract_address = TOKEN_ADDRESS
where t1.block_number >=15741181 and t1.ORIGIN_TO_ADDRESS=lower('0x82E0b8cDD80Af5930c4452c684E71c861148Ec8A')
and t1.BLOCK_TIMESTAMP>='2022-10-13'
)
SELECT date(BLOCK_TIMESTAMP) as date, symbol,
COUNT(DISTINCT from_address) as wallets,
sum(vol) as vol
from raw_eth
GROUP by date, symbol
UNION
SELECT date(BLOCK_TIMESTAMP) as date, a.symbol,
COUNT(DISTINCT from_address) as wallets,
sum(vol/pow(10, decimals) * avg_price) as vol
from raw_poly a
join prices p on a.symbol = p.symbol and date(BLOCK_TIMESTAMP) = pdate
GROUP by date, a.symbol
UNION
SELECT date(BLOCK_TIMESTAMP) as date, a.symbol,
COUNT(DISTINCT from_address) as wallets,
sum(vol/pow(10, decimals) * avg_price) as vol
from raw_avalanche a
join prices p on a.symbol = p.symbol and date(BLOCK_TIMESTAMP) = pdate
GROUP by date, a.symbol
UNION
SELECT date(BLOCK_TIMESTAMP) as date, a.symbol,
COUNT(DISTINCT from_address) as wallets,
sum(vol/pow(10, 18) * avg_price) as vol
from raw_bsc a
join prices p on a.symbol = p.symbol and date(BLOCK_TIMESTAMP) = pdate
GROUP by date, a.symbol
Run a query to Download Data