Comment on page
🧙
Advanced mode
With the Advanced Mode, you can build your charts with the SQL language over the datasets available in Agnostic. This guide will walk you through the steps to harness the full potential of this powerful tool.
Before you dive into the Advanced Mode, please make sure you have the necessary access permissions to create a chart and a good understanding of SQL, as this mode requires SQL query input.
- 1.Log in to your Agnostic account.
- 2.From the project page, locate the "Create" > "Chart" button then select "Advanced Mode" and name your chart and click on "Create". This will open the Advanced Mode interface, which is essentially an integrated development environment (IDE) for writing SQL queries and visualizing the results as charts.
Create advanced chart modal
- 1.In the Advanced Mode interface, you'll see a text editor where you can write SQL queries. Start by composing your query based on the dataset available in Agnostic. You can use standard SQL syntax, including SELECT statements, JOINs, GROUP BY, and more.
- 2.As you type, you'll benefit from autocomplete suggestions for SQL keywords, tables, and columns from the Agnostic dataset. This feature streamlines the query-writing process and helps prevent typos and errors.
- 1.To create a chart, you need to define how the SQL query results will be visualized. Agnostic's Advanced Mode relies on aliases to designate the chart's axes:
Axis | Aliases |
|---|---|
X-axis | Use the chart_x alias to define your X values |
Y-axis | Use the chart_y alias to define your Y values |
Z-axis (Optional) | There are two ways to define the Z-axis
|
- 2.Configure your SQL query to generate results that match the axis aliases you've defined.
Let's create a chart showing the number of USDC transferred to Coinbase vs. Binance by week from May 2021.
select
'Coinbase' as chart_z,
date_trunc('week', timestamp) as chart_x,
sum(input_2_value_uint256) as chart_y
from
evm_events_ethereum_mainnet
where
timestamp >= '2021-05-01' and
address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' and -- USDC token Address
signature = 'Transfer(address,address,uint256)' and
input_1_value_address in (
'0x71660c4005BA85c37ccec55d0C4493E66Fe775d3', -- Coinbase 1
'0x503828976D22510aad0201ac7EC88293211D23Da', -- Coinbase 2
'0xddfAbCdc4D8FfC6d5beaf154f18B778f892A0740', -- Coinbase 3
'0x3cD751E6b0078Be393132286c442345e5DC49699', -- Coinbase 4
'0xb5d85CBf7cB3EE0D56b3bB207D5Fc4B82f43F511', -- Coinbase 5
'0xeB2629a2734e272Bcc07BDA959863f316F4bD4Cf', -- Coinbase 6
'0x02466E547BFDAb679fC49e96bBfc62B9747D997C', -- Coinbase 8
'0xA9D1e08C7793af67e9d92fe308d5697FB81d3E43', -- Coinbase 10
'0x77696bb39917C91A0c3908D577d5e322095425cA', -- Coinbase 11
'0x7c195D981AbFdC3DDecd2ca0Fed0958430488e34', -- Coinbase 12
'0x95A9bd206aE52C4BA8EecFc93d18EACDd41C88CC', -- Coinbase 13
'0xb739D0895772DBB71A89A3754A160269068f0D45' -- Coinbase 14
)
group by
chart_x
order by
chart_x asc
UNION ALL
select
'Binance' as chart_z,
date_trunc('week', timestamp) as chart_x,
sum(input_2_value_uint256) as chart_y
from
evm_events_ethereum_mainnet
where
timestamp >= '2021-05-01' and
address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' and -- USDC token Address
signature = 'Transfer(address,address,uint256)' and
input_1_value_address in (
'0x3f5CE5FBFe3E9af3971dD833D26bA9b5C936f0bE', -- Binance
'0xD551234Ae421e3BCBA99A0Da6d736074f22192FF', -- Binance 2
'0x564286362092D8e7936f0549571a803B203aAceD', -- Binance 3
'0x0681d8Db095565FE8A346fA0277bFfdE9C0eDBBF', -- Binance 4
'0xfE9e8709d3215310075d67E3ed32A380CCf451C8', -- Binance 5
'0x4E9ce36E442e55EcD9025B9a6E0D88485d628A67', -- Binance 6
'0xBE0eB53F46cd790Cd13851d5EFf43D12404d33E8', -- Binance 7
'0xF977814e90dA44bFA03b6295A0616a897441aceC', -- Binance 8
'0x001866Ae5B3de6cAa5a51543FD9fB64f524F5478', -- Binance 9
'0x85b931A32a0725Be14285B66f1a22178c672d69B', -- Binance 10
'0x708396f17127c42383E3b9014072679b2F60B82f', -- Binance 11
'0xE0F0CfDe7Ee664943906f17F7f14342E76A5CeC7', -- Binance 12
'0x8f22f2063d253846b53609231ed80fa571bc0c8f', -- Binance 13
'0x28C6c06298d514Db089934071355E5743bf21d60', -- Binance 14
'0x21a31Ee1afC51d94C2eFcCAa2092aD1028285549', -- Binance 15
'0xDFd5293D8e347dFe59E90eFd55b2956a1343963d' -- Binance 16
)
group by
chart_x
order by
chart_x asc
USDC Transferred
- 1.Once your SQL query and chart aliases are defined, click the "Play" button (on the top right corner of the IDE). Agnostic will process your query and generate a chart based on the specified aliases.
- 2.Review the generated chart to ensure it accurately represents the data and visualization you intended to create.
- 3.If everything looks good, you can save the chart within your Agnostic project by clicking the "Save" button.
Congratulations! You've successfully created a chart in Agnostic's Advanced Mode using SQL. This mode empowers you to leverage your SQL skills to craft customized visualizations from your blockchain data. Explore the possibilities, refine your queries, and unlock actionable insights with this advanced data visualization tool.
Last modified 2mo ago