Method getTopPages

packages/db/src/services/pages.service.ts:36–123

({
    projectId,
    startDate,
    endDate,
    timezone,
    search,
    limit,
  }: IGetPagesInput)

  constructor(private client: typeof ch) {}

  async getTopPages({
    projectId,
    startDate,
    endDate,
    timezone,
    search,
    limit,
  }: IGetPagesInput): Promise<ITopPage[]> {
    // CTE: Get titles from the last 30 days for faster retrieval
    const titlesCte = clix(this.client, timezone)
      .select([
        'concat(origin, path) as page_key',
        "anyLast(properties['__title']) as title",
      ])
      .from(TABLE_NAMES.events, false)
      .where('project_id', '=', projectId)
      .where('name', '=', 'screen_view')
      .where('created_at', '>=', clix.exp('now() - INTERVAL 30 DAY'))
      .groupBy(['origin', 'path']);

    // CTE: compute screen_view durations via window function (leadInFrame gives next event's timestamp)
    const screenViewDurationsCte = clix(this.client, timezone)
      .select([
        'project_id',
        'session_id',
        'path',
        'origin',
        `dateDiff('millisecond', created_at, lead(created_at, 1, created_at) OVER (PARTITION BY session_id ORDER BY created_at)) AS duration`,
      ])
      .from(TABLE_NAMES.events, false)
      .where('project_id', '=', projectId)
      .where('name', '=', 'screen_view')
      .where('path', '!=', '')
      .where('created_at', 'BETWEEN', [
        clix.datetime(startDate, 'toDateTime'),
        clix.datetime(endDate, 'toDateTime'),
      ]);

    // Pre-filtered sessions subquery for better performance
    const sessionsSubquery = clix(this.client, timezone)
      .select(['id', 'project_id', 'is_bounce'])
      .from(TABLE_NAMES.sessions, true) // FINAL
      .where('project_id', '=', projectId)
      .where('created_at', 'BETWEEN', [
        clix.datetime(startDate, 'toDateTime'),
        clix.datetime(endDate, 'toDateTime'),
      ])
      .where('sign', '=', 1);

    // Main query: aggregate events and calculate bounce rate from pre-filtered sessions
    const query = clix(this.client, timezone)
      .with('page_titles', titlesCte)
      .with('screen_view_durations', screenViewDurationsCte)
      .select<ITopPage>([
        'e.origin as origin',
        'e.path as path',
        "coalesce(pt.title, '') as title",
        'uniq(e.session_id) as sessions',