anomoneBalloonsville - Average Sales Price Daily
Updated 2022-05-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 label_balloon as(
SELECT ADDRESS FROM solana.labels
WHERE LABEL = 'balloonsville'
),
secondary_sales as(
SELECT tx_id, mint FROM solana.nfts
WHERE INSTRUCTION:programId = 'MEisE1HzehtrDpAAT8PnLHjpSSkRYakotTuJRPjTpo8'
AND array_size(inner_instruction:instructions) > 4 -- To filter only for secondary sales
AND date_trunc('day' , BLOCK_TIMESTAMP ) >= TO_DATE('2022-02-05')
),
SOL_price as (SELECT
CASE
WHEN AMOUNT_IN is not null then AMOUNT_USD/AMOUNT_IN
ELSE AMOUNT_USD/AMOUNT_OUT END as price
FROM ethereum.dex_swaps
WHERE LOWER(token_address) = LOWER('0xD31a59c85aE9D8edEFeC411D448f90841571b89c') --Wrapped SOL (Wormhole) token contract address
ORDER BY BLOCK_TIMESTAMP desc
LIMIT 1
),
flattened_table as
(select
BLOCK_TIMESTAMP,
BLOCK_ID,
BLOCKCHAIN,
RECENT_BLOCK_HASH,
n.TX_ID,
n.MINT,
SUCCEEDED,
INNER_INSTRUCTION:instructions,--:info:lamports::int as ffs,
lvl1.value as lvl1,
lvl2.value as lvl2,