NavidCopy of Copy of Untitled Query
Updated 2022-12-09
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 transactions_chain as (
select
tx_id,
attribute_value as destination_chain
from
axelar.core.fact_msg_attributes
where
attribute_key = 'destinationChain'
), fee_table as (
select
date(block_timestamp) as day,
case
when attribute_value ilike '%uaxl%' then 'AXL'
when attribute_value ilike '%uusdc%' then 'USDC'
when attribute_value ilike '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then 'UST'
when attribute_value ilike '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' then 'ATOM'
when attribute_value ilike '%weth-wei%' then 'ETH'
when attribute_value ilike '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' then 'LUNA'
when attribute_value ilike '%wbtc-satoshi%' then 'WBTC'
else 'Other'
end as token,
destination_chain,
count(distinct tx_id) as tx_cnt,
sum(
case
when attribute_value ilike '%uaxl%' then cast(REPLACE(attribute_value, 'uaxl', '') as INT) / power(10, 6)
when attribute_value ilike '%uusdc%' then cast(REPLACE(attribute_value, 'uusdc', '') as INT) / power(10, 6)
when attribute_value ilike '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then cast(REPLACE(attribute_value, 'ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F', '') as INT) / power(10, 6)
when attribute_value ilike '%ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E%' then cast(REPLACE(attribute_value, 'ibc/9117A26BA81E29FA4F78F57DC2BD90CD3D26848101BA880445F119B22A1E254E', '') as INT) / power(10, 6)
when attribute_value ilike '%weth-wei%' then cast(REPLACE(attribute_value, 'weth-wei', '') as INT) / power(10, 18)
when attribute_value ilike '%ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8%' then cast(REPLACE(attribute_value, 'ibc/4627AD2524E3E0523047E35BB76CC90E37D9D57ACF14F0FCBCEB2480705F3CB8', '') as INT) / power(10, 6)
when attribute_value ilike '%wbtc-satoshi%' then cast(REPLACE(attribute_value, 'wbtc-satoshi', '') as INT) / power(10, 9)
end
) as volume
from
axelar.core.fact_msg_attributes
Run a query to Download Data