talebimorteza_72credid2 by https://app.flipsidecrypto.com/velocity/queries/7c2e38fa-b5e8-4fea-bb15-7b670559f553
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 Table_of_user AS
(SELECT
TX_ID as tx, ATTRIBUTE_VALUE as user_ID
FROM
AXELAR.CORE.FACT_MSG_ATTRIBUTES
WHERE ATTRIBUTE_KEY = 'spender'),
M1 as
(SELECT
user_ID as users,BLOCK_TIMESTAMP::date as timestamps,a.TX_ID, a.ATTRIBUTE_VALUE
FROM
AXELAR.CORE.FACT_MSG_ATTRIBUTES a
JOIN Table_of_user b ON a.tx_id = b.tx
WHERE MSG_TYPE = 'coin_spent'
and ATTRIBUTE_INDEX = 1 and MSG_GROUP = '1:0'),
Table_of_Token as
(SELECT
timestamps,ATTRIBUTE_VALUE,TX_ID,users,
CASE WHEN ATTRIBUTE_VALUE ilike '%weth-wei%' then 'ETH'
WHEN ATTRIBUTE_VALUE ilike '%uaxl%' then 'AXL'
WHEN ATTRIBUTE_VALUE ilike '%uusd%' then 'USDC'
WHEN ATTRIBUTE_VALUE ilike '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then 'UST'
WHEN ATTRIBUTE_VALUE ilike '%wbtc-satoshi%' then 'WBTC'
ELSE 'OTHER' END AS token
FROM
M1 ),
token_Amount_Table as
(SELECT
timestamps,TX_ID,users,token,
CASE WHEN ATTRIBUTE_VALUE ilike '%uusdc%' then cast(REPLACE(attribute_value, 'uusdc', '') as INT) / power(10, 6)
WHEN ATTRIBUTE_VALUE ilike '%uaxl%' then cast(REPLACE(ATTRIBUTE_VALUE, 'uaxl', '') 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/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then cast(REPLACE(attribute_value, 'ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F', '') as INT) / power(10, 6)
WHEN ATTRIBUTE_VALUE ilike '%wbtc-satoshi%' then cast(REPLACE(attribute_value, 'wbtc-satoshi', '') as INT) / power(10, 9)
Run a query to Download Data