JonasoTether : USDT
    Updated 2024-07-29
    with

    -- labeled db setup
    LL as(
    select to_varchar(value:BB) as cate, to_varchar(value:CC) as issuer, to_varchar(value:DD) as token, to_varchar(value:CT) as CONTRACT, to_varchar(value:CH) as ch
    from (select live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/868a10dd-8c47-4df6-a5d5-d246600e5411/data/latest') as db ) , LATERAL FLATTEN (input => db:data)
    where to_varchar(value:AA) = 'stable' ),

    AA as(
    select 'Ethereum' as chain, '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 'Ethereum' as chain, '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 'Arbitrum' as chain, '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 'Arbitrum' as chain, '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 union all
    select 'Optimism' as chain, '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 'Optimism' as chain, '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 'Base' as chain, '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 'Base' as chain, '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 'Blast' as chain, 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from blast.core.fact_token_transfers union all
    select 'Blast' as chain, 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from blast.core.fact_token_transfers union all
    select 'Avalanche' as chain, '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 'Avalanche' as chain, '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 'BNB chain' as chain, 'mint' as action, raw_amount as amount, contract_address, from_address as mint, to_address as entity, block_timestamp from bsc.core.fact_token_transfers union all
    select 'BNB chain' as chain, 'burn' as action, 0 - raw_amount as amount, contract_address, to_address as mint, from_address as entity, block_timestamp from bsc.core.fact_token_transfers union all
    select 'Polygon' as chain, '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 'Polygon' as chain, '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
    ),

    -- logic
    BA as(
    select block_timestamp, chain, amount/1e6 as amount
    from AA as a
    join LL as b on a.contract_address = lower(b.contract)
    where mint = '0x0000000000000000000000000000000000000000'
    and token in ('USDT') ),

    BB as(
    QueryRunArchived: QueryRun has been archived