KPI user-retention calculation runs nested per-cohort queries

[RUKAYAT-CODER]

Overview

KpiService.calculateUserRetention() in src/utils/masking/kpi.service.ts (around lines 131-181) loops over cohorts and, for each, runs additional per-month retention queries with a large WHERE userId IN (:...cohortUserIds) clause. As cohorts grow this produces large IN-lists and repeated round-trips on an EVERY_5_MINUTES cron, which is inefficient and can strain the database.

Specifications

Features:

• Retention metrics are computed with fewer, set-based queries and without unbounded IN-lists.

Tasks:

• Refactor the cohort/retention computation to use grouped/joined aggregate queries (e.g. join cohort users to events grouped by month) instead of nested per-cohort, per-month queries.
• Keep the retention gauge labels/values consistent with the current output.

Impacted Files:

• src/utils/masking/kpi.service.ts

Acceptance Criteria

• Retention is computed without per-cohort nested query loops or unbounded IN-lists.
• Reported retention values are unchanged for the same data.
• All the CI passes
• Star the repo

[shobhamerabacha-star]

Hi there,

I would like to resolve this issue. Here is my proposed execution plan:

• Implementation: Build out the handlers for KPI user-retention calculation runs nested per-cohort queries ensuring proper HTTP status codes and error propagation.
• Testing: Add integration test suites for standard invocations and boundary/error conditions.

Happy to pick this up today—please assign to me!