@devinwalsh / Aave TVL v3

SQL query

1WITH deposits_raw AS
2  (SELECT p.price,
3          p.minute,
4          d.evt_block_time,
5          (d._amount/1e18) AS amt_adj
6   FROM aave."LendingPool_evt_Deposit" d
7   INNER JOIN prices."usd" p ON d._reserve=p.contract_address
8   AND p.minute=date_trunc('minute', evt_block_time)),
9     deposits AS
10  (SELECT date_trunc('day', evt_block_time) AS DAY,
11          sum(price*amt_adj) AS amt
12   FROM deposits_raw
13   GROUP BY 1
14   ORDER BY DAY DESC),
15     redeem_raw AS
16  (SELECT p.price,
17          p.minute,
18          r.evt_block_time,
19          (r._amount/1e18) AS amt_adj
20   FROM aave."LendingPool_evt_RedeemUnderlying" r
21   INNER JOIN prices."usd" p ON r._reserve=p.contract_address
22   AND p.minute=date_trunc('minute', evt_block_time)),
23     redeems AS
24  (SELECT date_trunc('day', evt_block_time) AS DAY,
25          sum(price*amt_adj) AS amt...