SELECT

block_id,

block_timestamp,

tx_id,

msg_index,

MAX(IFF(attribute_key = 'sender', attribute_value, '')) as sender,

MAX(IFF(attribute_key = 'recipient', attribute_value, '')) as recipient,

SPLIT(MAX(IFF(attribute_key = 'amount', attribute_value, '')), ',') as amount_oris

FROM osmosis.core.fact_msg_attributes a

WHERE EXISTS (

SELECT 1

FROM osmosis.core.fact_msgs m

WHERE tx_status = 'SUCCEEDED'

AND a.tx_id = m.tx_id

)

AND msg_type = 'transfer'

AND msg_group is not null

GROUP BY

block_id,

block_timestamp,

tx_id,

msg_index

HAVING (sender = '{{pool_address}}' OR recipient = '{{pool_address}}')

),

transfers AS (

SELECT

block_id,

block_timestamp,

tx_id,

IFF(sender = '{{pool_address}}', 'out', 'in') as type,

REGEXP_SUBSTR(F.Value,'^\\d+') as amount,

RIGHT(F.Value, LEN(F.Value) - LEN(amount)) as denom

FROM transfers_in_arrays,

LATERAL FLATTEN (input => amount_oris) F