select

block_timestamp, -- time of bridge

case

when msg_value:"token":"denom"= 'uluna' then 'Luna'

when msg_value:"token":"denom"= 'uusd' then 'UST'

end as "token name" , -- name of token

msg_value:"token":"amount"/1e6 as "amount" , -- amount of tansfered

case

when msg_value:receiver ilike 'osmo%' then 'Osmosis'

when msg_value:receiver ilike 'secret%' then 'Secret'

when msg_value:receiver ilike 'axelar%' then 'Injective'

when msg_value:receiver ilike 'inj%' then 'Axelar'

end as "destination name" -- destination of birdge (one of these four)

from terra.msgs

where

block_timestamp > '2021-10-21' and -- once select min(block_timestamp) and find min date and hardcoded to just boost performance

msg_value:"@type" = '/ibc.applications.transfer.v1.MsgTransfer' and

tx_status = 'SUCCEEDED' and

(msg_value:"token":"denom" = 'uusd' or msg_value:"token":"denom" = 'uluna') and

msg_value:source_port = 'transfer' and

(msg_value:receiver ilike 'osmo%' or msg_value:receiver ilike 'secret%' or msg_value:receiver ilike 'axelar%' or msg_value:receiver ilike 'inj%' )

)

-- saimple 1 : base query to see table

select * from base_table_for_bridge limit 4

-- sample 2 : daily count and amount of daily transfered (for all network )

-- select

-- block_timestamp::date as "day", sum("amount") as "daily transfer tx Vol",sum(1) as "count of transfer tx"

-- from base_table_for_bridge

-- group by "day"

-- sample 3 : daily count and amount of daily transfered (seprate for each token and network )

-- select

-- block_timestamp::date as "day","destination name", sum("amount") as "daily transfer tx Vol",sum(1) as "count of transfer tx"