shadilRand Gallery NFT Sales - top sale in $ALGO
Updated 2022-05-03
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
›
⌄
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