WITH FIX_ETH_OUTFLOW AS (

SELECT BLOCK_TIMESTAMP,

IFF(CONTRACT_ADDRESS = '0xdeaddeaddeaddeaddeaddeaddeaddeaddead0000','0x4200000000000000000000000000000000000006' , CONTRACT_ADDRESS) AS "Token Address",

EVENT_INPUTS:value AS "Raw Outflow"

FROM optimism.core.fact_event_logs

WHERE EVENT_INPUTS:to ='0x0000000000000000000000000000000000000000' and

EVENT_NAME = 'Transfer' AND BLOCK_TIMESTAMP::DATE BETWEEN '2022-07-01' AND '2022-07-31'

),

L2_L1 AS (SELECT BLOCK_TIMESTAMP::DATE "Days", "Token Address", SUM("Raw Outflow") "Raw Outflow"

FROM FIX_ETH_OUTFLOW

GROUP BY "Days", "Token Address"),

AMOUNT AS (SELECT "Days", SYMBOL, "Token Address", ("Raw Outflow"/POWER (10,IFF(DECIMALS IS NULL OR DECIMALS = 0, 1, DECIMALS))) "Amount"

FROM L2_L1 JOIN optimism.core.dim_contracts ON ADDRESS = "Token Address"),

DAILY_PRICES AS (SELECT HOUR::DATE AS Days, TOKEN_ADDRESS , AVG(PRICE) "Daily price", Symbol "Token"

FROM optimism.core.fact_hourly_token_prices

WHERE Days BETWEEN '2022-07-01' AND '2022-07-31'

GROUP BY Days, "Token", TOKEN_ADDRESS),

Daily_Outflow_Volume_Per_Token AS (

SELECT Days, ("Daily price" * "Amount") "Volume", "Token"

FROM DAILY_PRICES JOIN AMOUNT ON (Days ="Days" AND TOKEN_ADDRESS = "Token Address")

),

OP_PRICE AS

(SELECT HOUR::DATE "Days", AVG(PRICE) "Op price($)"

FROM optimism.core.fact_hourly_token_prices WHERE

"Days" BETWEEN '2022-07-01' AND '2022-07-31' AND TOKEN_ADDRESS = '0x4200000000000000000000000000000000000042'

GROUP BY "Days"),

Daily_Outflow_Volume AS (

SELECT Days, SUM("Volume") AS "Outflow volume($)"

FROM Daily_Outflow_Volume_Per_Token