talebimorteza_72credid from https://app.flipsidecrypto.com/velocity/queries/220b5239-87e7-4542-afc4-0d1962ea4ad9
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_users 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_users 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 '%wbtc-satoshi%' then 'WBTC'
WHEN ATTRIBUTE_VALUE ilike '%ibc/6F4968A73F90CF7DE6394BF937D6DF7C7D162D74D839C13F53B41157D315E05F%' then 'UST'
ELSE 'OTHER' END AS token
FROM M1),
token_Amount_Table as
(SELECT
timestamps,TX_ID,users,token,
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 '%weth-wei%' then cast(REPLACE(attribute_value, 'weth-wei', '') as INT) / power(10, 18)
WHEN ATTRIBUTE_VALUE ilike '%wbtc-satoshi%' then cast(REPLACE(attribute_value, 'wbtc-satoshi', '') as INT) / power(10, 9)