AI Waifu2024-05-02 11:25 AM
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
›
⌄
-- Calculate total revenue from specified contracts
WITH total_revenue AS (
SELECT
SUM(livequery.utils.udf_hex_to_int(value) / POWER(10, 18)) AS total_revenue
FROM
blast.core.fact_transactions
WHERE
to_address IN ('0xca4a0be8d75435ae10eb5a98ca91d35740682fd0') -- Revenue sources
AND block_timestamp >= '2024-03-25' -- Adjust period as needed
),
-- Count unique users in the same period
unique_users AS (
SELECT
COUNT(DISTINCT origin_from_address) AS unique_users
FROM
blast.core.fact_transactions
WHERE
contract_address IN ('0xca4a0be8d75435ae10eb5a98ca91d35740682fd0') -- Relevant contract addresses
AND block_timestamp >= '2024-03-25' -- Same period as revenue calculation
)
-- Calculate ARPU
SELECT
total_revenue / unique_users AS arpu -- Average Revenue Per User
FROM
total_revenue,
unique_users
Run a query to Download Data