date_trunc('d',block_timestamp) as "Day",

case

when contract_address::string = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e' then 'USDC'

when contract_address::string = lower('0xC891EB4cbdEFf6e073e859e987815Ed1505c2ACD') then 'EUROC'

when contract_address::string = '0x152b9d0fdc40c096757f570a51e494bd4b943e50' then 'BTC.b'

when contract_address::string = '0x9702230a8ea53601f5cd2dc00fdbc13d4df4a8c7' then 'USDT'

else 'Other' end as "Type",

count(distinct tx_hash) as "Transactions",

count (distinct (case when decoded_log:to='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be' then decoded_log:from else decoded_log:to end)) as "Active Users",

sum(tx_fee) as "$AVAX Gas Fees"

from avalanche.core.ez_decoded_event_logs left join avalanche.core.fact_transactions using (tx_hash)

where "Day" >= '2023-07-01'

and event_name='Transfer'

and (decoded_log:to='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be' OR decoded_log:from='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be')

group by 1,2