recorded_hour::date as date,

currency,

symbol,

avg(price) as price_usd

from osmosis.core.ez_prices

group by 1, 2, 3

),

t2 as (

block_timestamp,

when block_timestamp < '2022-04-01' then '1. Winter, 2022'

when block_timestamp < '2022-07-01' then '2. Spring, 2022'

when block_timestamp < '2022-10-01' then '3. Summer, 2022'

when block_timestamp < '2023-01-01' then '4. Autumn, 2022'

else '5. Winter, 2023'

end as timespan,

when fee like '%ibc/%' then (substring(fee, 0, position('ibc/' in fee) -1)::int)/pow(10, iff(t.symbol = 'LIKE', 9, 6))

when fee like '%uosmo' then replace(fee, 'uosmo', '')::int/1e6

when fee like '%uion' then replace(fee, 'uion', '')::int/1e6

end) * t2.price_usd as tx_fee_usd,

tx_id,

(amount * t.price_usd) / pow(10, decimal) as amount_usd,

delegator_address as user

from osmosis.core.fact_staking a

join osmosis.core.fact_transactions

using (tx_id)

join t on a.currency = t.currency and a.block_timestamp::date = t.date

join t t2 on t2.date = a.block_timestamp::date and t2.currency = (case

when fee like '%ibc/%' then substring(fee, position('ibc/' in fee), length(fee))

when fee like '%uosmo' then 'uosmo'

when fee like '%uion' then 'uion' end)