ALEX

ALEX Schema Description and Methodology

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 Name	Column Name	Description
ez_metrics	tvl	The total value locked in ALEX protocol
ez_metrics	source	Data source identifier (always ‘Defillama’)

Chain-Specific Metrics

Table Name	Column Name	Description
ez_metrics_by_chain	tvl	Total value locked on the specific chain
ez_metrics_by_chain	chain	The blockchain (currently ‘stacks’)
ez_metrics_by_chain	source	Data source identifier (always ‘Defillama’)

Market and Token Metrics

Table Name	Column Name	Description
ez_metrics	price	The price of ALEX token in USD
ez_metrics	market_cap	The market cap of ALEX token in USD
ez_metrics	fdmc	The fully diluted market cap of ALEX token in USD
ez_metrics	token_volume	The trading volume of ALEX token in USD
ez_metrics	token_turnover_circulating	The turnover of ALEX token based on circulating supply
ez_metrics	token_turnover_fdv	The 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