Context activity

1-- core.launchdarkly.context_activity
2-- How many contexts each environment has seen, and how many were active recently.
3-- LaunchDarkly bills client-side usage on monthly active contexts, so the 30-day
4-- column is the one to watch against your plan.
5--
6-- Grain: one row per project x environment x context kind (current state as of the
7-- last sync).
8-- Depends on: staging.launchdarkly.context, staging.launchdarkly.project_environment,
9--             staging.launchdarkly.project
10--
11-- Recency is measured from CURRENT_DATE() using each context's last_seen. This is a
12-- snapshot: last_seen moves forward on every evaluation, so the table cannot tell
13-- you how many contexts were active in a past month. Schedule a daily copy of this
14-- model if you want that history.
15
16SELECT
17    c.project_id,
18    ANY_VALUE(p.project_key)                                            AS project_key,
19    ANY_VALUE(p.project_name)                                           AS project_name,
20    c.environment_key,
21    ANY_VALUE(e.environment_name)                                       AS environment_name,
22    ANY_VALUE(e.is_critical)                                            AS is_critical_environment,
23    c.context_kind,
24
25    COUNT(*)                                                            AS contexts_total,
26    COUNTIF(DATE(c.last_seen_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))  AS contexts_seen_last_1_day,
27    COUNTIF(DATE(c.last_seen_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))  AS contexts_seen_last_7_days,
28    COUNTIF(DATE(c.last_seen_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AS contexts_seen_last_30_days,
29    COUNTIF(DATE(c.last_seen_at) >= DATE_TRUNC(CURRENT_DATE(), MONTH))         AS contexts_seen_this_month,
30    MAX(c.last_seen_at)                                                 AS latest_seen_at
31FROM {{staging.launchdarkly.context}} c
32LEFT JOIN {{staging.launchdarkly.project_environment}} e
33  ON  e.project_id      = c.project_id
34  AND e.environment_key = c.environment_key
35LEFT JOIN {{staging.launchdarkly.project}} p
36  ON p.project_id = c.project_id
37GROUP BY c.project_id, c.environment_key, c.context_kind