from algorand.account_asset

where amount > 0 and asset_closed = 'FALSE'

group by 1,2

order by 3 desc

limit 10),

tx_count as (select block_id, asset_id, count(distinct tx_id) as tx_count

from algorand.transactions

where asset_id in (select asset_id from top10)

group by 1,2),

block as (select block_id, block_timestamp

from algorand.block),

final as (select c.block_timestamp, b.asset_id, b.block_id, b.tx_count, a.asset_name

from block c

left outer join tx_count b on c.block_id = b.block_id

left outer join top10 a on b.asset_id = a.asset_id),

lasts as (select date_trunc('week', block_timestamp) as dt, asset_name, sum(tx_count) as transaction_count

from final

group by 1,2)

select *

from lasts

where transaction_count is not null