Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

6.5 KB
Raw Blame
import { describe, expect, it, vi } from "vitest";

vi.mock("../../db/clickhouse/clickhouse.js", () => ({
  clickhouse: { query: vi.fn() },
}));
vi.mock("../../db/postgres/postgres.js", () => ({
  db: {},
}));

import { buildMetricQuery } from "./getMetric.js";
import { SESSION_CHANNEL_AGG } from "./utils/sessionAttribution.js";
import { FilterParameter } from "./types.js";

const baseQuery = (overrides: Partial<Record<string, unknown>> = {}) =>
  ({
    start_date: "",
    end_date: "",
    time_zone: "",
    filters: "",
    parameter: "browser" as FilterParameter,
    ...overrides,
  }) as Parameters<typeof buildMetricQuery>[0];

const SITE_ID = 1;

describe("buildMetricQuery", () => {
  describe("default (session-attributed) parameters", () => {
    it("aggregates the parameter per session with argMin", () => {
      const sql = buildMetricQuery(baseQuery(), SITE_ID);
      expect(sql).toContain("argMin(browser, e.timestamp) as value");
      expect(sql).toContain("GROUP BY e.session_id");
      expect(sql).toContain("LIMIT 100");
    });

    it("sums each attributed session's pageviews instead of counting sessions", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "country" }), SITE_ID);
      expect(sql).toContain("sum(pageviews_in_session) as pageviews");
      expect(sql).toContain("sum(sum(pageviews_in_session)) OVER ()");
      expect(sql).not.toMatch(/COUNT\(\) as pageviews\b/);
    });

    it("uses the session-channel aggregation for channel", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "channel" }), SITE_ID);
      expect(sql).toContain(`${SESSION_CHANNEL_AGG} as value`);
      expect(sql).not.toContain("argMin(channel, e.timestamp)");
    });

    it("counts distinct values in the count query without pagination", () => {
      const sql = buildMetricQuery(baseQuery({ limit: 25, page: 3 }), SITE_ID, true);
      expect(sql).toContain("COUNT(DISTINCT value) as totalCount");
      expect(sql).not.toContain("LIMIT");
      expect(sql).not.toContain("OFFSET");
    });
  });

  describe("event_name", () => {
    it("restricts to custom events", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "event_name" }), SITE_ID);
      expect(sql).toContain("event_name as value");
      expect(sql).toContain("AND type = 'custom_event'");
    });

    it("counts distinct event names", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "event_name" }), SITE_ID, true);
      expect(sql).toContain("COUNT(DISTINCT event_name) as totalCount");
    });

    it("keeps event names row-scoped inside a session-qualified campaign", () => {
      const filters = JSON.stringify([
        { parameter: "utm_campaign", type: "equals", value: ["launch"] },
        { parameter: "event_name", type: "equals", value: ["signup"] },
      ]);
      const sql = buildMetricQuery(baseQuery({ parameter: "event_name", filters }), SITE_ID);

      expect(sql).toContain("FilteredSessions AS (");
      expect(sql).toContain("INNER JOIN FilteredSessions USING (session_id)");
      expect(sql).toContain("AND event_name = 'signup'");
      expect(sql.slice(sql.lastIndexOf("FROM events"))).not.toContain("url_parameters['utm_campaign']");
    });
  });

  describe("page_title", () => {
    it("computes bounce rate from single-pageview sessions", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "page_title" }), SITE_ID);
      expect(sql).toContain("SessionPageCounts");
      expect(sql).toContain("pageviews_in_session = 1");
      expect(sql).toContain("as bounce_rate");
    });

    it("wraps the core logic for the count query", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "page_title" }), SITE_ID, true);
      expect(sql).toContain("SELECT COUNT(*) as totalCount FROM (");
    });
  });

  describe("entry_page / exit_page", () => {
    it("orders sessions ascending for entry pages", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "entry_page" }), SITE_ID);
      expect(sql).toContain("e.timestamp_ms AS timestamp_ms");
      expect(sql).toContain("timestamp_ms AS timestamp");
      expect(sql).toContain("ORDER BY timestamp_ms ASC) as row_num");
      expect(sql).toContain("WHERE row_num = 1");
    });

    it("orders sessions descending for exit pages", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "exit_page" }), SITE_ID);
      expect(sql).toContain("ORDER BY timestamp_ms DESC) as row_num");
    });

    it("caps time-on-page at 30 minutes", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "entry_page" }), SITE_ID);
      expect(sql).toContain("time_diff_seconds > 1800, 1800");
    });
  });

  describe("pathname", () => {
    it("aggregates per-path stats with hostname", () => {
      const sql = buildMetricQuery(baseQuery({ parameter: "pathname" }), SITE_ID);
      expect(sql).toContain("anyHeavy(hostname) as top_hostname");
      expect(sql).toContain("GROUP BY pathname");
    });

    it("builds paths from all pageviews in campaign-qualified sessions", () => {
      const filters = JSON.stringify([{ parameter: "utm_campaign", type: "equals", value: ["launch"] }]);
      const sql = buildMetricQuery(baseQuery({ parameter: "pathname", filters }), SITE_ID);

      expect(sql).toContain("FilteredSessions AS (");
      expect(sql).toContain("INNER JOIN FilteredSessions fs ON e.session_id = fs.session_id");
      expect(sql.slice(sql.indexOf("EventTimes AS (")).lastIndexOf("url_parameters['utm_campaign']")).toBe(-1);
    });
  });

  describe("pagination", () => {
    it("applies explicit limit and page offsets", () => {
      const sql = buildMetricQuery(baseQuery({ limit: 7, page: 3 }), SITE_ID);
      expect(sql).toContain("LIMIT 7");
      expect(sql).toContain("OFFSET 14");
    });

    it("omits OFFSET on the first page", () => {
      const sql = buildMetricQuery(baseQuery({ limit: 7, page: 1 }), SITE_ID);
      expect(sql).toContain("LIMIT 7");
      expect(sql).not.toContain("OFFSET");
    });
  });

  describe("time and filter statements", () => {
    it("constrains by the requested date range", () => {
      const sql = buildMetricQuery(
        baseQuery({ start_date: "2024-01-01", end_date: "2024-01-31", time_zone: "UTC" }),
        SITE_ID
      );
      expect(sql).toContain("AND timestamp >=");
      expect(sql).toContain("'2024-01-01'");
    });

    it("threads filters into the WHERE clause", () => {
      const filters = JSON.stringify([{ parameter: "country", type: "equals", value: ["US"] }]);
      const sql = buildMetricQuery(baseQuery({ filters }), SITE_ID);
      expect(sql).toContain("'US'");
    });
  });
});