Query Referencing

Easily reference results from exiting queries!

Query Referencing allows you to streamline your analysis workflow by reusing the results of previously executed queries as data sources for new queries. This feature enhances query organization, promotes reusability of complex result sets, and significantly boosts query performance.

Overview

This section will cover:


How to reference a query?

  1. Identify the Query:

    1. Navigate to the desired query and click "Copy Reference" button to obtain the query ID.

    2. To reference your own query, find the query you want to reference under "My Work". Click the 3-dot menu, and select "Add to Query".

  2. Reference the Query:

    • In your new query, use the following syntax to reference the query:

      SELECT * 
      FROM $query('query_id')
  3. Join with other tables:

    • You can join the referenced query with other tables in your dataset:

      SELECT * 
      FROM bitcoin.core.fact_transactions t
      JOIN $query('query_id') AS my_query 
        ON my_query.blockheight = t.blockheight

Leveraging Query Referencing for Performance Optimization

A significant advantages of Query Referencing is its ability to dramatically improve the performance of parameterized queries.

  1. Pre-calculate the 100,000 most relevant addresses:

    select 
        from_address,
        count(1) as n_tx   -- can safely count(1) b/c this table is 1 row per tx_hash
    from ethereum.core.fact_transactions 
    where block_timestamp >= '2024-01-01'
    group by from_address
    having n_tx < 20001 -- assume > 20k is bots 
    order by n_tx desc 
    limit 100000;
  2. Reference the pre-calculated result:

    select * from 
    $query('0a0d54ca-f8de-43fe-8e5f-c270272982f9') -- query id taken from studio URL
    where from_address = '{{wallet_address}}';

How to decide which query to reference?

  1. Do Your Own Research (DYOR): Always verify the logic and accuracy of the referenced query.

Since all public queries can be referenced, make sure you trace query dependencies! Hover over any $query('query_id') and click on the query title in the popover to view all information about the underlying query.

  1. Consider Query Refresh Rates: Ensure that the referenced query is refreshed at an appropriate interval to provide up-to-date results.

  • To ensure up-to-date results, set a "Refresh Rate" on your base query

  • Any new query referencing the results will automatically use the refreshed data

  • Refreshes follow standard query refresh capabilities

  1. Understand Cache Expiration: Be aware of the cache duration and expiration rules for referenced queries.


Troubleshooting Errors

Error: Result for query {ID} has expired.

This error occurs when the results of a referenced query has expired.

How to resolve:

  1. If you own the referenced query:

    1. Set a refresh rate of at least once every 24 hours on the referenced query. This ensures that its results are consistently available to access.

  2. If you do NOT own the referenced query:

    1. Hover over the query ID in your SQL to see the details of the referenced query, click on the query title to open it.

    2. Fork the referenced query to create a copy under your ownership and set a refresh rate of at least once every 24 hours.

    3. Update the query ID in your current query with the ID of the forked query to ensure uninterrupted access to the results.


Limitations

Base queries that utilize parameters will always execute with their default values when referenced. Currently, there’s no mechanism to dynamically pass parameters into the $query() function. It is best to avoid using parameters in your base queries.

Last updated

Was this helpful?