mamad-5XN3k3User Distribution by Inscriptions copy
Updated 2023-08-05
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 User Distribution by Inscriptions @ https://flipsidecrypto.xyz/edit/queries/08222671-0596-4cd1-8113-c4a85dbc19de
with pricet as (
SELECT
--distinct SYMBOL,
date_trunc('day', hour) as pdays,
avg(PRICE) as prices
FROM ethereum.core.fact_hourly_token_prices
where SYMBOL ilike 'WBTC'
group by 1),
usert as (
SELECT
distinct outputs[0]:scriptPubKey:address as minter,
count(*) as Daily_Inscriptions,
--sum(Daily_Inscriptions) OVER (ORDER BY days asc) as cum_Inscriptions,
--count(distinct outputs[0]:scriptPubKey:address) as minters,
--sum(minters) OVER (ORDER BY days asc) as cum_minters,
sum(size) as Ord_Size_Usage,
sum(virtual_size) as Ord_vSize_Usage,
sum(fee) as fees_btc,
sum(fee*prices) as fees_usd
--sum(fees_btc) OVER (ORDER BY days asc) as cum_fees_btc,
--sum(fees_usd) OVER (ORDER BY days asc) as cum_fees_usd
from bitcoin.core.fact_transactions join pricet on pdays=BLOCK_TIMESTAMP::date
WHERE BLOCK_NUMBER > 767429
and hex LIKE '%0063036f726401%'
GROUP BY 1 )
select
avg(Daily_Inscriptions),
avg(fees_btc),
avg(fees_usd)
from usert
--group by 1
Run a query to Download Data