SELECT

DATE_TRUNC('day', transfer.block_timestamp) AS donation_date,

transfer.tx_from AS from_owner,

transfer.tx_to AS to_owner,

tags.name,

SUM(transfer.amount) AS transfer_amount,

COUNT(transfer.tx_id) AS donate_count

FROM

solana.core.fact_transfers AS transfer

LEFT JOIN (

SELECT

'2ZiQY2u9bNxLF3fDhUFmZCz1n1baNnx2jtu1asPYpMhR' AS address,

'0xAA' AS name

) AS tags ON transfer.tx_from = tags.address

WHERE

transfer.tx_to = 'Cfy5rFwmU2fe43YbR79F1Nsm1REZ9DurQ3aoKhP6ANgt'

AND transfer.mint = 'GxdTh6udNstGmLLk9ztBb6bkrms7oLbrJp5yzUaVpump'

GROUP BY

DATE_TRUNC('day', transfer.block_timestamp),

transfer.tx_from,

transfer.tx_to,

tags.name

),

daily_metrics AS (

SELECT

donation_date,

COUNT(DISTINCT from_owner) AS unique_wallet_count,

SUM(transfer_amount) AS total_volume_donated,

SUM(donate_count) AS total_donations

FROM

all_donations

GROUP BY

donation_date