Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

5.2 KB
Raw Blame
import { UnprocessableEntityException } from '@nestjs/common'

import {
  buildBreakdownQuery,
  parseSortParam,
} from '../query/breakdown-query.builder'
import {
  getBreakdownDimension,
  getMetric,
  parseMetricsParam,
} from '../registry'

const SUB_QUERY = `FROM events WHERE pid = {pid:FixedString(12)} AND type = 'pageview'  AND created BETWEEN {groupFrom:String} AND {groupTo:String}`

const normalize = (sql: string) => sql.replace(/\s+/g, ' ').trim()

describe('parseSortParam', () => {
  const dimension = getBreakdownDimension('country', 'traffic')
  const metrics = parseMetricsParam('visitors,pageviews', 'traffic')

  it('defaults to first metric desc', () => {
    expect(parseSortParam(undefined, dimension, metrics)).toEqual({
      field: 'visitors',
      direction: 'desc',
    })
  })

  it('parses field:direction and validates both', () => {
    expect(parseSortParam('pageviews:asc', dimension, metrics)).toEqual({
      field: 'pageviews',
      direction: 'asc',
    })
    expect(parseSortParam('value:asc', dimension, metrics)).toEqual({
      field: 'value',
      direction: 'asc',
    })
    expect(() => parseSortParam('visitors:up', dimension, metrics)).toThrow(
      UnprocessableEntityException,
    )
    expect(() => parseSortParam('users:desc', dimension, metrics)).toThrow(
      UnprocessableEntityException,
    )
  })
})

describe('buildBreakdownQuery', () => {
  it('builds a multi-metric traffic query with pagination and total', () => {
    const query = normalize(
      buildBreakdownQuery({
        dataType: 'traffic',
        dimension: getBreakdownDimension('country', 'traffic'),
        metrics: parseMetricsParam('visitors,pageviews', 'traffic'),
        subQuery: SUB_QUERY,
        ctx: { customEVFilterApplied: false },
        sort: { field: 'visitors', direction: 'desc' },
      }),
    )

    expect(query).toContain('cc AS value')
    expect(query).toContain('count(DISTINCT psid) AS visitors')
    expect(query).toContain('count(*) AS pageviews')
    expect(query).toContain('count() OVER () AS __total')
    expect(query).toContain('GROUP BY value')
    expect(query).toContain('ORDER BY visitors desc')
    expect(query).toContain('LIMIT {v2_limit:UInt32} OFFSET {v2_offset:UInt32}')
  })

  it('degrades visitors to count(*) under custom-event filters (v1 parity)', () => {
    const query = buildBreakdownQuery({
      dataType: 'traffic',
      dimension: getBreakdownDimension('country', 'traffic'),
      metrics: [getMetric('visitors', 'traffic')],
      subQuery: SUB_QUERY,
      ctx: { customEVFilterApplied: true },
      sort: { field: 'visitors', direction: 'desc' },
    })

    expect(query).toContain('count(*) AS visitors')
    expect(query).not.toContain('count(DISTINCT psid)')
  })

  it('includes extra fields for region and groups by them', () => {
    const query = normalize(
      buildBreakdownQuery({
        dataType: 'traffic',
        dimension: getBreakdownDimension('region', 'traffic'),
        metrics: [getMetric('visitors', 'traffic')],
        subQuery: SUB_QUERY,
        ctx: { customEVFilterApplied: false },
        sort: { field: 'visitors', direction: 'desc' },
      }),
    )

    expect(query).toContain('rg AS value')
    expect(query).toContain('cc AS country')
    expect(query).toContain('rgc AS region_code')
    expect(query).toContain('GROUP BY value, country, region_code')
    expect(query).toContain('AND rg IS NOT NULL')
  })

  it('applies the measure to performance metrics', () => {
    const query = buildBreakdownQuery({
      dataType: 'performance',
      dimension: getBreakdownDimension('page', 'performance'),
      metrics: [getMetric('load_time', 'performance')],
      subQuery: SUB_QUERY,
      ctx: { customEVFilterApplied: false, measure: 'p95' },
      sort: { field: 'load_time', direction: 'desc' },
    })

    expect(query).toContain(
      'round(divide(quantileExact(0.95)(pageLoad), 1000), 2) AS load_time',
    )
  })

  it('uses captcha meta-column expressions and per-dimension guards', () => {
    const query = normalize(
      buildBreakdownQuery({
        dataType: 'captcha',
        dimension: getBreakdownDimension('solve_time', 'captcha'),
        metrics: [getMetric('events', 'captcha')],
        subQuery: SUB_QUERY,
        ctx: { customEVFilterApplied: false },
        sort: { field: 'events', direction: 'desc' },
      }),
    )

    expect(query).toContain('multiIf(')
    expect(query).toContain("= 'pass'")

    const countryQuery = normalize(
      buildBreakdownQuery({
        dataType: 'captcha',
        dimension: getBreakdownDimension('country', 'captcha'),
        metrics: [getMetric('events', 'captcha')],
        subQuery: SUB_QUERY,
        ctx: { customEVFilterApplied: false },
        sort: { field: 'events', direction: 'desc' },
      }),
    )

    expect(countryQuery).toContain("= 'pass'")
  })

  it('rejects sessions-join metrics with a 422', () => {
    expect(() =>
      buildBreakdownQuery({
        dataType: 'traffic',
        dimension: getBreakdownDimension('country', 'traffic'),
        metrics: [getMetric('bounce_rate', 'traffic')],
        subQuery: SUB_QUERY,
        ctx: { customEVFilterApplied: false },
        sort: { field: 'bounce_rate', direction: 'desc' },
      }),
    ).toThrow(UnprocessableEntityException)
  })
})