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