theericstoneuser rankings
Updated 2022-08-11
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 elo as (
select * from bi_analytics.content_rankings.elo_ratings
where project_name = 'Overall'
)
/*popularity as (
WITH mx_date AS (
SELECT MAX(timestamp) AS mx_timestamp
FROM bi_analytics.flipside_app_prod.pages
WHERE timestamp <= CURRENT_DATE + 1
), views AS (
SELECT split(context_page_tab_url,'/')[array_size(split(context_page_tab_url,'/'))-1]::string as dashboard_slug
, CASE WHEN timestamp < DATEADD('days', -15, m.mx_timestamp) then 1 else 0.5 end as recency_wt
, COUNT(1) AS n_views
FROM bi_analytics.flipside_app_prod.pages p
JOIN mx_date m ON p.timestamp >= DATEADD('days', - 90, m.mx_timestamp)
AND p.timestamp <= m.mx_timestamp
WHERE context_page_tab_url LIKE '%dashboard%'
AND NOT context_page_tab_url ILIKE '%datadog%'
AND NOT context_page_tab_url ILIKE '%view-test%'
AND NOT context_page_tab_url ILIKE '%kpi-metrics-dashboard%'
GROUP BY 1,2
order by 1 desc
), b2 AS (
SELECT
dashboard_slug,
SUM(n_views * recency_wt) AS n_views_wt
FROM views
GROUP BY dashboard_slug
), b3 AS (
select
b2.dashboard_slug,
b2.n_views_wt,
d.created_by_id as user_id,
u.username,
d.created_at
from b2
Run a query to Download Data