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,

MAX(IFF(attribute_key = 'amount', attribute_value, '')) as amount_ori

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 = 'osmo1c9y7crgg6y9pfkq0y8mqzknqz84c3etr0kpcvj' OR recipient = 'osmo1c9y7crgg6y9pfkq0y8mqzknqz84c3etr0kpcvj')

),

gamm_txs_with_details AS (

SELECT

block_id,

block_timestamp,

tx_id,

IFF(sender = 'osmo1c9y7crgg6y9pfkq0y8mqzknqz84c3etr0kpcvj', 'join_pool', 'exit_pool') as type,

REGEXP_SUBSTR(amount_ori,'^\\d+') as amount,

RIGHT(amount_ori, LEN(amount_ori) - LEN(amount)) as denom

FROM gamm_amounts

HAVING denom = 'gamm/pool/{{pool_id}}'