latest_bucketed_custom_metric_for_contract(s)

Schema: lib

Description

These functions retrieve the latest value in each time bucket for a custom metric for each provided contract address.

Examples this function can help address:

• Get the latest borrowRate for a single lending pool contract for each hour over the last 24 hours
• Get the latest borrowRate for a set of lending pool contracts for each hour over the last 24 hours
• Get the latest totalSupply for an ERC20 token for each day over the last 2 weeks

Two variants of this function exist:

• latest_bucketed_custom_metric_for_contract: Get the latest bucketed values for a single contract
• latest_bucketed_custom_metric_for_contracts: Get the latest bucketed values for a set of contracts

Usage

Signature

Single contract:

lib.latest_bucketed_custom_metric_for_contract(contract text, metric_name text, start_time timestamp with time zone, end_time timestamp with time zone, bucket_width interval)

Multiple contracts:

lib.latest_bucketed_custom_metric_for_contracts(contracts text[], metric_name text, start_time timestamp with time zone, end_time timestamp with time zone, bucket_width interval)

Parameters

Name	Type	Description
contract	text	The address(es) of the contracts to find data for.
metric_name	text	The name of the custom metric to find data for.
start_time	timestamptz	The starting point for the data search. Only data points that occurred at or after this time will be considered.
end_time	timestamptz	The endpoint for the data search. Only data points that occurred at or before this time will be included.
bucket_width	interval	The time window used to group the data. This allow downsampling to the latest datapoint for each bucket and for comparison of data points across regular time intervals (e.g. hour-over-hour).

Results

Name	Type	Description
contract	text	The address of the contract the custom metric corresponds to.
metric_name	text	The name of the custom metric
bucket_time	timestamptz	The time bucket that the data was grouped into.
val	numeric	The latest value in the time bucket for the custom metric.

Examples

Retrieve latest bucketed data for a specific contract and metric

select * from lib.latest_bucketed_custom_metric_for_contract('0x123abc', 'total_sales', '2021-01-01 00:00:00', '2021-01-31 23:59:59', '1 day');

Retrieve latest supplyRate a single contract

select *
from lib.latest_bucketed_custom_metric_for_contract(
	'0xce86ebc669bbf07a64a0a55bb105cc2b5b5d1961',
	'supplyRate',
	now() - '1 day'::interval,
	now(),
	'1 hour'
);

Retrieve latest totalSupply for multiple contracts

select *
from lib.latest_bucketed_custom_metric_for_contracts(
	array['0xce86ebc669bbf07a64a0a55bb105cc2b5b5d1961', '0x1bf92be674483d54a8e3a589e456c39eac4339bd'],
	'totalSupply',
	now() - '2 weeks'::interval,
	now(),
	'1 day'
);