theericstonehelius livequery remix
Updated 2023-12-06
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
›
⌄
-- let's use flipside and helius together
-- to find their tokens/holdings
with maddestlad as (
select
sales.purchaser,
sum(sales.sales_amount) as sol_spent
from
solana.nft.fact_nft_sales sales
join solana.core.dim_labels nfts
on nfts.address = sales.mint
where sales.block_timestamp > current_date - 30
and nfts.label = 'mad lads'
group by purchaser
order by sol_spent desc
),
-- now let's pull the address of the most active mad lads trader
-- we'll get their balances via a call to Helius
rawbals as (
SELECT livequery.live.udf_api(
concat(
'https://api.helius.xyz/v0/addresses/',
(select distinct first_value(purchaser) OVER (order by sol_spent desc) from maddestlad),
'/balances?api-key=ed2451a6-5f4e-4a9e-baf4-3d0affb608d0')
) as response
),
-- this is just formatting the raw json we receive from helius
tokens as (
select
value:mint::string as token,
value:amount / pow(10,value:decimals) as amount
from rawbals,
lateral flatten (input => response:data:tokens)
),
nativesol as (
QueryRunArchived: QueryRun has been archived