omer9312-18 feb 2nd sales
Updated 2024-09-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
28
29
30
31
32
33
34
35
36
›
⌄
with
info as (
SELECT
tx_id, count(distinct CASE WHEN msg_type = 'wasm-buy_now' AND attribute_key = 'nft_token_id' THEN attribute_value END) AS nft_id,
MAX(CASE WHEN msg_type = 'wasm-buy_now' AND attribute_key = 'nft_seller' THEN attribute_value END) AS seller,
MAX(CASE WHEN msg_type = 'wasm-buy_now' AND attribute_key = 'sale_price' THEN attribute_value END) AS attribute_value,
--MAX(CASE WHEN msg_type = 'wasm-buy_now' AND attribute_key = 'sale_price' THEN REPLACE(attribute_value, 'usei', '')::DECIMAL / 1000000 END) AS sei_price,
MAX(CASE WHEN msg_type = 'coin_spent' AND attribute_key = 'spender' THEN attribute_value END) AS buyer,
MAX(CASE WHEN msg_type = 'wasm-buy_now' AND attribute_key = 'nft_address' THEN attribute_value END) AS collection
FROM
sei.core.fact_msg_attributes
where block_timestamp between '2024-02-12' and '2024-02-18'
GROUP BY
tx_id
having collection in ('sei1svkamkuklth3wls08wt596q2dykf6j55tnnw7rsh5675fsfpj40sjdhn00','sei12y9v6h9jsqhamxgy79hfs9703flt62a46t7fgpqsjnfqzr2ep3kq8wuxf7','sei175ykndekpckkr8zxjah3gfksymj0ztpu8lzadkga7hc2xcpw45rq9evrhw','sei1wt9nn8lezq4a3j0h6alqu9hg763x78tqgxumkjf5yk7cjqs94lpsf02td5',
'sei1xlve6pkm5lhu0jhuhk9slqekds85h0yqfxkqdquqn5jzj7xcyesq7eayxk','sei15v3n2u5cp8xawheh2evwcncjg38aruf89kjznxw8ur7m976dfm4s5qcvn5','sei19scyrjzgpfa9qmyle8e7pygde69fx8z0mgtqtfqz5a88uh9m6xnsv0s3xf','sei19lderg9lfwmcslajqx6xztpnmgdt2nx0t5eqnjfqgqqv04wxj0ys98vcqg','sei1m392l37pgs2mp7uk8mnegtdereyp9rh874del8uuvpkqjpa8g8asqxmykf','sei1mtjdkklxgjkd8xsqpwpn7a72lc635wug848lfv6hpgeacwmc2r6sqr596p')
),
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/pow(10,6)*nft_id as sei_volume
from info
)
select
case when collection='sei1svkamkuklth3wls08wt596q2dykf6j55tnnw7rsh5675fsfpj40sjdhn00' then 'Ghosty'
when collection='sei12y9v6h9jsqhamxgy79hfs9703flt62a46t7fgpqsjnfqzr2ep3kq8wuxf7' then 'Fud Foxes'
when collection='sei175ykndekpckkr8zxjah3gfksymj0ztpu8lzadkga7hc2xcpw45rq9evrhw' then 'Sei Maps'
when collection='sei1wt9nn8lezq4a3j0h6alqu9hg763x78tqgxumkjf5yk7cjqs94lpsf02td5' then 'BankSEI'
when collection='sei1mtjdkklxgjkd8xsqpwpn7a72lc635wug848lfv6hpgeacwmc2r6sqr596p' then '6oblin'
when collection='sei19lderg9lfwmcslajqx6xztpnmgdt2nx0t5eqnjfqgqqv04wxj0ys98vcqg' then 'Seigen Art'
when collection='sei1m392l37pgs2mp7uk8mnegtdereyp9rh874del8uuvpkqjpa8g8asqxmykf' then 'Piggy Tales'
when collection='sei15v3n2u5cp8xawheh2evwcncjg38aruf89kjznxw8ur7m976dfm4s5qcvn5' then 'Seipex Finance'
when collection='sei19scyrjzgpfa9qmyle8e7pygde69fx8z0mgtqtfqz5a88uh9m6xnsv0s3xf' then 'Seiscapes'