Function queryMessageLevelStats

packages/core/src/query/advanced/relationship.ts:210–342

(
  db: DatabaseAdapter,
  segmentIds: number[],
  memberNames: Map<number, string>,
  perseveranceThreshold: number
)

}

function queryMessageLevelStats(
  db: DatabaseAdapter,
  segmentIds: number[],
  memberNames: Map<number, string>,
  perseveranceThreshold: number
): {
  responseLatency: ResponseLatencyMember[]
  perseverance: PerseveranceMember[]
  totalDoubleTexts: number
  monthlyResponseLatency: MonthlyResponseLatency[]
  monthlyPerseverance: MonthlyPerseverance[]
} {
  const empty = {
    responseLatency: [],
    perseverance: [],
    totalDoubleTexts: 0,
    monthlyResponseLatency: [],
    monthlyPerseverance: [],
  }
  if (segmentIds.length === 0) return empty

  const BATCH_SIZE = 500
  const responseTotals = new Map<number, { sum: number; count: number }>()
  const dtTotals = new Map<number, number>()
  const monthlyRespMap = new Map<string, Map<number, { sum: number; count: number }>>()
  const monthlyDtMap = new Map<string, Map<number, number>>()

  for (let i = 0; i < segmentIds.length; i += BATCH_SIZE) {
    const batch = segmentIds.slice(i, i + BATCH_SIZE)
    const placeholders = batch.map(() => '?').join(',')

    const latencyRows = db
      .prepare(
        `WITH msg_lag AS (
           SELECT m.sender_id, m.ts,
                  LAG(m.sender_id) OVER (PARTITION BY mc.segment_id ORDER BY m.ts, m.id) AS prev_sender_id,
                  LAG(m.ts) OVER (PARTITION BY mc.segment_id ORDER BY m.ts, m.id) AS prev_ts
           FROM message_context mc JOIN message m ON m.id = mc.message_id
           WHERE mc.segment_id IN (${placeholders}) AND m.type = 0
         )
         SELECT strftime('%Y-%m', datetime(ts, 'unixepoch', 'localtime')) AS month,
                sender_id AS responder_id, SUM(ts - prev_ts) AS total_time, COUNT(*) AS response_count
         FROM msg_lag WHERE prev_sender_id IS NOT NULL AND sender_id != prev_sender_id
         GROUP BY month, responder_id`
      )
      .all(...batch) as Array<{ month: string; responder_id: number; total_time: number; response_count: number }>

    for (const row of latencyRows) {
      const existing = responseTotals.get(row.responder_id)
      if (existing) {
        existing.sum += row.total_time
        existing.count += row.response_count
      } else responseTotals.set(row.responder_id, { sum: row.total_time, count: row.response_count })

      if (!monthlyRespMap.has(row.month)) monthlyRespMap.set(row.month, new Map())
      const mMap = monthlyRespMap.get(row.month)!
      const mExisting = mMap.get(row.responder_id)
      if (mExisting) {