FatemeTheLadyOSMO: 1
Updated 2022-10-19
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
›
⌄
-- What does a Daily Active User look like on Osmosis?
-- First, plot out how many DAUs there are on Osmosis.
-- For this exercise, consider a DAU would be any wallet transacting on Osmosis a majority of days every week.
-- What does a Daily Active User look like on Osmosis? First, plot out how many DAUs there are on Osmosis.
-- For this exercise, consider a DAU would be any wallet transacting on Osmosis a majority of days every week.
-- Once you have the DAUs, what does their activity look like?
-- Do they LP more or swap more?
-- How often do they transfer tokens into Osmosis?
-- Where are these transfers coming from?
-- Grand Prize 103.592 OSMO
-- Payout 69.061 OSMO
----------------------------------------------------------------------------------------------------------------
with new_osmosis_users AS (
SELECT
tx_from as address,
count(distinct tx_id) as tx_counts,
count(distinct date(block_timestamp)) as days_active,
min(date(block_timestamp)) as first_tx,
max(date(block_timestamp)) as last_tx,
datediff('day', last_tx, getdate()) as days_last_active,
datediff('day', first_tx, getdate()) as age_today
FROM osmosis.core.fact_transactions
group by 1)
,
new_users AS (
SELECT
first_tx,
count(distinct address) as "New Users",
sum("New Users") over (order by first_tx) as "Cumulative New users"
FROM new_osmosis_users
GROUP BY 1)
,
Run a query to Download Data