apostleoffinanceDates of Base Overtaking Optimism in Active Users
Updated 2023-09-04
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 base_cte AS (
SELECT
date_trunc('day', block_timestamp) AS Date,
COUNT(DISTINCT to_address) AS Base_Active_Users,
COUNT(tx_hash) AS Number_of_Transaction,
SUM(tx_fee) AS ETH_Fee,
SUM(eth_value) AS Trading_Volume
FROM base.core.fact_transactions
WHERE Date BETWEEN '2023-08-09' AND CURRENT_DATE
GROUP BY 1
),
optimism_cte AS (
SELECT
date_trunc('day', block_timestamp) AS Date,
COUNT(DISTINCT to_address) AS Optimism_Active_Users,
COUNT(tx_hash) AS Number_of_Transaction,
SUM(tx_fee) AS ETH_Fee,
SUM(eth_value) AS Trading_Volume
FROM optimism.core.fact_transactions
WHERE Date BETWEEN '2023-08-09' AND CURRENT_DATE
GROUP BY 1
)
SELECT
CASE WHEN base_cte.Base_Active_Users > optimism_cte.Optimism_Active_Users
THEN base_cte.Date
ELSE NULL -- If Base active users don't surpass Optimism, return NULL
END AS Date_When_Base_Surpasses_Optimism,
--base_cte.Date AS Date,
base_cte.Base_Active_Users AS Base_Active_Users,
optimism_cte.Optimism_Active_Users AS Optimism_Active_Users,
Base_Active_Users - Optimism_Active_Users AS Active_User_Delta
--base_cte.Trading_Volume AS Base_Trading_Volume,
--optimism_cte.Trading_Volume AS Optimism_Trading_Volume,
--base_cte.Number_of_Transaction AS Base_Number_of_Transaction,
Run a query to Download Data