Flag health by project

1-- core.launchdarkly.flag_health_by_project
2-- Flag debt per project: how many flags are live, how many of those are temporary,
3-- how many are overdue for cleanup or have nobody responsible for them.
4--
5-- Grain: one row per project (current state as of the last sync).
6-- Depends on: core.launchdarkly.flag_inventory
7--
8-- Counts only, so every column sums across projects. Rates are computed from the
9-- counts in the same row, never averaged across rows.
10
11SELECT
12    project_id,
13    ANY_VALUE(project_key)                                          AS project_key,
14    ANY_VALUE(project_name)                                         AS project_name,
15
16    COUNT(*)                                                        AS flags_total,
17    COUNTIF(NOT is_archived)                                        AS flags_live,
18    COUNTIF(lifecycle_status = 'live_temporary')                    AS flags_live_temporary,
19    COUNTIF(lifecycle_status = 'live_permanent')                    AS flags_live_permanent,
20    COUNTIF(lifecycle_status = 'deprecated')                        AS flags_deprecated,
21    COUNTIF(is_archived)                                            AS flags_archived,
22    COUNTIF(is_cleanup_candidate)                                   AS flags_cleanup_candidates,
23    COUNTIF(NOT is_archived AND has_no_maintainer)                  AS flags_live_without_maintainer,
24    COUNTIF(created_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AS flags_created_last_30_days,
25
26    ROUND(SAFE_DIVIDE(COUNTIF(is_cleanup_candidate),
27                      COUNTIF(lifecycle_status = 'live_temporary')), 4) AS cleanup_candidate_share_of_temporary,
28    -- Median age of live flags. Rising month on month means flags are added faster
29    -- than they are removed.
30    APPROX_QUANTILES(IF(NOT is_archived, age_days, NULL), 2)[OFFSET(1)] AS median_live_flag_age_days
31FROM {{core.launchdarkly.flag_inventory}}
32GROUP BY project_id