SELECT t.block_timestamp, LIQUIDITY_PROVIDER_ADDRESS, t.tx_id, action, pool_id, AMOUNT / POW(10, decimal) as amount, PROJECT_NAME as symbol, a.ATTRIBUTE_VALUE as gamm

FROM osmosis.core.fact_liquidity_provider_actions t INNER JOIN osmosis.core.FACT_MSG_ATTRIBUTES a ON t.tx_id = a.tx_id

INNER JOIN osmosis.core.dim_labels l ON t.currency = l.address

WHERE t.block_id >= 4707300 and currency = 'uosmo' and TX_STATUS = 'SUCCEEDED' and a.MSG_TYPE = 'transfer' and a.attribute_key = 'amount'

and CONTAINS(a.ATTRIBUTE_VALUE, 'gamm')

, lag_tx_osmo as (

SELECT block_timestamp, LIQUIDITY_PROVIDER_ADDRESS, tx_id, symbol, action, lag(action, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_action,

datediff('seconds', lag(block_timestamp, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC), block_timestamp) as time_between,

pool_id, lag(pool_id, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_pool_id,

AMOUNT, lag(AMOUNT, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_amount, ((amount / prev_amount) - 1) * 100 as pct_profit, amount - prev_amount as tokens_gained,

gamm, lag(gamm, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_gamm

FROM relevant_txs_osmo

, relevant_txs_nonosmo as (

SELECT t.block_timestamp, LIQUIDITY_PROVIDER_ADDRESS, t.tx_id, action, pool_id, AMOUNT / POW(10, decimal) as amount, PROJECT_NAME as symbol, a.ATTRIBUTE_VALUE as gamm

FROM osmosis.core.fact_liquidity_provider_actions t INNER JOIN osmosis.core.FACT_MSG_ATTRIBUTES a ON t.tx_id = a.tx_id

INNER JOIN osmosis.core.dim_labels l ON t.currency = l.address

WHERE t.block_id >= 4707300 and currency <> 'uosmo' and TX_STATUS = 'SUCCEEDED' and a.MSG_TYPE = 'transfer' and a.attribute_key = 'amount'

and CONTAINS(a.ATTRIBUTE_VALUE, 'gamm')

, lag_tx_nonosmo as (

SELECT block_timestamp, LIQUIDITY_PROVIDER_ADDRESS, tx_id, symbol, action, lag(action, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_action,

datediff('seconds', lag(block_timestamp, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC), block_timestamp) as time_between,

pool_id, lag(pool_id, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_pool_id,

AMOUNT, lag(AMOUNT, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_amount, ((amount / prev_amount) - 1) * 100 as pct_profit, amount - prev_amount as tokens_gained,

gamm, lag(gamm, 1) over (partition by LIQUIDITY_PROVIDER_ADDRESS order by block_timestamp ASC) as prev_gamm

FROM relevant_txs_nonosmo

, combine as (