kaibladeCopy of Daily Bridge Transactions Volume (Outflow) Group By Ecosystems
Updated 2022-11-17
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 raw_trf AS
(SELECT *, split(sender, 1) AS sender_list, split(receiver, 1) AS receiver_list,
sender_list[0] AS from_chain, receiver_list[0] AS to_chain
FROM axelar.core.fact_transfers
WHERE transfer_type != 'AXELAR'
AND tx_succeeded = TRUE
AND block_timestamp::date >= CURRENT_DATE()- INTERVAL '11 days'
ORDER BY block_timestamp DESC
),
combined_data AS
(SELECT trf.block_timestamp, trf.tx_id, trf.currency, trf.decimal, trf.sender, trf.amount, dim.msg_type, dim.msg_index
,dim.attribute_key,dim.attribute_value
FROM raw_trf trf
JOIN axelar.core.fact_msg_attributes dim
ON trf.tx_id = trf.tx_id
WHERE attribute_value ILIKE '%ibc/%'
AND attribute_key = 'denom'
ORDER BY block_timestamp DESC, tx_id)
-- labelled_trf AS
-- (SELECT trf.block_timestamp, trf.tx_id, trf.currency, trf.decimal, trf.amount, trf.msg_index, trf.attribute_value,
-- label.label, label.label_subtype, label.label_type, label.address_name
-- FROM combined_data trf
-- LEFT JOIN axelar.core.dim_labels label
-- ON trf.attribute_value = label.address
-- )
SELECT DATE_TRUNC('day', block_timestamp) AS "Days",
to_chain,
COUNT(DISTINCT tx_id) AS "Transaction Volume"