select

block_id,

asset_id,

count(distinct tx_id) as number_of_transactions

from algorand.asset_transfer_transaction

group by block_id, asset_id

),

transactions_with_block_timestamp as (

select

transactions.block_id,

asset_id,

block_timestamp,

number_of_transactions

from transactions

left join algorand.block on algorand.block.block_id = transactions.block_id

),

daily_transactions as (

select

date_trunc('week', block_timestamp) as blocktime,

transactions_with_block_timestamp.asset_id as ID,

sum(number_of_transactions) as number_of_transactions,

algorand.asset.asset_name as name

from

transactions_with_block_timestamp

left join algorand.asset on transactions_with_block_timestamp.asset_id = algorand.asset.asset_id

group by blocktime, ID, name

select

blocktime,

name,

number_of_transactions,

row_number() over (partition by blocktime order by number_of_transactions desc) as rownumber

from daily_transactions

where blocktime > getdate() - interval'4 weeks'

qualify rownumber < 4