SELECT

date_trunc(week, block_timestamp) AS week,

CASE WHEN origin_function_signature = '0x53a8aa03' THEN 'GPL_minted'

ELSE 'GPL_redeemed'

END AS action,

SUM(raw_amount/1e18) AS sum

FROM avalanche.core.fact_token_transfers

WHERE origin_function_signature IN ('0x53a8aa03' , '0x0f3aa554' )

AND block_timestamp > CURRENT_DATE() - interval '3 months'

GROUP BY 1, 2

ORDER BY 1

),

price AS(

SELECT

WEEK(hour) AS time,

AVG(price) AS avg_price

FROM ethereum.core.fact_hourly_token_prices

WHERE token_address = '0x1ce0c2827e2ef14d5c4f29a091d735a204794041'

AND hour > CURRENT_DATE() - interval '3 months'

GROUP BY 1

ORDER BY 1

SELECT

week,

action,

sum*avg_price AS value

FROM a

LEFT JOIN price

ON a.week = price.time