-
Notifications
You must be signed in to change notification settings - Fork 80
Closed
Closed
Usage and Analytics#1348
Feature
Milestone
Description
- Api endpoint that returns daily spent in uakt, uusdc and total usd
Here's the sql query to fetch the daily spent per address:
WITH current_block AS (
SELECT MAX(height) as height
FROM block
),
daily_leases AS (
SELECT
d.date,
l.owner,
l.denom,
l.price,
l."createdHeight",
CASE
WHEN l.id IS NULL THEN 0
ELSE LEAST(d."lastBlockHeightYet", COALESCE(l."closedHeight", l."predictedClosedHeight")) -
GREATEST(d."firstBlockHeight", l."createdHeight")
END as blocks_in_day,
d."aktPrice"
FROM day d
LEFT JOIN lease l ON
l.owner = 'akash18andxgtd6r08zzfpcdqg9pdr6smks7gv76tyt6' AND -- INPUT ADDRESS HERE
l."createdHeight" < d."lastBlockHeightYet" AND
COALESCE(l."closedHeight", l."predictedClosedHeight") > d."firstBlockHeight"
),
daily_costs AS (
SELECT
date,
ROUND(CAST(SUM(CASE
WHEN denom = 'uakt'
THEN blocks_in_day * price / 1000000.0
ELSE 0
END) as numeric), 2) as daily_akt_spent,
ROUND(CAST(SUM(CASE
WHEN denom = 'uusdc'
THEN blocks_in_day * price / 1000000.0
ELSE 0
END) as numeric), 2) as daily_usdc_spent,
ROUND(CAST(SUM(CASE
WHEN denom = 'uakt'
THEN blocks_in_day * price * "aktPrice" / 1000000.0
WHEN denom = 'uusdc'
THEN blocks_in_day * price / 1000000.0
ELSE 0
END) as numeric), 2) as daily_usd_spent
FROM daily_leases
GROUP BY date
)
SELECT
d.date::DATE AS "Date",
COUNT(dl.owner) AS "Active Leases",
COALESCE(dc.daily_akt_spent, 0) as "Daily AKT Spent",
COALESCE(SUM(dc.daily_akt_spent) OVER (ORDER BY d.date), 0) as "Total AKT Spent",
COALESCE(dc.daily_usdc_spent, 0) as "Daily USDC Spent",
COALESCE(SUM(dc.daily_usdc_spent) OVER (ORDER BY d.date), 0) as "Total USDC Spent",
COALESCE(dc.daily_usd_spent, 0) as "Daily USD Spent",
COALESCE(SUM(dc.daily_usd_spent) OVER (ORDER BY d.date), 0) as "Total USD Spent"
FROM day d
LEFT JOIN daily_leases dl ON dl.date = d.date
LEFT JOIN daily_costs dc ON dc.date = d.date
GROUP BY
d.date,
dc.daily_akt_spent,
dc.daily_usdc_spent,
dc.daily_usd_spent
ORDER BY d.date DESC;
- Implement the usage metrics in the settings as per the following screenshots
Metadata
Metadata
Assignees
Labels
No labels
Type
Projects
Status
Released (in Prod)