0xsloaneBONKscooper from token symbol by Tx cnt
Updated 2025-03-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
›
⌄
-- forked from BONKinferno from token symbol swap volume @ https://flipsidecrypto.xyz/studio/queries/9e230095-5216-4ad0-8054-32075680ed33
-- forked from Pooperscooper from token symbol swap volume copy @ https://flipsidecrypto.xyz/studio/queries/037a01a9-0bd6-4783-b4f8-d65e110fe7bf
WITH fees_to_pooperscooper AS (
SELECT ft.tx_id
, ft.block_timestamp
, DATE_TRUNC('hour', ft.block_timestamp) AS date_hour
, ft.amount AS tokens_amt
, case when ft.mint = 'So11111111111111111111111111111111111111111'
then 'So11111111111111111111111111111111111111112' else ft.mint
end as token_mint
, p.symbol
, ft.amount * p.price AS fees_usd
FROM solana.core.fact_transfers ft
left JOIN solana.price.ez_prices_hourly p
ON token_mint = p.token_address
AND DATE_TRUNC('hour', ft.block_timestamp) = p.hour
WHERE tx_to IN (
'2Vi8WzFHAAVNjtAquByvdzzpw8p4MuXhkQyBxs4qSVxw'
)
AND ft.block_timestamp::DATE >= '2024-01-11'
)
, fees_to_pooperscooper AS (
SELECT tx_id
, fees_usd
FROM fees_to_pooperscooper
)
SELECT
--fs.BLOCK_TIMESTAMP::DATE AS block_date
COALESCE(swap_from_symbol, '*UNKNOWN*') AS swap_from_symbol
-- , round(SUM(COALESCE(btv.fees_usd,0)),2) AS pooperscooper_fees_paid
-- , SUM(COALESCE(swap_to_amount_usd,0)) AS swap_amount_usd
, COUNT(DISTINCT fs.tx_id) AS cnt_tx
, COUNT(DISTINCT fs.swapper) AS cnt_scoopers
, DIV0(COUNT(DISTINCT btv.tx_id), COUNT(DISTINCT fs.swapper)) AS avg_swaps_per_user
QueryRunArchived: QueryRun has been archived