with prices as (
SELECT asset_id, asset_name, avg(price_usd) as price
from algorand.prices_swap
where date(block_hour) >= '2022-05-14'
GROUP by asset_id, asset_name
),
new_wallets as (
select
sender
from (
select
sender,
min(date(block_timestamp)) as min_date
from algorand.transactions
group by sender
)
where min_date BETWEEN '2022-05-04' and '2022-05-10'
GROUP by sender
)
SELECT
asa.asset_name,
count( distinct address) as wallets,
sum(amount/pow(10, DECIMALS)) * p.price as holdings,
(holdings/wallets) as avg_holdings
from algorand.account_asset asa
join prices p on p.asset_id = asa.ASSET_ID
join flipside_prod_db.algorand.asset asset_dec on asset_dec.asset_id = asa.ASSET_ID
where address in (SELECT sender FROM new_wallets)
group by asa.asset_name, p.price, DECIMALS
order by wallets DESC
limit 10