DB as(

select value:CHAIN as chain,

value:TAG as tag,

value:SYMBOL as symbol,

value:CONTRACT as contract,

value:MINT as mint

from (select livequery.live.udf_api ('https://flipsidecrypto.xyz/api/v1/queries/8d17a483-bb7d-4694-9bb0-a1ecc6fd142c/data/latest') as resp),

lateral flatten(input => resp:data) ),

A as(

select 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from ethereum.core.fact_token_transfers union all

select 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from ethereum.core.fact_token_transfers union all

select 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from base.core.fact_token_transfers union all

select 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from base.core.fact_token_transfers union all

select 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from avalanche.core.fact_token_transfers union all

select 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from avalanche.core.fact_token_transfers union all

select 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from polygon.core.fact_token_transfers union all

select 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from polygon.core.fact_token_transfers union all

select 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from optimism.core.fact_token_transfers union all

select 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from optimism.core.fact_token_transfers union all

select 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from arbitrum.core.fact_token_transfers union all

select 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from arbitrum.core.fact_token_transfers ),

AB as( select block_timestamp, symbol, chain, contract_address, action, amount/1e6 as amount

from A as a

join DB as b on a.contract_address = lower(b.contract) and a.mint = lower(b.mint)

where chain = 'Ethereum' ),

AC as( select block_timestamp, 'USDT' as symbol, 'Ethereum' as chain, contract_address,

case when event_name = 'Issue' then 'mint' else 'burn' end as action,

case when event_name = 'Issue' then get(decoded_log, 'amount')/1e6 else 0 - get(decoded_log, 'amount')/1e6 end amount

from ethereum.core.fact_decoded_event_logs