saber-jlweekly tx and wallets average on terra
Updated 2023-01-09Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with tab1 as (
SELECT
date_trunc('week',block_timestamp) as date,
CASE
when block_timestamp >= '2022-01-01' and block_timestamp < '2022-12-18' then 'other days'
when block_timestamp >= '2022-12-18' and block_timestamp <= '2023-01-03' then 'holidays'
end as time_frame,
COUNT(distinct tx_id) as tx_count,
count(distinct TX_SENDER) as uniqe_wallets
from terra.core.fact_transactions
where TX_SUCCEEDED = 'TRUE'
and block_timestamp >= '2022-01-01' and block_timestamp <= '2023-01-03'
group by 1,2)
select
date,
time_frame,
count(distinct date) as week_count,
sum(tx_count)/week_count as "avg tx count",
sum(uniqe_wallets)/week_count as "avg new wallets"
from tab1
group by 1,2
Run a query to Download Data