SELECT DATE_TRUNC('Day',BLOCK_HOUR) AS date, AVG(PRICE_USD) AS ALGO_PRICE

FROM flipside_prod_db.algorand.prices_swap

WHERE ASSET_NAME = 'ALGO'

AND ASSET_ID = '0'

GROUP BY 1

),

t1 AS ( --BTC PRICE

SELECT DATE_TRUNC('Day',HOUR) AS dt1, AVG(PRICE) AS BTC_PRICE

FROM ethereum.core.fact_hourly_token_prices

WHERE TOKEN_ADDRESS = LOWER('0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599')

GROUP BY 1

),

t2 AS ( -- ETH PRICE

SELECT DATE_TRUNC('Day',HOUR) AS dt2, AVG(PRICE) AS ETH_PRICE

FROM ethereum.core.fact_hourly_token_prices

WHERE TOKEN_ADDRESS = LOWER('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

GROUP BY 1

),

t3 AS ( -- SOL PRICE

SELECT DATE_TRUNC('Day',HOUR) AS dt3, AVG(PRICE) AS SOL_PRICE

FROM ethereum.core.fact_hourly_token_prices

WHERE TOKEN_ADDRESS = '0xd31a59c85ae9d8edefec411d448f90841571b89c'

GROUP BY 1

),

t4 AS (

SELECT DATE_TRUNC('Day',BLOCK_TIMESTAMP) AS dt4, COUNT(TX_ID) AS ALGO_Transfers

FROM flipside_prod_db.algorand.transfers

WHERE ASSET_ID = '0'

GROUP BY 1

),