> ## Documentation Index
> Fetch the complete documentation index at: https://artemis.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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
```