WITH pricet AS (

SELECT

recorded_hour::date AS date,

symbol,

currency,

AVG(price) AS price_usd

FROM osmosis.price.ez_prices

WHERE symbol = 'OSMO'

GROUP BY 1, 2, 3

),

msgs AS (

SELECT

tx_id,

block_timestamp,

tx_from AS sender,

TRY_PARSE_JSON(attribute_value):amount/1e6 AS amount_osmo,

amount_osmo * price_usd AS amount_usd,

TRY_PARSE_JSON(TRY_PARSE_JSON(attribute_value):receiver) AS receiver,

receiver:autopilot:stakeibc:stride_address AS stride_address,

iff(fee ilike '%uosmo', replace(fee, 'uosmo', ''), 0)/1e6 AS fee_osmo,

fee_osmo * price_usd AS fee_usd

FROM osmosis.core.fact_msg_attributes

JOIN osmosis.core.fact_transactions USING (tx_id, block_timestamp)

JOIN pricet ON date = block_timestamp::date

WHERE receiver:autopilot:stakeibc:action = 'LiquidStake'

AND stride_address ILIKE 'stride%'

AND tx_succeeded = 1

),

swaps AS (

SELECT

tx_id,

block_timestamp,