shadilRand Gallery NFT Sales - top sale in $ALGO
    Updated 2022-05-03
    with algo_prices as (
    SELECT date(BLOCK_HOUR) as pdate, avg(price_usd) as price
    from algorand.prices_swap
    where asset_id = 0
    GROUP by pdate
    ),
    tops as (
    SELECT tx_group_id, sum(total_algo) as total_algo, sum(total_usd) as total_usd
    FROM (
    SELECT f.tx_group_id, sum(zeroifnull(m.amount)) as t_amount , avg(zeroifnull(f.amount)) as fam, t_amount + fam as total_algo,
    total_algo * p.price as total_usd
    from algorand.payment_transaction f
    join algorand.payment_transaction m on f.tx_group_id = m.tx_group_id and m.amount > 0.005 and lower(m.receiver) != lower('RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE')
    join algo_prices p on p.pdate = date(m.block_timestamp)
    -- where f.tx_group_id = 'l44U5hi3bcVaIAXBJbR7TGbQobCZLzAoiK+JtgGzNAw=' -- sample NFT Sale
    where lower(f.receiver) = lower('RANDGVRRYGVKI3WSDG6OGTZQ7MHDLIN5RYKJBABL46K5RQVHUFV3NY5DUE')
    GROUP by f.tx_group_id, p.price
    )
    GROUP by tx_group_id
    order by total_algo DESC
    limit 5
    )
    SELECT a.ASSET_NAME, top.total_algo, tr.ASSET_ID
    from tops top
    left join algorand.asset_transfer_transaction tr on tr.tx_group_id = top.tx_group_id and (tr.extra:aca > 0 or tr.ASSET_AMOUNT > 0)
    left join algorand.asset a on tr.ASSET_ID = a.ASSET_ID
    order by top.total_algo DESC
    Run a query to Download Data