permaryAsset Transfer Volume
Updated 2025-02-18Copy Reference Fork
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 asset_transfers AS (
SELECT
DATE(CLOSED_AT) AS date,
COALESCE(ASSET_CODE, 'XLM') AS asset_code,
ASSET_ISSUER,
SUM(AMOUNT) / 10000000 AS total_transferred, -- Convert to standard units
COUNT(*) AS transfer_count,
l.label AS issuer_label
FROM stellar.core.fact_operations f
LEFT JOIN stellar.core.dim_labels l
ON f.asset_issuer = l.address
WHERE
TYPE_STRING = 'payment'
AND CLOSED_AT >= DATEADD('day', -30, CURRENT_DATE())
GROUP BY
date,
asset_code,
ASSET_ISSUER,
l.label
),
asset_totals AS (
SELECT
asset_code,
COALESCE(issuer_label, asset_issuer) AS issuer,
SUM(total_transferred) AS total_volume,
SUM(transfer_count) AS total_transactions
FROM asset_transfers
GROUP BY
asset_code,
COALESCE(issuer_label, asset_issuer)
)
SELECT
asset_code,
issuer,
ROUND(total_volume, 2) AS total_volume, -- Rounded to 2 decimal places
QueryRunArchived: QueryRun has been archived