insights: over-reported numbers from backend

[emidoots]

Whoopsies, I made a mistake here and the numbers reported by the backend are wrong in the 3.25 release - over-reported by ~2x roughly depending on the situation. They aren't recorded wrong, but are queried wrong.

I originally added the aggregation behavior in https://github.com/sourcegraph/sourcegraph/pull/18506 - and did so on the assumption that we store 1 data point every 12h and thus SUMing all data points over a 12h span would give the accurate count.

@efritz called out this might be wrong, and I created https://github.com/sourcegraph/sourcegraph/issues/18510 which signals an issue with this type of SUM aggregation during periods of frequent service restarts - but I failed to realize there were two other edge cases:

1. If the 12h aggregation window does not align on the dot with the interval of recorded data, then we will sometimes get 2 of the 12h data points SUM'd together.
2. The above can also be affected by whether or not the data point is recorded exactly on the 12h interval, or is delayed a bit: this could happen due to the search taking e.g. 59s to complete - or in the future by nature of how the data back-filler will operate on a different interval schedule than the regular enqueuer.

SUM was a bad choice, likely need MAX.

[github-actions]

Heads up @Joelkw @felixfbecker - the "team/code-insights" label was applied to this issue.

[emidoots]

Looks like these will fix it.

Sum of all points across all repos

SELECT sub.time_bucket,
	SUM(sub.avg),
	sub.metadata
FROM (
	SELECT time_bucket(INTERVAL '12 hours', time) AS time_bucket,
		AVG(value),
		m.metadata,
		repo_id
	FROM series_points p
	LEFT JOIN metadata m ON p.metadata_id = m.id
	GROUP BY time_bucket, metadata, repo_id
	ORDER BY time_bucket DESC
) sub
GROUP BY time_bucket, metadata
ORDER BY time_bucket DESC;

Sum of all points across all repos matching regex.

Same as above, just add a WHERE clause to subquery.

Per-repository:

SELECT * FROM (
	SELECT time_bucket(INTERVAL '12 hours', time) AS time_bucket,
		AVG(value),
		m.metadata,
		repo_id
	FROM series_points p
	LEFT JOIN metadata m ON p.metadata_id = m.id
	GROUP BY time_bucket, metadata, repo_id
	ORDER BY time_bucket DESC
) sub;