In this activity, use Space and Time Studio and a few AI prompts to generate both a functioning query and new chart.

Create an AI Query

You can generate queries using either standard database SQL (structured query language), or using SXT AI-SQL, simply use natural language and let AI write your query for you! This is great for people who don't know SQL, for people learning SQL, and even for advanced users who know SQL very well, but want a quick-start to their project!

--show me a comparison between the TPS of Polygon and the TPS of Ethereum, by hour across a rolling 14 days. Combine the date and hours into a timestamp, and display only the first 13 characters.
SELECT 
    substr(time_stamp,1,13) as hour_period,
    'Ethereum' AS blockchain,
    COUNT(transaction_hash) / 3600 AS tps
FROM ETHEREUM.TRANSACTIONS
WHERE time_stamp BETWEEN current_date- 14 AND current_date
GROUP BY hour_period, blockchain
  UNION ALL
SELECT 
    substr(time_stamp,1,13) as hour_period,
    'Polygon' AS blockchain,
    COUNT(transaction_hash) / 3600 AS tps
FROM POLYGON.TRANSACTIONS
WHERE time_stamp BETWEEN  current_date- 14 AND current_date
GROUP BY hour_period, blockchain
ORDER BY hour_period, blockchain
LIMIT 1000;

📘

Don't be afraid to boss the AI around! Tell it exactly what you want, and how you want it. The more descriptive, the better!

The AI-SQL engine is generative, meaning it will regenerate a new SQL statement every request, and there can be some differences in the SQL results it creates. However, it should look something like the image on the right.

🚧

AI will likely add a LIMIT to the bottom of the query, which limits the data return to a defined number of rows, but may sometimes under-shoot how many rows to expect.

You should have around 336 rows (24 * 14) or twice that number, 672, depending on how the AI wrote the query. If not, increase the LIMIT at the bottom of the query to 1000 and "Run Query" again.

👍

Congrats! You've generated your first AI-SQL query!

Create a Chart From Your Data

Once you (or AI, or both) have used Space and Time Studio to create a query and you've checked your data in the "Query Results" tab, click on the "Query Visualization" tab between the query editor and data grid - this will show you the top 3 AI-designed charts that you can pick from, or, you can also click "Explore More Visualizations" to see additional choices. The example above produced a total of 5, but your number may vary. If you see a chart you like as-is, you can simply click the Save icon. Or click on the Configure icon (gear in front of a column chart) and open up the chart configuration window. You can also save first, and configure at anytime later.

Let's pick a chart we like, and polish it up a little bit. For the purposes of this demo, we selected the first line chart, and made it look like this:

🚧

Remember, the query, data, and chart are all created by generative AI, so it's possible your experience could be different. This is fine! It's about getting you familiar with creating charts in general, not specifically replicating this chart. Play around!

Once you've tweaked your chart to look how you want, let's save our work!

In the upper-left of this page you'll see an option to name both the query and the chart. You can chose to give them both the same name, or completely different names - but you must name both the chart and the query.

The reason you might want to name them slightly different: you can have one query that supports many charts. For example, we created a line-chart here, but we could also create a pie chart from the same query. By saving the query independent of the chart, we don't have to recreate a new query for every chart.

When creating dashboards, the "Chart Name" will be displayed to your dashboard users, so aim for a short-but-descriptive name.

All that said - you can always rename both queries and charts in the future without disruption, so you don't need to over-think this decision. For now, we'll use the name TPS by Hr, ETH vs Poly for both the query and chart name.

You may see a pop-up notification called "Performance Tip." We'll cover this in more detail later, for now you can simply "Save Chart As Is".

👍

Congrats! You've created your first chart!

Once your chart has been saved, the Space and Time Studio will drop you in the "My Visuals" workflow, under "Queries" tab. This makes it easy to add this new chart to any dashboards you've created, or to create a brand-new dashboard.

If you want to start a new query for a new chart, click on "Queries" tab at the top, and you're ready to begin again!

Rinse, Repeat

If you've followed along above, you should now have a query and chart comparing the TPS of Ethereum and Polygon. See if you can generate two more comparison charts, using the AI prompt (or optionally SQL) below:

Two Week Wallet Count

Let's look at the Wallet count of Ethereum vs Polygon for the same 2 week period:

AI Prompt:

show me a comparison of the count of Ethereum wallets and polygon wallets for last two weeks by date. Include only full days, and call the column Wallet_Date. Make a column for each blockchain. Always limit to 1000 rows.

Click here for help!

In the case below, the AI-SQL elected to generate a query that JOINs the two chains rather than UNIONs. In other words, created a different column for Ethereum and Polygon, instead of having a "Blockchain" column.

SQL

--show me a comparison of the count of Ethereum wallets and polygon wallets for last two weeks by date
SELECT 
    e.wallet_date AS wallet_date,
    COALESCE(e.wallet_count, 0) AS ethereum_wallet_count,
    COALESCE(p.wallet_count, 0) AS polygon_wallet_count
FROM (
    SELECT 
        DATE(time_stamp) AS wallet_date,
        COUNT(DISTINCT wallet_address) AS wallet_count
    FROM ETHEREUM.NATIVE_WALLETS
    WHERE time_stamp BETWEEN current_date -14 AND current_date
    GROUP BY DATE(time_stamp)
) e
FULL JOIN (
    SELECT 
        DATE(time_stamp) AS wallet_date,
        COUNT(DISTINCT wallet_address) AS wallet_count
    FROM POLYGON.NATIVE_WALLETS
    WHERE time_stamp BETWEEN current_date -14 AND current_date
    GROUP BY DATE(time_stamp)
) p
ON e.wallet_date = p.wallet_date
ORDER BY e.wallet_date
LIMIT 1000;

This is fine - in fact, having 2 different columns on the same row typically generates great charts with very little adjustment:

Note that the two different Y-axis series (Ethereum and Polygon) may end up on different scales, one on the right axis and one on the left axis. For example, configuring the last chart in the above picture, you'll see there are different scales, one on the right Y-axis (polygon) which goes from 0 to 700k, and one on the left (ethereum) which goes from 0 to 600k:

You can force both Y-axis to appear on the same y-axis, which normalizes the height of the bars to the same scale. To do this, scroll down to the lower-left of the configuration screen, in the "Data" section and change the Y1 "Y1-Axis to Plot" to be on the "right" side. With a few other tweaks, you can quickly end up with a solid final product:

Don't forget to save your chart and query! To be consistent with the above example, let's use the name:
Wallet Count, ETH vs Poly

Count of ERC20 Tokens

Finally, let's compare ERC20 tokens between Ethereum and Polygon:

show me a comparison in the distinct count of ERC20 tokens on Ethereum and on Polygon, for the last two weeks by date. Add a column called "blockchain" to display the chain name. Always limit to 1000 rows.

--show me a comparison in the distinct count of ERC20 tokens on Ethereum and on Polygon, for the last two weeks by date.
SELECT 
    DATE(e.time_stamp) AS date, 
    COUNT(DISTINCT e.contract_address) AS distinct_token_count, 
    'Ethereum' AS blockchain
FROM 
    ETHEREUM.ERC20_EVT_TRANSFER e
WHERE 
    e.time_stamp BETWEEN current_date -14 and current_date
GROUP BY 
    DATE(e.time_stamp)
UNION ALL
SELECT 
    DATE(p.time_stamp) AS date, 
    COUNT(DISTINCT p.contract_address) AS distinct_token_count, 
    'Polygon' AS blockchain
FROM 
    POLYGON.ERC20_EVT_TRANSFER p
WHERE 
    p.time_stamp BETWEEN current_date -14 and current_date
GROUP BY 
    DATE(p.time_stamp)
ORDER BY 
    date, blockchain
LIMIT 1000;

Again, don't forget to save your chart and query! To be consistent with the rest, let's use the name:
Token Count, ETH vs Poly

Share Your Work!

Get Credit:

You'll get credit for this activity if you:

• Have generated 3 queries and 3 charts, and shared them publicly.