omer93moodz on sei v2 2nd listings
Updated 2024-11-21
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
35
36
›
⌄
with
info as (
SELECT
tx_id, sum(CASE WHEN msg_type = 'wasm-create_auction' AND attribute_key = 'token_id' THEN 1 END) AS nft_id,
MAX(CASE WHEN msg_type = 'wasm' AND attribute_key = 'sender' THEN attribute_value END) AS user,
MAX(CASE WHEN msg_type = 'wasm-create_auction' AND (attribute_key = 'min_price' or attribute_key='auction_price') THEN attribute_value END) AS attribute_value,
MAX(CASE WHEN msg_type = 'wasm-create_auction' AND (attribute_key = 'nft_address' or attribute_key='collection_address') THEN attribute_value END) AS collection
FROM
sei.core.fact_msg_attributes
where block_timestamp>=current_date-INTERVAL '1 WEEK'
GROUP BY
tx_id
having collection='sei17f543dtmwt9wraa3pffz7dss5k68p77tymxn898kzq4q67kr0wgst4wl0v'
),
info2 as (
select *,
case when attribute_value ilike 'native::%' then substr(attribute_value, 13,24)
else split(attribute_value, 'usei')[0] end as prices,
prices/pow(10,6) as sei_price,
(prices*nft_id)/pow(10,6) as sei_volume
from info where attribute_value not like '%Coin%'
)
select
count(distinct tx_id) as listings,
count(distinct user) as users_listing,
avg(sei_price) as avg_nft_price,
min(sei_price) as floor_price,
max(sei_price) as max_nft_price
from info2
QueryRunArchived: QueryRun has been archived