marquHelio Sales - agg copy
Updated 2024-09-27
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
purchase_txs as (
select
*
, sum(units) over (partition by buyer order by block_timestamp) as units_cumul
from
(select
block_timestamp
, tx_id
, tx_from as buyer
, amount
, mint
, round(amount / 450, 2) as units
from solana.core.fact_transfers
WHERE tx_to = '5EiuwvpjXx44QRi5ro48DwBkUeaQ3VHNKVtkLdZJy9P3'
and mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' -- USDC
and block_timestamp > '2023-12-22'
and block_timestamp <= '2024-07-22'
and block_id < 279067277
union all
select
block_timestamp
, tx_id
, signers[1] as buyer
, utils.udf_hex_to_int(to_char(reverse(to_binary(substr(utils.udf_base58_to_hex(instruction :data), 3 + 16, 16))))) / pow(10, 6) as amount -- thank you @FlyingFish for the decoding!
, instruction :accounts[8] ::string as mint
, round(amount / 450, 2) as units
QueryRunArchived: QueryRun has been archived