Eman-RazTop 10 Collections Based on the Sales Count
Updated 2024-01-15
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
28
29
30
31
32
33
34
›
⌄
with table5 as (with table3 as (with table1 as (select block_timestamp, event_data:buyer_address as buyer, tx_hash, event_data:id:token_data_id:collection as collection,
event_data:id:token_data_id:name as nft
from aptos.core.fact_events
where event_module='marketplaceV2' and tx_type='user_transaction' and success='true' and
event_resource='BuyEvent' and event_address='0xd1fd99c1944b84d1670a2536417e997864ad12303d19eac725891691b04d614e'),
table2 as (select tx_hash, block_timestamp, sum(event_data:amount)/pow(10,8) as price
from aptos.core.fact_events
where event_module='coin' and event_resource='WithdrawEvent'
and tx_type='user_transaction' and success='true' and
payload_function='0xd1fd99c1944b84d1670a2536417e997864ad12303d19eac725891691b04d614e::marketplaceV2::batch_buy_script'
group by 1,2)
select table1.block_timestamp, table1.tx_hash, buyer, price, collection, nft
from table1 left join table2 on table1.tx_hash=table2.tx_hash and table1.block_timestamp=table2.block_timestamp
order by 1),
table4 as (select block_timestamp, tx_hash, event_data:purchaser as buyer, event_data:price/pow(10,8) as price,
event_data:token_metadata:collection_name as collection, event_data:token_metadata:token_name as nft
from aptos.core.fact_events
where tx_type='user_transaction' and event_address='0xd520d8669b0a3de23119898dcdff3e0a27910db247663646ad18cf16e44c6f5'
and event_module='events' and event_resource='TokenOfferFilledEvent')
select * from table3 union all
select * from table4)
select collection, count(distinct tx_hash) as "Sales Count", count(distinct buyer) as "Buyers Count",
sum(price) as "Sales Volume (APT)", MAX(price) as "Max Price (APT)", Avg(price) as "Avg Price (APT)"
from table5
where block_timestamp::date>='{{Start_Date}}' and block_timestamp::date<='{{End_Date}}'
and price is not null
group by 1
order by 2 desc
limit 10
QueryRunArchived: QueryRun has been archived