hrst79lenrs by collction
Updated 2023-06-27
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 main copy @ https://flipsidecrypto.xyz/edit/queries/36d8b742-19dc-4e06-b5f1-c5e9307220fd
-- forked from main @ https://flipsidecrypto.xyz/edit/queries/db75511d-ba35-4242-b5aa-a6146731777a
with base as (
SELECT tx_hash ,
NFT_ADDRESS
from ethereum.core.fact_event_logs join ethereum.core.ez_nft_transfers using(tx_hash)
where CONTRACT_ADDRESS = lower('0x29469395eaf6f95920e59f858042f0e28d98a20b')
),
users as ( select
to_address,
from_address ,
tx_hash,
case when NFT_ADDRESS = '0xed5af388653567af2f388e6224dc7c4b3241c544' then 'Azuki'
when NFT_ADDRESS = '0xb7f7f6c52f2e2fdb1963eab30438024864c313f6' then 'crypto Punks'
when NFT_ADDRESS = '0x5af0d9827e0c53e4799bb226655a1de152a425a5' then 'Milady'
when NFT_ADDRESS = '0x8821bee2ba0df28761afff119d66390d594cd280' then 'Degods'
--when NFT_ADDRESS = '0x3235ba66bc9ff27efea7021112d593a92433668d' then 'Boners'
end as collection
from ethereum.core.fact_token_transfers join base using(tx_hash)
where NFT_address in ('0xed5af388653567af2f388e6224dc7c4b3241c544','0xb7f7f6c52f2e2fdb1963eab30438024864c313f6','0x5af0d9827e0c53e4799bb226655a1de152a425a5','0x8821bee2ba0df28761afff119d66390d594cd280')
)--,
--final as (
select
--date_trunc('day',block_timestamp)as date ,
count (DISTINCT from_address) as lenders,
count (DISTINCT To_address) as borrowers,
count (DISTINCT tx_hash) as loans,
sum(raw_amount/1e18) as loan_volume,
--sum (loan_volume) over (order by date asc ) as cum_volume,
--sum (loans) over (order by date asc ) as cum_loans,
loan_volume / loans as avg_volume_per_loan,
collection
from ethereum.core.fact_token_transfers a join users b using(from_address,tx_hash,to_address)
Run a query to Download Data