select

regexp_replace(b.contract_name, '(Teleported)|(Token)','') as swap_to_token,

token_out_amount,

regexp_replace(c.contract_name, '(Teleported)|(Token)','') as swap_from_token,

token_in_amount,

trader,

tx_id,

iff(swap_to_token in ('Tether','FUSD','Fiat') ,1,d.PRICE_USD) as sawp_to_usd_price,

iff(swap_from_token in ('Tether','FUSD','Fiat') ,1,e.PRICE_USD) as sawp_from_usd_price,

token_out_amount * sawp_to_usd_price as token_out_amount_in_usd,

token_in_amount * sawp_from_usd_price as token_in_amount_in_usd,

a.block_timestamp

from flow.core.fact_swaps a

left join flow.core.dim_contract_labels b on (a.token_out_contract = b.event_contract)

left join flow.core.dim_contract_labels c on (a.token_in_contract = c.event_contract)

left join flow.core.fact_prices d on (regexp_replace(b.contract_name, '(Teleported)|(Token)','') = regexp_replace(d.TOKEN, '(Teleported)|(Token)','') and date_trunc(hour,a.block_timestamp) = date_trunc(HOUR,d.timestamp))

left join flow.core.fact_prices e on (regexp_replace(c.contract_name, '(Teleported)|(Token)','') =regexp_replace(e.TOKEN, '(Teleported)|(Token)','')and date_trunc(hour,a.block_timestamp) = date_trunc(HOUR,e.timestamp))

where block_timestamp::date >= '2022-05-01'

and block_timestamp::date < '2022-06-01'

count(distinct tx_id) as number_of_swaps,

sum(token_out_amount) as total_out_amount,

sum(token_in_amount) as total_in_amount,

sum(token_out_amount_in_usd) as total_usd_out,

sum(token_in_amount_in_usd) as total_usd_in,

total_usd_out + (total_usd_in * -1) as usd_amount_received,

swap_from_token,

swap_to_token,

concat(swap_from_token,'/',swap_to_token) as token_pair,

block_timestamp::date as day

from initial_data

group by swap_to_token, swap_from_token, day, token_pair