SELECT

t1.block_timestamp::date AS transfer_date,

COALESCE(t2.address_name, t3.address_name) AS address_label,

t1.tx_id,

t1.tx_from,

t1.amount

FROM

solana.core.fact_transfers t1

JOIN solana.core.dim_labels t2 ON t1.tx_to = t2.address

JOIN crosschain.core.dim_labels t3 ON t1.tx_to = t3.address

WHERE

t2.label != 'solana'

AND t1.mint = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB'

AND t2.label_type NOT IN ('nft', 'token')

AND t3.label_type NOT IN ('nft', 'token')

AND t1.block_timestamp >= '2024-08-01'

AND t1.block_timestamp < '2024-09-01'

),

AggregatedData AS (

SELECT

transfer_date,

INITCAP(address_label) AS destination_label,

COUNT(DISTINCT tx_id) AS transaction_count,

COUNT(DISTINCT tx_from) AS user_count,

SUM(amount) AS total_volume

FROM

FilteredTransfers

GROUP BY

transfer_date,

INITCAP(address_label)

ad.transfer_date AS date,

ad.destination_label AS Destination_Label,

ad.transaction_count AS TX_Count,