Available Tables
Astar data is available in two main tables:- ez_metrics: Main aggregated metrics for the Astar Network
- ez_metrics_by_chain: Chain-specific metrics (currently focused on Astar network)
Table Schema
Network and Usage Metrics
| Table Name | Column Name | Description |
|---|---|---|
| ez_metrics | chain_dau | Daily unique users on Astar |
| ez_metrics | chain_txns | Daily transactions on Astar |
| ez_metrics | dau | Same as chain_dau (legacy naming) |
| ez_metrics | txns | Same as chain_txns (legacy naming) |
Fee and Revenue Metrics
| Table Name | Column Name | Description |
|---|---|---|
| ez_metrics | fees | The total USD value generated by Astar from all user-paid fees |
| ez_metrics | fees_native | The total native ASTR value generated by Astar from all user-paid fees |
| ez_metrics | fees | Same as fees (legacy naming) |
| ez_metrics | fees_native | Same as fees_native (legacy naming) |
Market and Token Metrics
| Table Name | Column Name | Description |
|---|---|---|
| ez_metrics | price | The price of ASTR token in USD |
| ez_metrics | market_cap | The market cap of ASTR token in USD |
| ez_metrics | fdmc | The fully diluted market cap of ASTR token in USD |
| ez_metrics | token_volume | The trading volume of ASTR token in USD |
| ez_metrics | token_turnover_circulating | The turnover of ASTR token based on circulating supply |
| ez_metrics | token_turnover_fdv | The turnover of ASTR token based on fully diluted supply |
Chain-Specific Metrics
| Table Name | Column Name | Description |
|---|---|---|
| ez_metrics_by_chain | chain | The blockchain identifier (currently ‘astar’) |
| ez_metrics_by_chain | chain_dau | Daily unique users on the specific chain |
| ez_metrics_by_chain | chain_txns | Daily transactions on the specific chain |
| ez_metrics_by_chain | fees | Revenue generated on the specific chain |
| ez_metrics_by_chain | fees_native | Revenue in native tokens on the specific chain |
| ez_metrics_by_chain | price | Token price for the specific chain |
| ez_metrics_by_chain | market_cap | Market cap for the specific chain |
| ez_metrics_by_chain | fdmc | FDMC for the specific chain |
| ez_metrics_by_chain | token_volume | Token trading volume for the specific chain |
Sample Queries
Basic Network Activity Query
-- Pull fundamental network activity data for Astar
SELECT
date,
chain_txns,
chain_dau,
fees,
fees_native,
price,
market_cap
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
Network Growth and User Engagement
-- Track Astar network growth and user adoption trends
SELECT
date,
chain_dau,
chain_txns,
fees,
chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
fees / NULLIF(chain_dau, 0) as revenue_per_user,
fees / NULLIF(chain_txns, 0) as avg_fee_per_txn,
LAG(chain_dau, 7) OVER (ORDER BY date) as dau_7d_ago,
LAG(chain_txns, 7) OVER (ORDER BY date) as txns_7d_ago,
(chain_dau - LAG(chain_dau, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_dau, 7) OVER (ORDER BY date), 0) * 100 as dau_growth_7d,
(chain_txns - LAG(chain_txns, 7) OVER (ORDER BY date)) / NULLIF(LAG(chain_txns, 7) OVER (ORDER BY date), 0) * 100 as txns_growth_7d
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
date ASC
Fee Structure and Economics
-- Analyze Astar's fee structure and economic model
SELECT
date,
fees,
fees_native,
chain_txns,
chain_dau,
price,
fees / NULLIF(chain_txns, 0) as avg_fee_per_txn_usd,
fees_native / NULLIF(chain_txns, 0) as avg_fee_per_txn_astr,
fees / NULLIF(chain_dau, 0) as revenue_per_user,
fees_native * price as calculated_revenue_usd
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND fees > 0
ORDER BY
date ASC
Market Performance vs Network Activity
-- Analyze ASTR token market performance vs network metrics
SELECT
date,
price,
market_cap,
fdmc,
token_volume,
chain_dau,
chain_txns,
fees,
token_turnover_circulating,
market_cap / NULLIF(fees, 0) as mcap_to_revenue_ratio,
token_volume / NULLIF(market_cap, 0) * 100 as daily_volume_to_mcap_ratio,
token_turnover_circulating * 100 as daily_token_turnover_percentage,
fees / NULLIF(chain_dau, 0) as revenue_per_user
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
Network Activity Trends
-- Track Astar network activity trends with moving averages
SELECT
date,
chain_dau,
chain_txns,
fees,
-- 7-day moving averages
AVG(chain_dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_dau_7d,
AVG(chain_txns) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_txns_7d,
AVG(fees) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_revenue_7d,
-- 30-day moving averages
AVG(chain_dau) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_dau_30d,
AVG(chain_txns) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_txns_30d,
AVG(fees) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_revenue_30d
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
Token Market Analysis
-- Analyze ASTR token market metrics and trading patterns
SELECT
date,
price,
market_cap,
fdmc,
token_volume,
token_turnover_circulating,
token_turnover_fdv,
fees,
-- Token valuation metrics
market_cap / NULLIF(fdmc, 0) * 100 as circulating_percentage,
token_volume / NULLIF(market_cap, 0) * 100 as volume_to_mcap_ratio,
-- Network correlation
market_cap / NULLIF(fees, 0) as mcap_per_dollar_revenue,
token_volume / NULLIF(fees, 0) as token_volume_to_revenue_ratio
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND price > 0
ORDER BY
date ASC
Chain-Specific Performance
-- Analyze performance by chain (currently Astar-focused)
SELECT
date,
chain,
chain_dau,
chain_txns,
fees,
fees_native,
price,
market_cap,
chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
fees / NULLIF(chain_txns, 0) as avg_fee_per_txn
FROM
art_share.astar.ez_metrics_by_chain
WHERE
date >= DATEADD(month, -1, CURRENT_DATE())
ORDER BY
date DESC, fees DESC
Network Efficiency Analysis
-- Analyze Astar network efficiency and utilization
SELECT
date,
chain_txns,
chain_dau,
fees,
fees_native,
price,
-- Efficiency metrics
chain_txns / NULLIF(chain_dau, 0) as avg_txns_per_user,
fees / NULLIF(chain_txns, 0) as revenue_per_txn,
fees_native / NULLIF(chain_txns, 0) as native_fee_per_txn,
-- Network utilization trends
LAG(chain_txns / NULLIF(chain_dau, 0), 7) OVER (ORDER BY date) as txns_per_user_7d_ago,
(chain_txns / NULLIF(chain_dau, 0)) - LAG(chain_txns / NULLIF(chain_dau, 0), 7) OVER (ORDER BY date) as txns_per_user_change_7d
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND chain_dau > 0
ORDER BY
date ASC
Weekly Performance Summary
-- Weekly aggregated performance summary for Astar
SELECT
DATE_TRUNC('week', date) as week,
AVG(chain_dau) as avg_daily_users,
SUM(chain_txns) as total_weekly_txns,
SUM(fees) as total_weekly_revenue,
SUM(fees_native) as total_weekly_revenue_native,
AVG(price) as avg_token_price,
SUM(token_volume) as total_token_volume,
AVG(market_cap) as avg_market_cap,
SUM(chain_txns) / NULLIF(AVG(chain_dau), 0) as avg_txns_per_user_per_day
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY
DATE_TRUNC('week', date)
ORDER BY
week DESC
Polkadot Ecosystem Analysis
-- Analyze Astar's performance within the Polkadot ecosystem context
SELECT
date,
chain_dau,
chain_txns,
fees,
price,
market_cap,
token_volume,
-- Growth metrics
(chain_dau - LAG(chain_dau, 30) OVER (ORDER BY date)) / NULLIF(LAG(chain_dau, 30) OVER (ORDER BY date), 0) * 100 as dau_growth_30d,
(fees - LAG(fees, 30) OVER (ORDER BY date)) / NULLIF(LAG(fees, 30) OVER (ORDER BY date), 0) * 100 as revenue_growth_30d,
-- Network health indicators
fees / NULLIF(chain_dau, 0) as revenue_per_user,
chain_txns / NULLIF(chain_dau, 0) as network_activity_ratio,
token_volume / NULLIF(market_cap, 0) * 100 as token_liquidity_ratio
FROM
art_share.astar.ez_metrics
WHERE
date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
date ASC