elenahooMedian sale price vs. min-sale ratio
Updated 2021-08-29
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
›
⌄
with mint AS (
select
year(block_timestamp) || '-' || month(block_timestamp) as date,
count(ethereum.nft_metadata.token_id) as mint_count
from ethereum.nft_events
left join ethereum.nft_metadata on ethereum.nft_events.token_id = ethereum.nft_metadata.token_id
and ethereum.nft_events.contract_address = ethereum.nft_metadata.contract_address
where ethereum.nft_events.contract_address in ('0x059edd72cd353df5106d2b9cc5ab83a52287ac3a','0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270')
and token_metadata:collection_name is not null
and event_type = 'mint'
and price is not null
group by date
order by date
),
sale as (
select
year(block_timestamp) || '-' || month(block_timestamp) as date,
count(ethereum.nft_metadata.token_id) as sale_count
from ethereum.nft_events
left join ethereum.nft_metadata on ethereum.nft_events.token_id = ethereum.nft_metadata.token_id
and ethereum.nft_events.contract_address = ethereum.nft_metadata.contract_address
where ethereum.nft_events.contract_address in ('0x059edd72cd353df5106d2b9cc5ab83a52287ac3a','0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270')
and token_metadata:collection_name is not null
and event_type = 'sale'
and price is not null
group by date
order by date
),
mint_sale_ratio AS (
select sale.date
, mint.mint_count / sale.sale_count as mint_sale_ratio
from sale left join mint on sale.date = mint.date
),
medium_price AS(
select
year(block_timestamp) || '-' || month(block_timestamp) as date,