from terra.swaps

where year(block_timestamp) = '2022'),

price as (select date_trunc('day', block_timestamp) as dt, currency, avg(price_usd) as price

from terra.oracle_prices

where year(block_timestamp) = '2022'

group by 1,2),

fees as (select date_trunc('day', block_timestamp) as dt, fee[0]:amount[0]:denom::string as currency, sum(fee[0]:amount[0]:amount/1e6) as fees

from terra.transactions

where year(block_timestamp) = '2022' and tx_status = 'SUCCEEDED' and tx_id in (select tx_id from native)

group by 1,2),

alltx as (select date_trunc('day', block_timestamp) as dt, fee[0]:amount[0]:denom::string as currency, sum(fee[0]:amount[0]:amount/1e6) as fees

from terra.transactions

where year(block_timestamp) = '2022' and fee[0]:amount[0]:amount is not null and tx_status = 'SUCCEEDED'

group by 1,2),

tx_all as (select a.dt, a.currency, a.fees, b.price, (a.fees*b.price) as tx_fees_usd

from alltx a

left join price b on a.dt = b.dt and a.currency = b.currency),

swaps as (select a.dt, a.currency, a.fees, b.price, (a.fees*b.price) as swap_fees_usd

from fees a

left join price b on a.dt = b.dt and a.currency = b.currency),

lastt as (select t.dt, sum(t.tx_fees_usd) as alltx_fees_usd, sum(s.swap_fees_usd) as swap_usd

from tx_all t

left join swaps s on t.dt = s.dt

group by 1)

select *, (alltx_fees_usd-swap_usd) as other_fees_usd

from lastt