WITH PriceTb AS (

SELECT

hour :: date AS p_date,

AVG(price) AS price

FROM

solana.price.ez_prices_hourly

WHERE

token_address = '4vMsoUT2BWatFweudnQM1xedRLfJgJ7hswhcpz4xgBTy'

GROUP BY

1

),

burn_information AS (

SELECT

x.block_timestamp,

x.tx_id,

BURN_AUTHORITY AS signer,

burn_amount / POWER(10, 9) AS Honey_burn_amount,

(price * burn_amount) / POWER(10, 9) AS Honey_burn_usd

FROM

solana.defi.fact_token_burn_actions AS x

LEFT OUTER JOIN PriceTb ON p_date = DATE_TRUNC('day', x.block_timestamp)

WHERE

mint LIKE '4vMsoUT2BWatFweudnQM1xedRLfJgJ7hswhcpz4xgBTy'

AND x.block_timestamp > '2022-01-01'

AND SUCCEEDED

SELECT

DATE_TRUNC('month', block_timestamp) AS month,

COUNT(DISTINCT signer) AS burners,

SUM(Honey_burn_amount) AS burn_amount,

SUM(Honey_burn_usd) AS burn_amount_usd,

COUNT(*) AS burn_events