kaibladeCopy of Daily Bridge Transactions Volume (Outflow) Group By Ecosystems
    Updated 2022-11-17
    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"