coder · BrunoQuaresma · Nov 12, 2024 · Nov 12, 2024 · Nov 12, 2024 · Nov 12, 2024
diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/coderd.go b/coderd/coderd.go
@@ -1260,6 +1260,7 @@ func New(options *Options) *API {
 			r.Get("/user-activity", api.insightsUserActivity)
 			r.Get("/user-latency", api.insightsUserLatency)
 			r.Get("/templates", api.insightsTemplates)
+			r.Get("/total-users", api.insightsTotalUsers)
 		})
 		r.Route("/debug", func(r chi.Router) {
 			r.Use(

diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1377,6 +1377,13 @@ func (q *querier) GetAPIKeysLastUsedAfter(ctx context.Context, lastUsed time.Tim
 	return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.GetAPIKeysLastUsedAfter)(ctx, lastUsed)
 }
 
+func (q *querier) GetAccumulatedUsersInsights(ctx context.Context, arg database.GetAccumulatedUsersInsightsParams) ([]database.GetAccumulatedUsersInsightsRow, error) {
+	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
+		return nil, err
+	}
+	return q.db.GetAccumulatedUsersInsights(ctx, arg)
+}
+
 func (q *querier) GetActiveUserCount(ctx context.Context) (int64, error) {
 	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
 		return 0, err

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -2346,6 +2346,13 @@ func (s *MethodTestSuite) TestSystemFunctions() {
 		dbgen.WorkspaceBuild(s.T(), db, database.WorkspaceBuild{})
 		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead)
 	}))
+	s.Run("GetAccumulatedUsersInsights", s.Subtest(func(db database.Store, check *expects) {
+		check.Args(database.GetAccumulatedUsersInsightsParams{
+			StartTime: dbtime.Time(time.Now()),
+			EndTime:   dbtime.Time(time.Now()),
+			Timezone:  "UTC",
+		}).Asserts(rbac.ResourceSystem, policy.ActionRead)
+	}))
 	s.Run("GetActiveUserCount", s.Subtest(func(db database.Store, check *expects) {
 		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
 	}))

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -2320,6 +2320,41 @@ func (q *FakeQuerier) GetAPIKeysLastUsedAfter(_ context.Context, after time.Time
 	return apiKeys, nil
 }
 
+func (q *FakeQuerier) GetAccumulatedUsersInsights(_ context.Context, arg database.GetAccumulatedUsersInsightsParams) ([]database.GetAccumulatedUsersInsightsRow, error) {
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	newUsersByDate := make(map[time.Time]int64)
+	for _, user := range q.users {
+		if user.CreatedAt.Before(arg.StartTime) || user.CreatedAt.After(arg.EndTime) {
+			continue
+		}
+		date := user.CreatedAt.Truncate(24 * time.Hour)
+		newUsersByDate[date]++
+	}
+
+	dateSeries := make([]time.Time, 0)
+	for date := arg.StartTime.Truncate(24 * time.Hour); date.Before(arg.EndTime); date = date.AddDate(0, 0, 1) {
+		dateSeries = append(dateSeries, date)
+	}
+
+	accumulatedByDate := make(map[time.Time]int64)
+	var accumulatedCount int64
+	for _, date := range dateSeries {
+		accumulatedCount += newUsersByDate[date]
+		accumulatedByDate[date] = accumulatedCount
+	}
+
+	var rows []database.GetAccumulatedUsersInsightsRow
+	for _, date := range dateSeries {
+		rows = append(rows, database.GetAccumulatedUsersInsightsRow{
+			Date:  date,
+			Total: accumulatedByDate[date],
+		})
+	}
+	return rows, nil
+}
+
 func (q *FakeQuerier) GetActiveUserCount(_ context.Context) (int64, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -771,3 +771,22 @@ SELECT
 FROM unique_template_params utp
 JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id] AND utp.name = wbp.name)
 GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
+
+-- name: GetAccumulatedUsersInsights :many
+-- GetAccumulatedUsersInsights returns the accumulated number of users created
+-- in the given timeframe. It returns the accumulated number of users for each date
+-- within the specified timeframe, providing a running total of user sign-ups.
+WITH RECURSIVE date_series AS (
+    SELECT @start_time::timestamptz AS date
+    UNION ALL
+    SELECT date + INTERVAL '1 day'
+    FROM date_series
+    WHERE date + INTERVAL '1 day' <= @end_time::timestamptz
+)
+SELECT
+    (d.date AT TIME ZONE @timezone::text)::date AS date,
+    (SELECT COUNT(*) FROM users u WHERE u.created_at <= d.date) AS total
+FROM
+    date_series d
+ORDER BY
+    d.date;