TheLaughingManDaily users <Emily>
Updated 2025-03-12
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 emferons / Daily users @ https://flipsidecrypto.xyz/emferons/q/u4K5NctD8t2A/daily-users
WITH kaikai_addresses AS (
SELECT address
FROM near.core.dim_address_labels
WHERE project_name = 'kaikai'
OR address LIKE '%.kaiching'
),
-- Get all active days first to create a clean date backbone
active_days AS (
SELECT DISTINCT DATE_TRUNC('day', block_timestamp) AS day
FROM near.core.fact_actions_events
WHERE receiver_id IN (SELECT address FROM kaikai_addresses)
AND block_timestamp >= CURRENT_DATE - INTERVAL '90 days'
),
-- Daily Active Users
daily_active AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day,
COUNT(DISTINCT signer_id) AS daily_active_users
FROM near.core.fact_actions_events
WHERE receiver_id IN (SELECT address FROM kaikai_addresses)
AND block_timestamp >= CURRENT_DATE - INTERVAL '90 days'
AND signer_id NOT IN (SELECT address FROM kaikai_addresses)
GROUP BY 1
),
-- Daily Transactions
daily_txs AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day,
COUNT(*) AS transaction_count
FROM near.core.fact_actions_events
WHERE receiver_id IN (SELECT address FROM kaikai_addresses)
QueryRunArchived: QueryRun has been archived