prices AS (

SELECT

hour::date AS day,

symbol,

AVG(price) AS price

FROM

ethereum.core.fact_hourly_token_prices

WHERE

hour::date BETWEEN '2023-01-01' AND CURRENT_DATE - 1

AND symbol IN ('WETH', 'WAVAX', 'WMATIC')

GROUP BY

day,

symbol

),

transactions AS (

SELECT

block_timestamp,

(tx_fee * price) AS fee_usd,

gas_used

FROM

arbitrum.core.fact_transactions transactions

JOIN prices ON transactions.block_timestamp::date = prices.day

WHERE

status = 'SUCCESS'

AND block_timestamp::date BETWEEN '2023-01-01' AND CURRENT_DATE - 1

AND symbol = 'WETH'

)

CASE

WHEN EXTRACT(DOW FROM block_timestamp) = 0 THEN '7.Sunday'

WHEN EXTRACT(DOW FROM block_timestamp) = 1 THEN '1.Monday'

WHEN EXTRACT(DOW FROM block_timestamp) = 2 THEN '2.Tuesday'

WHEN EXTRACT(DOW FROM block_timestamp) = 3 THEN '3.Wednesday'

WHEN EXTRACT(DOW FROM block_timestamp) = 4 THEN '4.Thursday'

WHEN EXTRACT(DOW FROM block_timestamp) = 5 THEN '5.Friday'