bucketed_lending_pool_summaries_for_company

Schema: lib_lending

Description

This function allows you to retrieve a time-series of lending pool summaries for all lending pools within a lending protocol. These summaries include total supply, total borrows, total reserves, and the price of the underlying token. This information can be useful for analyzing the overall health, utilization, and activity on a lending protocol over time.

Usage

Signature

lib_lending.bucketed_lending_pool_summaries_for_company(
  company text,
  start_time timestamptz default now() - '1 week'::interval,
  end_time timestamptz default now(),
  bucket_width interval default '1 day'::interval
)

Parameters

Name	Type	Description
company	text	The name of the company whose lending pool summaries you want to retrieve.
start_time	timestamptz	Only consider data points that occurred at or after this time. The assumption is lending pools are updated regularly, so you can generally set this to a value like now() - '1 week'::interval to get query results faster
end_time	timestamptz	Only consider data points that occurred at or before this time. Defaults to now()
bucket_width	interval	The size of the time bucket to use to group summary data for each lending pool. Only the latest values from each bucket are returned. Defaults to '1 hour'::interval

Results

The function returns a table with the following columns:

Name	Type	Description
contract	text	The address of the lending pool contract
symbol	text	The symbol of the lending pool
underlying_symbol	text	The symbol of the underlying token.
bucket_time	timestamptz	The data time based on the time of each datapoint bucketed using the provided bucket_width
price	numeric	The price of the underlying token.
total_supply	numeric	The total amount of underlying token supplied to the lending pool.
total_cash	numeric	The total amount of underlying token available for borrow.
total_borrows	numeric	The total amount of underlying token borrowed from the lending pool.
total_reserves	numeric	The total amount of underlying token held in reserve by the lending protocol.
total_supply_usd	numeric	The USD value of the total amount of underlying token supplied to the lending pool.
total_cash	numeric	The USD value of the total underlying token available for borrow.
total_borrows_usd	numeric	The USD value of the total underlying token borrowed from the lending pool.
total_reserves_usd	numeric	The USD value underlying token held in reserve by the lending protocol.

Examples

Get hourly lending pool summaries for all Hover lending pools for the last 24 hours

select *
from lib_lending.bucketed_lending_pool_summaries_for_company(
  'Hover',
  now() - '24 hours'::interval,
  now(),
	'1 hour'::interval
)
order by symbol, bucket_time;

Get hourly utlilization % for all Hover lending pools for the last 24 hours

This example renames fields to allow them to be shown in a line chart in our platform

select
	s.symbol as metric_name,
	s.bucket_time as time,
	round((s.total_borrows / (s.total_borrows + s.total_cash - s.total_reserves)) * 100, 2) as val
from lib_lending.bucketed_lending_pool_summaries_for_company(
  'Hover',
  now() - '24 hours'::interval,
  now(),
	'1 hour'::interval
) s
order by symbol, bucket_time;

That example can be simplified further for protocols where total_supply = s.total_borrows + s.total_cash - s.total_reserves:

select
	s.symbol as metric_name,
	s.bucket_time as time,
	round(s.total_borrows / s.total_supply * 100, 2) as val
from lib_lending.bucketed_lending_pool_summaries_for_company(
  'Hover',
  now() - '24 hours'::interval,
  now(),
	'1 hour'::interval
) s
order by symbol, bucket_time;