piperTrades & Traders
Updated 2024-11-26
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
›
⌄
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,
CASE
WHEN TO_CURRENCY = 'factory/osmo1n6asrjy9754q8y9jsxqf557zmsv3s3xa5m9eg5/uspice' THEN 'Buy'
WHEN FROM_CURRENCY = 'factory/osmo1n6asrjy9754q8y9jsxqf557zmsv3s3xa5m9eg5/uspice' THEN 'Sell'
ELSE 'Other'
END AS trade_type,
COUNT(DISTINCT tx_id) AS trades,
SUM (trades) OVER (ORDER BY date) AS total_trades,
COUNT(DISTINCT trader) AS traders,
SUM(
CASE
WHEN TO_CURRENCY = 'factory/osmo1n6asrjy9754q8y9jsxqf557zmsv3s3xa5m9eg5/uspice' THEN ROUND(TO_AMOUNT/pow(10,6), 0)
WHEN FROM_CURRENCY = 'factory/osmo1n6asrjy9754q8y9jsxqf557zmsv3s3xa5m9eg5/uspice' THEN ROUND(FROM_AMOUNT/pow(10,6), 0)
END
) AS trade_amount,
SUM (trade_amount) OVER (ORDER BY date) AS total_trades_amount
FROM
osmosis.defi.fact_swaps
WHERE
TX_SUCCEEDED = TRUE
AND
(
from_currency = 'factory/osmo1n6asrjy9754q8y9jsxqf557zmsv3s3xa5m9eg5/uspice'
OR
to_currency = 'factory/osmo1n6asrjy9754q8y9jsxqf557zmsv3s3xa5m9eg5/uspice'
)
GROUP BY date, trade_type
ORDER BY date ASC
QueryRunArchived: QueryRun has been archived