Skip to main content

Tips

This page includes helpful tips for getting to the data you want faster and more efficiently.

Ledger Works Library Functions​

Ledger Works provides a curated set of library functions, accessible via the lib schema. These functions encapsulate common operations we perform when analyzing financial, risk, and blockchain data. Using these functions can result in much simpler, less error-prone queries that leverage optimized functions efficiently getting the data you need.

Additional documentation on our library functions can be found here.

To view all currently available functions, execute the following SQL query from our search page:

SELECT * FROM lib.functions_directory();

Tips for Charting Data​

When querying data, the result set needs to be formatted in a specific way to be automatically put into charts (line, bar, etc.). To return a chartable result set, return at least these two columns:

  • time: The timestamp of each datapoint
  • val: The value of the datapoint

Optionally, to return a multi-series result set, add an additional column:

  • metric_name: The name of the series each datapoint is associated with

Tips for Querying Timeseries Data​

When working with timeseries data, it's important to keep in mind a few key tips to ensure your queries are efficient and performant. The key to efficient timeseries data querying is to minimize the amount of data that needs to be processed. By following these tips, you can ensure your queries remain performant.

Addresses are stored all-lowercase​

While there is merit in the checksummed format of addresses, we've found that doesn't necessarily apply to our use-case. Additionally, some data sources (e.g. certain block explorers, wallets, JRPC nodes, etc.) return address data in all-lowercase, all-uppercase, etc. To avoid user-facing complexity trying to figure out which casing your data source is using, we've decided to store addresses in all-lowercase. This means when you are filtering on an address ensure you use the all-lowercase version of an address. In our database, you can use the lower function to convert any address to lowercase. Example:

select lower('0x2f484AE898A0182B46C104C4a7529D5E08e68d68') as normalized_address;

Bound Your Queries​

Always try to bound your queries in time. Unbounded queries can lead to full table scans, which can be very slow and resource-intensive. For example, instead of querying all data, query data for a specific day, month, or year.

Simple Bound Example​

To bound your queries in time, you can use the now() function and the interval keyword. For example, to get data from the last 24 hours, you can use:

SELECT * FROM transactions
WHERE consensus_time BETWEEN (now() - interval '24 hours') AND now();

Duplicate Bounds on Join​

When joining tables, you may see performance improvements by specifying the time bound for each table in the join. For example, this query applies the time bound to both transactions and contract_calls tables individually.

SELECT * FROM transactions t
  JOIN contract_calls c ON t.transaction_hash = c.transaction_hash
WHERE t.consensus_time BETWEEN (now() - interval '24 hours') AND now()
AND c.consensus_time BETWEEN (now() - interval '24 hours') AND now()
AND c.contract = '<your contract address>';

Use Hyperfunctions​

We utilize TimescaleDB for time series data storage. This provides us with access to Timescale Hyperfunctions, a specialized set of functions designed for analyzing time-series data.

See this page for a list of commonly used hyperfunctions for reference. For a comprehensive list of Hyperfunctions, please refer to the TimescaleDB documentation site.

Be Specific​

The more specific your query, the faster it will run. Try to filter your data as much as possible. For example, if you only need data for a specific transaction or contract, make sure to include that in your query.

Limit Your Results​

If you only need a certain number of results, use the LIMIT clause in your query. This can prevent the database from returning more data than you need.

For example, to get the top 10 transactions by value, you can use:

SELECT * FROM transactions
ORDER BY consensus_time DESC
LIMIT 10;

Use the Right Timezone​

Make sure you're querying data in the correct timezone. Data on this platform is stored in UTC. Keep in mind if you're in a different timezone, you may need to account for this in your queries.

SELECT consensus_time AT TIME ZONE 'PST' FROM transactions;

This will return the consensus_time converted to Pacific Time.