-- Aggregate weekly totals from both tables within the last 3 years

SELECT

COALESCE(d.week_start, b.week_start) AS week_start,

d.total_supply,

b.total_borrow

FROM (

SELECT

DATE_TRUNC('WEEK', block_timestamp) AS week_start,

SUM(amount) AS total_supply

FROM ethereum.defi.ez_lending_deposits

WHERE block_timestamp >= DATEADD(YEAR, -3, CURRENT_DATE)

GROUP BY DATE_TRUNC('WEEK', block_timestamp)

) d

FULL JOIN (

SELECT

DATE_TRUNC('WEEK', block_timestamp) AS week_start,

SUM(amount) AS total_borrow

FROM ethereum.defi.ez_lending_borrows

WHERE block_timestamp >= DATEADD(YEAR, -3, CURRENT_DATE)

GROUP BY DATE_TRUNC('WEEK', block_timestamp)

) b

ON d.week_start = b.week_start

),

constants AS (

-- Define constants (adjust as needed)

SELECT

31536000.0 AS seconds_per_year, -- Number of seconds in a year

0.02 AS base_borrow_rate, -- Base borrow rate (e.g., 2%)

0.1 AS slope_low, -- Slope when utilization is below target

0.3 AS slope_high, -- Slope when utilization is above target

0.8 AS target_utilization, -- Target utilization ratio (e.g., 80%)

0.1 AS reserve_factor -- Reserve factor (e.g., 10%)

),

calc AS (

-- Compute utilization and carry constants