Extend CampaignsUsageStatistics with count of specs and diff stats
Created by: mrnugget
We talked about tracking the number of specs in our sync with @ebrodymoore.
And, inspired by Nick's message in Slack about a possible goal for Q4 being "lines of code edited with campaigns", we can/should also track the diff_stat_* fields of the changesets.
Query I have so far (in draft PR: https://github.com/sourcegraph/sourcegraph/pull/15279)
SELECT
(SELECT COUNT(*) FROM campaign_specs) AS campaign_specs_count,
(SELECT COUNT(*) FROM changeset_specs) AS changeset_specs_count,
(SELECT COUNT(*) FROM campaigns) AS campaigns_count,
COUNT(*) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED') AS action_changesets,
COALESCE(SUM(diff_stat_added) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED'), 0) AS action_changesets_diff_stat_added_sum,
COALESCE(SUM(diff_stat_changed) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED'), 0) AS action_changesets_diff_stat_changed_sum,
COALESCE(SUM(diff_stat_deleted) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED'), 0) AS action_changesets_diff_stat_deleted_sum,
COUNT(*) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED' AND external_state = 'MERGED') AS action_changesets_merged,
COALESCE(SUM(diff_stat_added) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED' AND external_state = 'MERGED'), 0) AS action_changesets_merged_diff_stat_added_sum,
COALESCE(SUM(diff_stat_changed) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED' AND external_state = 'MERGED'), 0) AS action_changesets_merged_diff_stat_changed_sum,
COALESCE(SUM(diff_stat_deleted) FILTER (WHERE owned_by_campaign_id IS NOT NULL AND publication_state = 'PUBLISHED' AND external_state = 'MERGED'), 0) AS action_changesets_merged_diff_stat_deleted_sum,
COUNT(*) FILTER (WHERE added_to_campaign) AS manual_changesets,
COUNT(*) FILTER (WHERE added_to_campaign AND external_state = 'MERGED') AS manual_changesets_merged
FROM changesets;
Problem: specs get cleaned up if they're not used, so we can't rely on COUNT