Sajjadiiiaxl
Updated 2022-11-03
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
›
⌄
----------------------------------------------------------------------------------------------------
-- credit this part of the query :
-- @CryptoIcicle <3
-- dashboard link: https://app.flipsidecrypto.com/dashboard/jia4Pq
----------------------------------------------------------------------------------------------------
WITH base AS (
SELECT *,
regexp_substr (sender,'[a-zA-Z]+|\d+') AS sender_chain,
regexp_substr (receiver,'[a-zA-Z]+|\d+') AS receiver_chain,
sender_chain|| ' => ' || receiver_chain AS transfer_diriction,
lower (split(currency,'-')[0]) AS symbol1,
iff (symbol1 ilike 'u%',substring(symbol1,2,LEN(symbol1)), symbol1) AS symbol
FROM axelar.core.fact_transfers
WHERE transfer_type IN ('IBC_TRANSFER_IN','IBC_TRANSFER_OUT')
AND symbol = 'usdc'
AND TX_SUCCEEDED = TRUE
----------------------------------------------------------------------------------------------------
-- credit this part of query :
-- @CryptoIcicle <3
-- dashboard link: https://app.flipsidecrypto.com/dashboard/jia4Pq
----------------------------------------------------------------------------------------------------
),
final AS (
SELECT block_timestamp::date AS date , transfer_diriction ,transfer_type,
COUNT (DISTINCT tx_id) AS transactions_count ,
COUNT (DISTINCT sender) AS unique_senders_count,
COUNT (DISTINCT receiver) AS unique_receiver_count,
SUM (amount/pow(10,DECIMAL)) AS amount_USD
FROM base
GROUP BY 1,2,3
)
SELECT date , transfer_diriction , transactions_count , unique_senders_count , unique_receiver_count ,amount_USD
-- CASE WHEN transfer_type = 'IBC_TRANSFER_OUT' THEN -1 * amount_USD ELSE amount_USD END AS amount_USD
FROM final
Run a query to Download Data