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.

Step 1: Accessing Advanced Mode

  1. 1.
    Log in to your Agnostic account.
  2. 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

Step 2: Writing SQL Queries

  1. 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. 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.

Step 3: Chart Configuration

  1. 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
  • By using the chart_z
  • Or by using a label on the Y-axis values like so chart_y_{LABEL} Example: chart_y_USDC and chart_y_DAI
  1. 2.
    Configure your SQL query to generate results that match the axis aliases you've defined.

A clickbait example 👀

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
Watch the result from the public chart USDC Transferred (Coinbase/Binance).

Step 4: Previewing and Saving

  1. 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. 2.
    Review the generated chart to ensure it accurately represents the data and visualization you intended to create.
  3. 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.