Eman-RazClassification of Buyers Based on the Purchase Volume
Updated 2023-11-02
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 final_tab as (with tab5 as (with tab3 as (with tab1 as (select distinct tx_id
from sei.core.fact_msg_attributes
where attribute_value='sei1pdwlx9h8nc3fp6073mweug654wfkxjaelgkum0a9wtsktwuydw5sduczvz'
and attribute_key='_contract_address'
and msg_type='wasm-execute-exchange'
and tx_succeeded='true'),
tab2 as (select block_timestamp::date as date, attribute_value/pow(10,6) as price, tx_id
from sei.core.fact_msg_attributes
where attribute_key='exchange-token-amount' and tx_succeeded='true' and msg_type='wasm-accept-request')
select date, tab1.tx_id as tx_id, price
from tab1 left join tab2 on tab1.tx_id=tab2.tx_id),
tab4 as (select tx_id, attribute_value as seller
from sei.core.fact_msg_attributes
where ATTRIBUTE_KEY='seller' and msg_type='wasm-accept-request' and msg_index= 12)
select date, tab3.tx_id as tx_id, seller, price
from tab3 left join tab4 on tab3.tx_id=tab4.tx_id),
tab6 as (select tx_id, tx_from as buyer, split_part(fee,'u',1)/pow(10,6) as fee
from sei.core.fact_transactions)
select buyer, sum(price), case
when sum(price)<=1 then 'V<=1 SEI'
when sum(price)>1 and sum(price)<=10 then '1<V<=10 SEI'
when sum(price)>10 and sum(price)<=100 then '10<V<=100 SEI'
when sum(price)>100 and sum(price)<=1000 then '100<V<=1K SEI'
when sum(price)>1000 and sum(price)<=10000 then '1K<V<=10K SEI'
when sum(price)>10000 and sum(price)<=100000 then '10K<V<=100K SEI'
else 'V>100K SEI' end as "Class"
from tab5 left join tab6 on tab5.tx_id=tab6.tx_id
group by 1)
select "Class", count(distinct buyer) as "Buyer Count"
Run a query to Download Data