winnie-fs3 copy
Updated 2023-03-16
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from 9a4c7399-95fb-4f3e-8bc2-186e4766f3e0
-- forked from c32647bd-f5b0-4b23-a0c3-f4015e884787
-- assuming people bought USDC at a discount right before payment,
-- how much $ did they save?
WITH
prices AS (
SELECT
recorded_hour,
case
WHEN id IN ('dai', '4943') then 'DAI'
ELSE 'USDC'
end AS token_symbol,
avg(close) AS price
FROM
crosschain.core.fact_hourly_prices
WHERE
recorded_hour > current_date - 5
AND id IN ('usd-coin', '3408', 'dai', '4943')
GROUP BY
recorded_hour,
token_symbol
),
hourly_repayments AS (
SELECT
'aave' AS protocol,
symbol AS token_symbol,
date_trunc('hour', block_timestamp) AS hour,
sum(repayed_tokens) AS tokens_repaid,
count(tx_hash) AS n_repayments
FROM
ethereum.aave.ez_repayments
WHERE
block_timestamp > current_date - 5
AND symbol IN ('USDC', 'DAI')
Run a query to Download Data