recorded_hour::date as date,

currency,

avg(price) as price_usd

from osmosis.core.ez_prices

where symbol = 'FLIX'

group by 1, 2

),

t1 as (

tx_id,

a.block_timestamp,

c.attribute_value as user,

d.attribute_value::int/1e6 as amount_flix,

amount_flix * price_usd as amount_usd

from osmosis.core.fact_msg_attributes a

join osmosis.core.fact_msg_attributes b

join osmosis.core.fact_msg_attributes c

join osmosis.core.fact_msg_attributes d

join t on a.block_timestamp::date = date

where a.attribute_key = 'stream_id'

and a.attribute_value = '1'

and b.attribute_key = 'action'

and b.attribute_value = 'exit_stream'

and c.attribute_key = 'sender'

and c.attribute_index = '1'

and c.msg_type = 'message'

and d.attribute_key = 'purchased'

and d.block_timestamp >= '2023-04-20'

and d.tx_succeeded = 1