Data to Queries: Exploring Starknet Data with Dune

Dune has recently integrated Starknet, one of Ethereum's most promising Layer 2. This integration allows you to easily interact with, visualize, query, and analyze data from Starknet directly on Dune.

If you're looking to create dashboards, you may want to check out my detailed guide: Queries to Insights: Visualizing Starknet Data with Dune.

This article will walk you through querying Starknet data on Dune, which you can use to create insightful dashboards.

Accessing Starknet's Data

Dune transforms raw blockchain data into SQL databases, enabling you to write DuneSQL queries to retrieve and analyze the data, visualize it with dashboards, develop data-driven applications, and many more.

1. Open the "Create" dropdown menu in Dune's navigation bar

2. Choose "New Query" from the options

3. You'll be directed to the "New Query" page

4. Click "Raw Blockchain Data" on the Data Explorer

5. Search and click on "starknet"

You can now explore and filter all Starknet-related tables, accessing data on blocks, calls, on-chain events, and transactions.

6. Explore the data tables

You can also filter columns within any table. In the image below, you can see the data types of the fields and the SQL editor on the right side for writing queries.

Take some time to explore the available data on Dune. When ready, you can start writing queries to extract the needed data.

Writing DuneSQL Queries for Starknet

DuneSQL is an easy-to-read query language similar to other SQL variations. To learn more about DuneSQL and its features, check out the Dune documentation.

1. Query Transaction Details by Hash

Let's write a query to retrieve the details of a transaction by its hash:

SELECT
  *
FROM
  starknet.transactions
WHERE
  transaction_hash = 0x0716bd0f37d806ceb45dff6d4d0620034c1d127df8a619e6febe9dfc034ef33e;

This query fetches all the columns for the transactions table with a specified transaction_hash. Click the "Run" button to execute:

You can create parameters for your queries that allow you to adjust variables on the fly and explore data dynamically:

SELECT
  *
FROM
  starknet.transactions
WHERE
  transaction_hash = {{tx_hash::text}};

2. Querying Latest Block Details

Let's write a query to retrieve the details of the latest block on Starknet:

SELECT
  time,
  number,
  tx_count,
  hash
FROM
  starknet.blocks
ORDER BY
  date DESC
LIMIT
  1;

This query fetches the creation time, block number, transaction count, and hash of the latest created block on Starknet. Click the "Run" button to execute:

3. Sorting Smart Contracts by Transaction Count

Let's write a more practical query that fetches the top contracts on Starknet by the number of transactions over the last 30 days:

SELECT
  contract_address,
  COUNT(*) AS transaction_count
FROM
  starknet.transactions
WHERE
  block_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND contract_address IS NOT NULL
GROUP BY
  contract_address
HAVING
  COUNT(*) > 0
ORDER BY
  transaction_count DESC
LIMIT
  10;

Explanation of the query:

• SELECT: The query retrieves two columns: contract_address and a count of all transactions associated with each contract_address, labeled transaction_count.
• FROM: The data is pulled from the transactions table within the starknet schema.
• WHERE:
  • Filters the transactions to include only those that occurred within the last 30 days (block_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY).
  • Excludes rows where contract_address is NULL to ensure you only count transactions tied to an actual contract address.
• GROUP BY: Groups the results by contract_address so that the COUNT function aggregates the number of transactions per contract address.
• HAVING: This condition ensures that only contract addresses with at least one transaction (HAVING COUNT(*) > 0) are included in the results. Although the WHERE clause already filters out NULL addresses, this is an additional safeguard.
• ORDER BY: Orders the grouped results by transaction_count in descending order, so the most active contract addresses appear first.
• LIMIT: Limits the output to the top 10 contract addresses with the highest transaction counts.

4. Retrieving $STRK Token Price

You can also query the price of tokens on Dune! Here's a modified query, inspired by the Starknet Overview Dashboard, that retrieves the price of Starknet's $STRK token:

SELECT
  DATE(minute) AS "Date",
  AVG(price) AS "STRK Price"
FROM
  prices.usd
WHERE
  blockchain = 'ethereum'
  AND contract_address = 0xca14007eff0db1f8135f4c25b34de49ab0d42766
GROUP BY
  1
ORDER BY
  "Date"

Visualizing Results From Your Queries

You can take your queries a step further by visualizing their generated data and even adding it to a dashboard. We'll be visualizing the query that fetches $STRK prices.

1. Click the "New" button beside "Query Results"

2. Select the chart type you want to create

3. Click the "Add Visualization" Button

4. Explore Dune's various visualizations

You can take your queries and visualizations further by adding them to a dashboard, allowing the data to flow and tell compelling stories.

Starknet Resources on Dune

Dune provides a growing selection of Starknet-specific queries and dashboards, offering valuable insights into the ecosystem. One standout resource is the Caravanserai page, featuring in-depth dashboards and queries of various Starknet metrics: https://dune.com/caravanserai

For more Starknet-related content, use Dune's search function to discover queries, dashboards, and contributors: https://dune.com/discover/content/relevant?q=starknet. As Starknet evolves, we can expect these analytics resources on Dune to expand, offering even deeper insights into its on-chain data.

Conclusion

In this article, we've explored how to query and visualize Starknet data on Dune and how to create dynamic queries by creating parameters for them.

If you're interested in analyzing Starknet data, finding opportunities in the ecosystem, or sharing valuable insights, join our MEV discussion group to connect with like-minded individuals.