Function getWorkspaceWebsiteStats

src/server/model/website/index.ts:725–839

(
  websiteId: string,
  filters: WebsiteQueryFilters
)

}

export async function getWorkspaceWebsiteStats(
  websiteId: string,
  filters: WebsiteQueryFilters
): Promise<any> {
  const { filterQuery, joinSession, params } = await parseWebsiteFilters(
    websiteId,
    {
      ...filters,
    }
  );

  // Prefer ClickHouse when enabled and healthy; translate filterQuery for CH; require no session join
  if (
    env.clickhouse.enable &&
    clickhouseHealthManager.isClickHouseHealthy() &&
    joinSession === Prisma.empty
  ) {
    try {
      const chFilter = unwrapSQL(filterQuery)
        .replace(/\"WebsiteEvent\"\./g, '')
        .replace(/\"WebsiteSession\"\./g, '')
        .replace(/\"Website\"\./g, '')
        .replace(/\"/g, '')
        .replace(/::[a-zA-Z]+/g, '');

      const chQuery = `
        select
          sum(t.c) as pageviews,
          uniqExact(t.sessionId) as uniques,
          sum(if(t.c = 1, 1, 0)) as bounces,
          sum(t.time) as totaltime
        from (
          select
            sessionId,
            toStartOfHour(createdAt) as bucket,
            count(*) as c,
            dateDiff('second', min(createdAt), max(createdAt)) as time
          from WebsiteEvent
          where websiteId = {websiteId:String}
            and createdAt between toDateTime({start:String}, 'UTC') and toDateTime({end:String}, 'UTC')
            and eventType = {eventType:UInt64}
            ${chFilter}
          group by sessionId, bucket
        ) as t
      `;

      const result = await clickhouse.query({
        query: chQuery,
        query_params: {
          websiteId: params.websiteId,
          start: dayjs(params.startDate).utc().format('YYYY-MM-DD HH:mm:ss'),
          end: dayjs(params.endDate).utc().format('YYYY-MM-DD HH:mm:ss'),
          eventType: EVENT_TYPE.pageView,
        },
      });
      const json = await result.json<any>();
      const rows = (json?.data ?? []) as {
        pageviews: number;