前天在跑步时候突然得到一个灵感，之前只是分析了stepn中心账户的数据，但是stepn日活这么高，其中大量的用户数据也是可以拿来分析的。于是，这次详细拉取了地址维度的交易数据，给大家呈现一版全面的用户地址侧报告。
下面是技术分享时间：
初始数据还是从dune上获取的，主要是获取了三部分数据。
**每个地址与stepn账户sol代币的交易记录。**这部分需要注意的是，在一笔交易中，stepn账户可能同时向多个账户转账，需要把这些地址都提取出来。用一下posexplode语句。
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-- sol 交互统计with sol_record as(select *FROM `solana`.`transactions`lateral view posexplode(account_keys) s1 as account_index,accountlateral view posexplode(pre_balances) s2 as pre_balances_index,pre_balancelateral view posexplode(post_balances) s3 as post_balances_index,post_balanceWHERE block_date >= '2022-01-01'and ARRAY_CONTAINS(account_keys,"STEPNq2UGeGSzCyGVr2nMQAzf8xuejwqebd84wcksCK")and size(pre_token_balances)=0 and size(post_token_balances)=0AND error is NULLand account_index=pre_balances_index and pre_balances_index=post_balances_index)
select account,block_date,sum(post_balance - pre_balance)/power(10,9) as sol_num,sum(case when post_balance - pre_balance>=0 then (post_balance - pre_balance)/power(10,9) else 0 end) as withdraw_sol_num,sum(case when post_balance - pre_balance<=0 then -(post_balance - pre_balance)/power(10,9) else 0 end) as despoit_sol_numfrom sol_recordgroup by account,block_date
每个地址与stepn账户gst代币的交易记录。
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
with gst_record as(select *,pre_token_balance.account as accountFROM `solana`.`transactions`LATERAL VIEW EXPLODE(pre_token_balances) t3 AS pre_token_balanceLATERAL VIEW EXPLODE(post_token_balances) t3 AS post_token_balanceWHERE block_date >= '2022-01-01'AND (pre_token_balance.mint='AFbX8oGjGpmVFywbVouvhQSRmiW2aR1mohfahi4Y2AdB'or post_token_balance.mint='AFbX8oGjGpmVFywbVouvhQSRmiW2aR1mohfahi4Y2AdB')and ARRAY_CONTAINS(account_keys,"HLS5Y68QSQgJP7wUbbbbCjEnMknVZrHXYDwwVaDcsdK7")AND error is NULLAND pre_token_balance.account=post_token_balance.account)
select account,block_date,sum(post_token_balance.amount-pre_token_balance.amount) as gst_num,sum(case when post_token_balance.amount - pre_token_balance.amount>=0 then post_token_balance.amount - pre_token_balance.amount else 0 end) as withdraw_gst_num,sum(case when post_token_balance.amount - pre_token_balance.amount<0 then pre_token_balance.amount - post_token_balance.amount else 0 end) as despoit_gst_numfromgst_recordgroup by account,block_date
**每个原生地址与gst派生地址的对应关系。**这个是比较麻烦的一点。只能从用户发起的gst交易记录里面去提取了。这笔交易的签名是用户的原生地址，需要与gst的派生地址映射一下。
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
with address_map_tmp as(select distinct pre_token_balances[0].account as gst_address,signer as sol_address from `solana`.`transactions`WHERE block_date >= '2022-01-01'and pre_token_balances[0].mint="AFbX8oGjGpmVFywbVouvhQSRmiW2aR1mohfahi4Y2AdB"),address_map as (select distinct t2.gst_address,t2.sol_address from (select sol_address,count(*) as num from address_map_tmp group by sol_address) t1 join address_map_tmp t2 on t1.sol_address=t2.sol_address and t1.num=1)
select * from address_map
在完成这三部分数据之后，我是想把它们做成视图的，方便重复使用。但是经过询问官方，Dune暂时不支持sol链数据的视图。于是我只能把它们下载下来，在本地用python处理了一下。
往期stepn文章链接：
stepn数据看板地址：https://dune.xyz/timtai589/stepn