flipsidecryptoDashboard Views + Impressions Flow
Updated 2023-08-16
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
›
⌄
--Since 5/22/23 (when we started collected twitter data)
with db_views AS (
SELECT
RIGHT(SPLIT(context_page_tab_url, '?')[0]::string, 6) AS slug_id,
count(*) AS views
FROM bi_analytics.core.fact_pages p
where timestamp::DATE >= '2023-05-22'
AND LEN(path) > 1
AND path != '/discover'
AND STARTSWITH(path, '/data') = FALSE
AND STARTSWITH(path, '/?sortBy=') = FALSE
AND STARTSWITH(path, '/previews') = FALSE
AND STARTSWITH(path, 'analysts') = FALSE
AND CHARINDEX('/q/', path) = 0
AND STARTSWITH(name, '/edit') = FALSE
AND STARTSWITH(context_page_url, 'http://localhost') = FALSE
AND LEN(path) - LEN(REPLACE(path,'/','')) > 1
group by 1
),
slugs AS (
select
id AS dashboard_id,
slug_id
from bi_analytics.velocity_app_prod.dashboards
QUALIFY( ROW_NUMBER() OVER (PARTITION BY dashboard_id ORDER BY updated_at DESC) = 1 )
),
impressions AS (
select
RIGHT(SPLIT(clean_url, '?')[0]::string, 6) AS slug_id,
sum(impression_count) AS n_impressions
from BI_ANALYTICS.TWITTER.TWEET
where platform = 'Flipside'
and slug_id <> ''
Run a query to Download Data