SELECT BLOCK_TIMESTAMP::date as date, l2.contract_name as token_in, l1.EVENT_CONTRACT, l1.contract_name as token_out, tx_id, TOKEN_OUT_AMOUNT, TOKEN_IN_AMOUNT, CASE token_in

WHEN 'FlowToken' THEN 'FLOW'

WHEN 'FiatToken' THEN 'USDC'

WHEN 'BloctoToken' THEN 'BLT'

WHEN 'TeleportedTetherToken' THEN 'USDT'

WHEN 'StarlyToken' THEN 'STARLY'

ELSE token_in

END as token

FROM flow.core.fact_swaps s LEFT JOIN flow.core.DIM_CONTRACT_LABELS l1 ON s.TOKEN_OUT_CONTRACT = l1.EVENT_CONTRACT

LEFT JOIN flow.core.DIM_CONTRACT_LABELS l2 ON s.TOKEN_IN_CONTRACT = l2.EVENT_CONTRACT

, avg_price as (

SELECT timestamp::date as date, symbol, avg(price_usd) as price_usd

FROM flow.core.fact_prices

GROUP BY 1, 2

SELECT t.date, t.token,

EVENT_CONTRACT, COUNT(DISTINCT tx_id) as num_tx, SUM(token_in_amount * IFF(price_usd IS NULL, 1, price_usd)) as swapped_amount

FROM tx t LEFT JOIN avg_price p ON t.date = p.date and t.token = symbol

GROUP BY 1, 2, 3