omer93crafty canines 2nd listings 2
Updated 2024-12-23
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,block_timestamp, 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') and attribute_value is not null 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 MONTH' -- and block_timestamp< '2024-06-01'
GROUP BY
tx_id,block_timestamp
having collection='sei1c7tj004asc9n6zhkk950u0nvxewwswanvg95aqazqs29q3l6nq9sqtrtwl'
),
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
trunc(block_timestamp,'day') as time,
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
--where time<current_date
group by 1 order by 1 desc
QueryRunArchived: QueryRun has been archived