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,

transfer_diriction || ' : Symbol -> '|| symbol AS Transfer_dirictions_with_symbol

FROM axelar.core.fact_transfers

WHERE transfer_type IN ('IBC_TRANSFER_OUT' , 'IBC_TRANSFER_IN')

AND TX_SUCCEEDED = TRUE

),

datecalc_sender as (

select a.sender,a.Transfer_dirictions_with_symbol,a.transfer_diriction,

datediff(day, a.block_timestamp, b.block_timestamp) as difference

from base a

left join base b

on a.sender = b.sender

and a.tx_id <> b.tx_id

and b.block_timestamp > a.block_timestamp

),

final AS (

select

case

when difference between 1 and 7 then 'Daily'

when difference between 7 and 30 then 'Weekly'

when difference between 30 and 90 then 'Monthly'

when difference between 90 and 364 then 'Quarterly'

when difference >= 365 then 'Yearly'

else 'one-time Or Unstable streams'

end as actions,Transfer_dirictions_with_symbol,transfer_diriction,

count (distinct sender) as senders,

row_number() over (order by senders desc) as rank