t.tx_id,

SWAP_PROGRAM,

ADDRESS_NAME,

TX_MESSAGE:txn:grp as grp_id,

l.ASSET_NAME,

l2.ASSET_NAME as an1,

SWAP_FROM_AMOUNT,

SWAP_TO_AMOUNT

from flipside_prod_db.algorand.swaps s inner join flipside_prod_db.algorand.asset l on s.SWAP_from_ASSET_ID=l.ASSET_ID

inner join flipside_prod_db.algorand.asset l2 on s.SWAP_TO_ASSET_ID=l2.ASSET_ID

inner join flipside_prod_db.algorand.labels l3 on s.pool_address=l3.ADDRESS

left join flipside_prod_db.algorand.transfers t on s.TX_GROUP_ID=t.TX_GROUP_ID

where s.block_timestamp>='2022-06-27'

),

raw2 as (select SWAP_PROGRAM, tx_id, case when grp_id is null then 'Not Via Alammex' else 'Via Alammex' end as aggt from raw )

select aggt, count(tx_id) as n_txn

from raw2

--where aggt='Via Alammex'

group by 1 order by 1

limit 10000