mint_and_burn_transactions AS (

SELECT

tx_hash

, event_index

, block_timestamp

, origin_from_address

, event_inputs['from']::string AS sender

, event_inputs['to']::string AS receiver

, contract_address AS token_address

, event_inputs['value']::bigint / pow(10,6) AS amount

, (CASE WHEN sender = '0xbf14db80d9275fb721383a77c00ae180fc40ae98' THEN 'Withdraw USDC to Avalanche'

WHEN receiver = '0xbf14db80d9275fb721383a77c00ae180fc40ae98' THEN 'Deposit USDC to Circle'

ELSE 'Others' END) AS transaction_type

, (CASE WHEN sender = '0xbf14db80d9275fb721383a77c00ae180fc40ae98' THEN receiver

WHEN receiver = '0xbf14db80d9275fb721383a77c00ae180fc40ae98' THEN sender END) AS wallet_address

FROM avalanche.core.fact_event_logs AS transfer

WHERE tx_status = 'SUCCESS'

AND event_name = 'Transfer'

AND block_timestamp >= '2022-07-01 00:00:00.000'

AND try_to_numeric(event_inputs['value']::string) IS NOT NULL

AND contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

AND (sender = '0xbf14db80d9275fb721383a77c00ae180fc40ae98' -- withdraw from Circle

OR receiver = '0xbf14db80d9275fb721383a77c00ae180fc40ae98') -- deposit to Circle

AND NOT (sender = '0x0000000000000000000000000000000000000000' -- mint

OR receiver = '0x0000000000000000000000000000000000000000') -- burn

),

daily_stats AS (

SELECT

block_timestamp::date AS utc_date