This schema contains comprehensive datasets for tracking ALEX fundamental data across multiple metrics categories, including total value locked, market data, and token metrics for the leading DeFi protocol on the Stacks blockchain.

​
Available Tables

ALEX data is available in two main tables:
  • ez_metrics: Main aggregated metrics for the ALEX Protocol including TVL and market data
  • ez_metrics_by_chain: Chain-specific metrics (currently focused on Stacks blockchain)

​
Table Schema

​
Protocol Liquidity Metrics

Table NameColumn NameDescription
ez_metricstvlThe total value locked in ALEX protocol
ez_metricssourceData source identifier (always ‘Defillama’)

​
Chain-Specific Metrics

Table NameColumn NameDescription
ez_metrics_by_chaintvlTotal value locked on the specific chain
ez_metrics_by_chainchainThe blockchain (currently ‘stacks’)
ez_metrics_by_chainsourceData source identifier (always ‘Defillama’)

​
Market and Token Metrics

Table NameColumn NameDescription
ez_metricspriceThe price of ALEX token in USD
ez_metricsmarket_capThe market cap of ALEX token in USD
ez_metricsfdmcThe fully diluted market cap of ALEX token in USD
ez_metricstoken_volumeThe trading volume of ALEX token in USD
ez_metricstoken_turnover_circulatingThe turnover of ALEX token based on circulating supply
ez_metricstoken_turnover_fdvThe turnover of ALEX token based on fully diluted supply

​
Sample Queries

​
Basic Protocol Metrics Query

-- Pull fundamental protocol data for ALEX
SELECT
    date,
    tvl,
    price,
    market_cap,
    token_volume,
    source
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date ASC

​
TVL Growth Analysis

-- Analyze ALEX TVL growth and trends
SELECT
    date,
    tvl,
    LAG(tvl, 1) OVER (ORDER BY date) as prev_day_tvl,
    LAG(tvl, 7) OVER (ORDER BY date) as tvl_7d_ago,
    LAG(tvl, 30) OVER (ORDER BY date) as tvl_30d_ago,
    (tvl - LAG(tvl, 1) OVER (ORDER BY date)) / NULLIF(LAG(tvl, 1) OVER (ORDER BY date), 0) * 100 as tvl_change_1d,
    (tvl - LAG(tvl, 7) OVER (ORDER BY date)) / NULLIF(LAG(tvl, 7) OVER (ORDER BY date), 0) * 100 as tvl_growth_7d,
    (tvl - LAG(tvl, 30) OVER (ORDER BY date)) / NULLIF(LAG(tvl, 30) OVER (ORDER BY date), 0) * 100 as tvl_growth_30d
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

​
Market Performance vs TVL Analysis

-- Analyze ALEX token market performance vs protocol TVL
SELECT
    date,
    tvl,
    price,
    market_cap,
    token_volume,
    token_turnover_circulating,
    market_cap / NULLIF(tvl, 0) as mcap_to_tvl_ratio,
    token_volume / NULLIF(market_cap, 0) * 100 as daily_volume_to_mcap_ratio,
    token_turnover_circulating * 100 as daily_token_turnover_percentage
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
    AND tvl > 0
ORDER BY
    date ASC

​
TVL Moving Averages

-- Track ALEX TVL with moving averages for trend analysis
SELECT
    date,
    tvl,
    AVG(tvl) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as tvl_7d_avg,
    AVG(tvl) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as tvl_30d_avg,
    AVG(tvl) OVER (ORDER BY date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) as tvl_90d_avg,
    tvl / NULLIF(AVG(tvl) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), 0) as tvl_vs_30d_avg_ratio
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
ORDER BY
    date ASC

​
Token Market Analysis

-- Analyze ALEX token market metrics and liquidity
SELECT
    date,
    price,
    market_cap,
    fdmc,
    token_volume,
    token_turnover_circulating,
    token_turnover_fdv,
    tvl,
    -- Token valuation metrics
    market_cap / NULLIF(fdmc, 0) * 100 as circulating_percentage,
    token_volume / NULLIF(market_cap, 0) * 100 as volume_to_mcap_ratio,
    -- Protocol correlation
    market_cap / NULLIF(tvl, 0) as mcap_tvl_ratio,
    tvl / NULLIF(token_volume, 0) as tvl_to_volume_ratio
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -2, CURRENT_DATE())
    AND price > 0
ORDER BY
    date ASC

​
Chain-Specific TVL Analysis

-- Analyze TVL by chain (currently Stacks-focused)
SELECT
    date,
    chain,
    tvl,
    source,
    LAG(tvl, 7) OVER (PARTITION BY chain ORDER BY date) as tvl_7d_ago,
    (tvl - LAG(tvl, 7) OVER (PARTITION BY chain ORDER BY date)) / NULLIF(LAG(tvl, 7) OVER (PARTITION BY chain ORDER BY date), 0) * 100 as tvl_growth_7d
FROM
    art_share.alex.ez_metrics_by_chain
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
    date DESC, tvl DESC

​
Protocol Performance Summary

-- Weekly aggregated performance summary for ALEX
SELECT
    DATE_TRUNC('week', date) as week,
    AVG(tvl) as avg_weekly_tvl,
    MAX(tvl) as peak_weekly_tvl,
    MIN(tvl) as min_weekly_tvl,
    AVG(price) as avg_token_price,
    SUM(token_volume) as total_token_volume,
    AVG(market_cap) as avg_market_cap,
    AVG(market_cap / NULLIF(tvl, 0)) as avg_mcap_tvl_ratio
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -3, CURRENT_DATE())
    AND tvl > 0
GROUP BY
    DATE_TRUNC('week', date)
ORDER BY
    week DESC

​
TVL Volatility Analysis

-- Analyze TVL volatility and stability
WITH tvl_stats AS (
    SELECT
        date,
        tvl,
        AVG(tvl) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as tvl_30d_avg,
        STDDEV(tvl) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as tvl_30d_stddev
    FROM
        art_share.alex.ez_metrics
    WHERE
        date >= DATEADD(month, -6, CURRENT_DATE())
)
SELECT
    date,
    tvl,
    tvl_30d_avg,
    tvl_30d_stddev,
    (tvl - tvl_30d_avg) / NULLIF(tvl_30d_stddev, 0) as tvl_z_score,
    tvl_30d_stddev / NULLIF(tvl_30d_avg, 0) * 100 as tvl_coefficient_of_variation,
    CASE
        WHEN ABS((tvl - tvl_30d_avg) / NULLIF(tvl_30d_stddev, 0)) > 2 THEN 'High Volatility'
        WHEN ABS((tvl - tvl_30d_avg) / NULLIF(tvl_30d_stddev, 0)) > 1 THEN 'Medium Volatility'
        ELSE 'Low Volatility'
    END as volatility_category
FROM
    tvl_stats
WHERE
    tvl_30d_stddev IS NOT NULL
ORDER BY
    date DESC

​
Market Cap to TVL Ratio Analysis

-- Analyze the relationship between market cap and TVL over time
SELECT
    date,
    tvl,
    market_cap,
    price,
    market_cap / NULLIF(tvl, 0) as mcap_tvl_ratio,
    -- Moving averages of the ratio
    AVG(market_cap / NULLIF(tvl, 0)) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as mcap_tvl_ratio_7d,
    AVG(market_cap / NULLIF(tvl, 0)) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as mcap_tvl_ratio_30d,
    -- Percentile ranking
    PERCENT_RANK() OVER (ORDER BY market_cap / NULLIF(tvl, 0)) as mcap_tvl_percentile
FROM
    art_share.alex.ez_metrics
WHERE
    date >= DATEADD(month, -6, CURRENT_DATE())
    AND tvl > 0
    AND market_cap > 0
ORDER BY
    date ASC

​
Monthly Protocol Summary

-- Monthly summary of ALEX protocol performance
WITH monthly_summary AS (
    SELECT
        DATE_TRUNC('month', date) as month,
        AVG(tvl) as avg_tvl,
        MAX(tvl) as peak_tvl,
        MIN(tvl) as min_tvl,
        AVG(price) as avg_price,
        AVG(market_cap) as avg_market_cap,
        SUM(token_volume) as total_token_volume,
        AVG(token_turnover_circulating) as avg_daily_turnover
    FROM
        art_share.alex.ez_metrics
    WHERE
        date >= DATEADD(month, -12, CURRENT_DATE())
    GROUP BY
        DATE_TRUNC('month', date)
)
SELECT
    month,
    avg_tvl,
    peak_tvl,
    avg_price,
    avg_market_cap,
    total_token_volume,
    avg_daily_turnover * 30 as estimated_monthly_turnover,
    -- Month-over-month growth
    (avg_tvl - LAG(avg_tvl, 1) OVER (ORDER BY month)) / NULLIF(LAG(avg_tvl, 1) OVER (ORDER BY month), 0) * 100 as tvl_growth_mom,
    (avg_price - LAG(avg_price, 1) OVER (ORDER BY month)) / NULLIF(LAG(avg_price, 1) OVER (ORDER BY month), 0) * 100 as price_growth_mom
FROM
    monthly_summary
ORDER BY
    month DESC