Astar

Astar Schema Description and Methodology

This schema contains comprehensive datasets for tracking Astar fundamental data across multiple metrics categories, including network activity, fees, user engagement, and market data for the multi-chain smart contract platform within the Polkadot ecosystem.

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	ecosystem_revenue	The total USD value generated by Astar from all user-paid fees
ez_metrics	ecosystem_revenue_native	The total native ASTR value generated by Astar from all user-paid fees
ez_metrics	fees	Same as ecosystem_revenue (legacy naming)
ez_metrics	fees_native	Same as ecosystem_revenue_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	ecosystem_revenue	Revenue generated on the specific chain
ez_metrics_by_chain	ecosystem_revenue_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,
    ecosystem_revenue,
    ecosystem_revenue_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,
    ecosystem_revenue,
    chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
    ecosystem_revenue / NULLIF(chain_dau, 0) as revenue_per_user,
    ecosystem_revenue / 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,
    ecosystem_revenue,
    ecosystem_revenue_native,
    chain_txns,
    chain_dau,
    price,
    ecosystem_revenue / NULLIF(chain_txns, 0) as avg_fee_per_txn_usd,
    ecosystem_revenue_native / NULLIF(chain_txns, 0) as avg_fee_per_txn_astr,
    ecosystem_revenue / NULLIF(chain_dau, 0) as revenue_per_user,
    ecosystem_revenue_native * price as calculated_revenue_usd
FROM
    art_share.astar.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
    AND ecosystem_revenue > 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,
    ecosystem_revenue,
    token_turnover_circulating,
    market_cap / NULLIF(ecosystem_revenue, 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,
    ecosystem_revenue / 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,
    ecosystem_revenue,
    -- 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(ecosystem_revenue) 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(ecosystem_revenue) 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,
    ecosystem_revenue,
    -- 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(ecosystem_revenue, 0) as mcap_per_dollar_revenue,
    token_volume / NULLIF(ecosystem_revenue, 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,
    ecosystem_revenue,
    ecosystem_revenue_native,
    price,
    market_cap,
    chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
    ecosystem_revenue / 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, ecosystem_revenue DESC

Network Efficiency Analysis

-- Analyze Astar network efficiency and utilization
SELECT
    date,
    chain_txns,
    chain_dau,
    ecosystem_revenue,
    ecosystem_revenue_native,
    price,
    -- Efficiency metrics
    chain_txns / NULLIF(chain_dau, 0) as avg_txns_per_user,
    ecosystem_revenue / NULLIF(chain_txns, 0) as revenue_per_txn,
    ecosystem_revenue_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(ecosystem_revenue) as total_weekly_revenue,
    SUM(ecosystem_revenue_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,
    ecosystem_revenue,
    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,
    (ecosystem_revenue - LAG(ecosystem_revenue, 30) OVER (ORDER BY date)) / NULLIF(LAG(ecosystem_revenue, 30) OVER (ORDER BY date), 0) * 100 as revenue_growth_30d,
    -- Network health indicators
    ecosystem_revenue / 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