DATE | POSTS_COUNT | REPLIES_COUNT | LIKES_COUNT | |
|---|---|---|---|---|
1 | 2022-10-05 00:00:00.000 | 0 | 0 | 1 |
2 | 2022-12-14 00:00:00.000 | 0 | 0 | 6 |
3 | 2022-12-15 00:00:00.000 | 0 | 0 | 1 |
4 | 2022-12-18 00:00:00.000 | 0 | 0 | 4 |
5 | 2022-12-19 00:00:00.000 | 0 | 0 | 3 |
6 | 2022-12-20 00:00:00.000 | 5 | 0 | 0 |
7 | 2022-12-21 00:00:00.000 | 52 | 0 | 0 |
8 | 2022-12-22 00:00:00.000 | 21 | 0 | 4 |
9 | 2022-12-23 00:00:00.000 | 10 | 0 | 55 |
10 | 2022-12-24 00:00:00.000 | 26 | 12 | 38 |
11 | 2022-12-25 00:00:00.000 | 17 | 7 | 40 |
12 | 2022-12-26 00:00:00.000 | 15 | 6 | 30 |
13 | 2022-12-27 00:00:00.000 | 7 | 5 | 34 |
14 | 2022-12-28 00:00:00.000 | 9 | 3 | 24 |
15 | 2022-12-29 00:00:00.000 | 6 | 1 | 21 |
16 | 2022-12-30 00:00:00.000 | 3 | 1 | 21 |
17 | 2022-12-31 00:00:00.000 | 4 | 0 | 3 |
18 | 2023-01-01 00:00:00.000 | 14 | 3 | 18 |
19 | 2023-01-02 00:00:00.000 | 10 | 3 | 83 |
20 | 2023-01-03 00:00:00.000 | 8 | 1 | 131 |
messariAtlas - Overtime Social Activty
Updated 2024-01-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
main_posts AS (
SELECT block_timestamp,
tx_hash,
block_id AS og_post_id,
block_id AS current_post_id,
signer_id,
post_text
FROM near.social.fact_posts
),
reply_posts AS (
SELECT block_timestamp,
tx_hash,
try_parse_json(try_parse_json(try_parse_json(node_data):"comment"):"item"):"blockHeight" AS og_post_id,
block_id AS current_post_id,
signer_id,
try_parse_json(try_parse_json(node_data):"comment"):"text" AS post_text
FROM near.social.fact_decoded_actions
WHERE node = 'post'
AND og_post_id IS NOT NULL
),
all_posts AS (
SELECT * FROM main_posts
UNION ALL
SELECT * FROM reply_posts
),
likes AS (
SELECT *,
try_parse_json(try_parse_json(try_parse_json(node_data):"like"):"key"):"blockHeight" AS og_post_id,
split(try_parse_json(try_parse_json(try_parse_json(node_data):"like"):"key"):"path", '/')[0] AS liked
FROM near.social.fact_decoded_actions
WHERE node_data::string LIKE '{"like"%'
),
-- Aggregations
posts_over_time AS (
Last run: about 1 year ago
...
425
16KB
5s