prices AS (

SELECT

prices.block_timestamp::date AS day,

prices.token_contract,

prices.symbol,

CASE

WHEN symbol = 'WAVAX' THEN 'Native Coin'

WHEN symbol IN ('USDt', 'USDT.e', 'USDC', 'USDC.e', 'BUSD.e', 'DAI.e') THEN 'Stablecoins'

ELSE 'Other Tokens'

END AS token_type,

AVG(contracts.token_decimals) AS decimals,

AVG(prices.price) AS price

FROM (

SELECT

block_timestamp,

token_in AS token_contract,

symbol_in AS symbol,

(amount_out / amount_in) AS price

FROM

avalanche.sushi.ez_swaps

WHERE

block_timestamp::date BETWEEN (CURRENT_DATE - 30) AND (CURRENT_DATE - 1)

AND symbol_out IN ('USDt', 'USDT.e', 'USDC', 'USDC.e', 'BUSD.e', 'DAI.e')

AND amount_in > 0

AND amount_out > 0

UNION

SELECT

block_timestamp,

token_out AS token_contract,

symbol_out AS symbol,

(amount_in / amount_out) AS price

FROM

avalanche.sushi.ez_swaps

WHERE

block_timestamp::date BETWEEN (CURRENT_DATE - 30) AND (CURRENT_DATE - 1)