Available Tables
Abstract data is available in the main metrics table:- ez_metrics: Aggregated metrics for the Abstract network including Layer 2 economics and fee distribution
Table Schema
Network and Usage Metrics
| Table Name | Column Name | Description |
|---|---|---|
| ez_metrics | chain_dau | Daily unique users on Abstract |
| ez_metrics | chain_txns | Daily transactions on Abstract |
| 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 | fees | The total USD value generated by Abstract from all user-paid fees |
| ez_metrics | fees_native | The total native value generated by Abstract from all user-paid fees |
| ez_metrics | fees | Same as fees (legacy naming) |
| ez_metrics | fees_native | Same as fees_native (legacy naming) |
Layer 2 Economics and Cash Flow Distribution
| Table Name | Column Name | Description |
|---|---|---|
| ez_metrics | l1_fee_allocation | The total USD value of L1 fee - fees paid to Ethereum for data posting and settlement |
| ez_metrics | l1_fee_allocation_native | The total native value of L1 fee - fees paid to Ethereum in native tokens |
| ez_metrics | foundation_fee_allocation | Revenue allocated to the Abstract foundation for grants, partnerships, operations, and legal expenses |
| ez_metrics | foundation_fee_allocation_native | Foundation revenue in native tokens |
| ez_metrics | cost | Same as l1_fee_allocation (legacy naming) |
| ez_metrics | cost_native | Same as l1_fee_allocation_native (legacy naming) |
| ez_metrics | revenue | Same as foundation_fee_allocation (legacy naming) |
| ez_metrics | revenue_native | Same as foundation_fee_allocation_native (legacy naming) |
Sample Queries
Basic Network Activity Query
-- Pull fundamental network activity data for Abstract
SELECT
date,
chain_txns,
chain_dau,
fees,
foundation_fee_allocation,
l1_fee_allocation
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
Layer 2 Economics Analysis
-- Analyze Abstract's Layer 2 economics and profitability
SELECT
date,
fees,
l1_fee_allocation,
foundation_fee_allocation,
l1_fee_allocation / NULLIF(fees, 0) * 100 as l1_cost_percentage,
foundation_fee_allocation / NULLIF(fees, 0) * 100 as foundation_percentage,
(fees - l1_fee_allocation) as net_l2_revenue,
foundation_fee_allocation / NULLIF((fees - l1_fee_allocation), 0) * 100 as foundation_of_net_revenue
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
AND fees > 0
ORDER BY
date ASC
Cash Flow Distribution Analysis
-- Track how Abstract distributes its revenue between L1 costs and foundation
SELECT
date,
fees,
l1_fee_allocation,
foundation_fee_allocation,
chain_txns,
l1_fee_allocation / NULLIF(chain_txns, 0) as l1_cost_per_txn,
fees / NULLIF(chain_txns, 0) as revenue_per_txn,
foundation_fee_allocation / NULLIF(chain_txns, 0) as foundation_revenue_per_txn
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND chain_txns > 0
ORDER BY
date ASC
Network Growth and Usage Trends
-- Track Abstract network growth and user adoption
SELECT
date,
chain_dau,
chain_txns,
fees,
chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
fees / NULLIF(chain_dau, 0) as revenue_per_user,
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.abstract.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
ORDER BY
date ASC
Revenue Efficiency Analysis
-- Analyze Abstract's revenue generation efficiency
SELECT
date,
fees,
foundation_fee_allocation,
l1_fee_allocation,
chain_txns,
chain_dau,
foundation_fee_allocation / NULLIF(fees, 0) * 100 as foundation_take_rate,
l1_fee_allocation / NULLIF(fees, 0) * 100 as l1_cost_rate,
(fees - l1_fee_allocation - foundation_fee_allocation) / NULLIF(fees, 0) * 100 as other_allocation_rate
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -1, CURRENT_DATE())
AND fees > 0
ORDER BY
date DESC
Foundation Revenue Tracking
-- Track foundation revenue and operational funding
SELECT
date,
foundation_fee_allocation,
foundation_fee_allocation_native,
fees,
chain_dau,
foundation_fee_allocation / NULLIF(chain_dau, 0) as foundation_revenue_per_user,
SUM(foundation_fee_allocation) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as foundation_revenue_30d,
AVG(foundation_fee_allocation) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as foundation_revenue_7d_avg
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -3, CURRENT_DATE())
ORDER BY
date ASC
L1/L2 Cost Analysis
-- Deep dive into Layer 1 vs Layer 2 cost structure
SELECT
date,
l1_fee_allocation,
l1_fee_allocation_native,
fees,
chain_txns,
l1_fee_allocation / NULLIF(chain_txns, 0) as l1_cost_per_transaction,
fees / NULLIF(chain_txns, 0) as total_revenue_per_transaction,
(fees - l1_fee_allocation) / NULLIF(chain_txns, 0) as net_l2_revenue_per_transaction,
l1_fee_allocation / NULLIF(fees, 0) * 100 as l1_cost_as_percentage_of_revenue
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -2, CURRENT_DATE())
AND chain_txns > 0
ORDER BY
date ASC
Daily Performance Summary
-- Comprehensive daily performance summary
SELECT
date,
chain_dau,
chain_txns,
fees,
l1_fee_allocation,
foundation_fee_allocation,
-- Efficiency metrics
chain_txns / NULLIF(chain_dau, 0) as txns_per_user,
fees / NULLIF(chain_txns, 0) as avg_fee_per_txn,
foundation_fee_allocation / NULLIF(fees, 0) * 100 as foundation_percentage,
l1_fee_allocation / NULLIF(fees, 0) * 100 as l1_cost_percentage
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(week, -4, CURRENT_DATE())
ORDER BY
date DESC
Revenue Breakdown Over Time
-- Track revenue allocation trends over time
SELECT
date,
fees,
l1_fee_allocation,
foundation_fee_allocation,
fees - l1_fee_allocation - foundation_fee_allocation as unallocated_revenue,
-- 30-day moving averages
AVG(l1_fee_allocation / NULLIF(fees, 0) * 100) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_l1_percentage_30d,
AVG(foundation_fee_allocation / NULLIF(fees, 0) * 100) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as avg_foundation_percentage_30d
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -6, CURRENT_DATE())
AND fees > 0
ORDER BY
date ASC
Network Economics Comparison
-- Compare different periods of network economics
WITH monthly_summary AS (
SELECT
DATE_TRUNC('month', date) as month,
SUM(fees) as total_revenue,
SUM(l1_fee_allocation) as total_l1_cost,
SUM(foundation_fee_allocation) as total_foundation_revenue,
AVG(chain_dau) as avg_dau,
SUM(chain_txns) as total_txns
FROM
art_share.abstract.ez_metrics
WHERE
date >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
month,
total_revenue,
total_l1_cost,
total_foundation_revenue,
avg_dau,
total_txns,
total_l1_cost / NULLIF(total_revenue, 0) * 100 as l1_cost_percentage,
total_foundation_revenue / NULLIF(total_revenue, 0) * 100 as foundation_percentage,
total_revenue / NULLIF(total_txns, 0) as avg_revenue_per_txn
FROM
monthly_summary
ORDER BY
month DESC