syz-cluster: display the reports count graph

Apart from just the total number of findings (some of which may end up
being non-reported), also display specifically the number of reports
that have found their way to the mailing lists.

2 files changed, 16 insertions, 0 deletions

diff --git a/syz-cluster/pkg/db/stats_repo.go b/syz-cluster/pkg/db/stats_repo.go
index 7604df159..588945b3b 100644
--- a/syz-cluster/pkg/db/stats_repo.go
+++ b/syz-cluster/pkg/db/stats_repo.go
@@ -39,6 +39,20 @@ ORDER BY Date`,
 	})
 }
 
+func (repo *StatsRepository) ReportsPerWeek(ctx context.Context) (
+	[]*CountPerWeek, error) {
+	return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
+		SQL: `SELECT
+  TIMESTAMP_TRUNC(SessionReports.ReportedAt, WEEK) as Date,
+  COUNT(*) as Count
+FROM Findings
+JOIN SessionReports ON SessionReports.SessionID = Findings.SessionID
+WHERE SessionReports.Moderation = FALSE AND SessionReports.ReportedAt IS NOT NULL
+GROUP BY Date
+ORDER BY Date`,
+	})
+}
+
 func (repo *StatsRepository) FindingsPerWeek(ctx context.Context) (
 	[]*CountPerWeek, error) {
 	return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
diff --git a/syz-cluster/pkg/db/stats_repo_test.go b/syz-cluster/pkg/db/stats_repo_test.go
index ce2b05ca9..18f774710 100644
--- a/syz-cluster/pkg/db/stats_repo_test.go
+++ b/syz-cluster/pkg/db/stats_repo_test.go
@@ -25,6 +25,8 @@ func TestStatsSQLs(t *testing.T) {
 		assert.NoError(t, err)
 		_, err = statsRepo.DelayPerWeek(ctx)
 		assert.NoError(t, err)
+		_, err = statsRepo.ReportsPerWeek(ctx)
+		assert.NoError(t, err)
 	}
 
 	dtd := &dummyTestData{t, ctx, client}