SELECT

BLOCK_TIMESTAMP,

TX_HASH,

ORIGIN_FROM_ADDRESS AS User,

CONTRACT_ADDRESS AS Pool_Address,

CONCAT('0x', SUBSTR(DATA, 27, 40)) AS OWNER,

livequery.utils.udf_hex_to_int(CONCAT('0x', SUBSTR(DATA, 27 + 1* 64, 40))) AS ytMinted,

livequery.utils.udf_hex_to_int(CONCAT('0x', SUBSTR(DATA, 27 + 2* 64, 40))) AS vtMinted,

'mint' AS Action,

CONCAT('0x', SUBSTR(TOPICS[2], 27 + 1* 0, 40)) AS Token

FROM avalanche.core.fact_event_logs

WHERE

TOPICS[0] = '0xac903ad8fe2e7a6d229683df0f465af874089964b0ea74e096f95d106cd333b7'

AND TOPICS[1] = '0xff6c677ae791aaee8dffde40cc8cbc6abf7c9a50914ac3d196a697b8d3807a4e'

),

Mint_With_Transfers AS (

SELECT

B.*,

A.contract_address AS Token_Contract,

A.AMOUNT AS Value,

A.AMOUNT_USD

FROM avalanche.core.ez_token_transfers A

INNER JOIN Mint_Data B ON A.TO_ADDRESS = B.Token AND A.TX_HASH = B.TX_HASH

SELECT

ROUND(SUM(Value), 2) AS Total_Deposits,

COUNT(DISTINCT TX_HASH) AS Total_Deposit_TX,

COUNT(DISTINCT User) AS Total_Deposit_Users,

ROUND(SUM(Value) / NULLIF(COUNT(DISTINCT TX_HASH), 0), 2) AS Avg_Deposit_Size,

MIN(DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS First_Date,

MAX(DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS Last_Date,

COUNT(DISTINCT DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS Active_Days,

ROUND(SUM(Value) / COUNT(DISTINCT DATE_TRUNC('day', BLOCK_TIMESTAMP)), 2) AS Avg_Daily_Deposits