with swap_transaction as (
select
tx_group_id,
sender as address,
0 as received_amount,
asset_amount as sent_amount
from algorand.asset_transfer_transaction
where block_timestamp > '2022-01-01'
and asset_transferred in (31566704, 312769)
and tx_group_id is not null
union all
select
tx_group_id,
asset_receiver as address,
asset_amount as received_amount,
0 as sent_amount
from algorand.asset_transfer_transaction
where block_timestamp > '2022-01-01'
and asset_transferred in (31566704, 312769)
and tx_group_id is not null
),
temp as (
select
address,
sum(received_amount)/1e6 as received_amount,
sum(sent_amount)/1e6 as sent_amount,
count(distinct tx_group_id) as number_of_transactions,
count_if(received_amount > 0) as number_of_receiving_transactions,
count_if(sent_amount > 0) as number_of_sending_transactions
from swap_transaction
group by address
),
overall_stats as (
select
*,
received_amount/sent_amount as profit_ratio,