0xsloanePooperscooper swaps and fees total
Updated 2025-03-09
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 fees_to_pooperscooper AS (
SELECT ft.tx_id
, ft.block_timestamp
, DATE_TRUNC('day', ft.block_timestamp) AS date
, ft.amount AS tokens_amt
, case when ft.mint = 'So11111111111111111111111111111111111111111'
then 'So11111111111111111111111111111111111111112' else ft.mint
end as token_mint
, ez.symbol
, ft.tx_from AS swapper
, ft.amount * p.price AS fees_usd
FROM solana.core.fact_transfers ft
INNER JOIN solana.price.ez_asset_metadata ez
ON token_mint = ez.token_address
INNER 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 BETWEEN DATEADD(day, -{{days_to_look_back}}, current_date()) AND current_date() - 1
-- )
-- , fees_to_pooperscooper AS (
-- SELECT tx_id
-- , SUM(fees_usd)
-- FROM fees_to_pooperscooper
)
SELECT
--fs.BLOCK_TIMESTAMP::DATE AS block_date
SUM(COALESCE(btv.fees_usd,0)) AS pooperscooper_fees_paid
--, SUM(COALESCE(swap_to_amount_usd,0)) AS swap_amount_usd
, COUNT(DISTINCT btv.tx_id) AS cnt_tx
, COUNT(DISTINCT btv.swapper) AS cnt_scoopers
FROM fees_to_pooperscooper btv
-- ON fs.tx_id = btv.tx_id
WHERE TRUE
QueryRunArchived: QueryRun has been archived