Skip to content

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

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

const query = vi.fn();
vi.mock("../../db/clickhouse/clickhouse.js", () => ({
  clickhouse: { query: (...args: unknown[]) => query(...args) },
}));

import {
  buildRetentionQuery,
  buildRetentionQueryParams,
  getRetention,
  MAX_COHORTS,
  processRetentionData,
  resolveRetentionWindow,
  RETENTION_LOOKBACK_DAYS,
} from "./getRetention.js";

// Wednesday Sep 30, 2026, 11:00 in New York.
const NOW = Date.parse("2026-09-30T15:00:00Z");
const NY = "America/New_York";

describe("resolveRetentionWindow", () => {
  it("lays Monday-start weeks over a date range and ends a range that includes today at now", () => {
    const window = resolveRetentionWindow(
      { start_date: "2026-08-03", end_date: "2026-09-30", time_zone: NY },
      "week",
      NOW
    );

    expect(window.periods).toEqual([
      "2026-08-03",
      "2026-08-10",
      "2026-08-17",
      "2026-08-24",
      "2026-08-31",
      "2026-09-07",
      "2026-09-14",
      "2026-09-21",
      "2026-09-28",
    ]);
    expect(window.start.toUTC().toISO()).toBe("2026-08-03T04:00:00.000Z");
    expect(window.end.toMillis()).toBe(NOW);
    expect(window.firstPeriodPartial).toBe(false);
    // The week of Sep 28 has only reached Wednesday: in progress, not a drop.
    expect(window.lastPeriodPartial).toBe(true);
    expect(window.endsAtNow).toBe(true);
    expect(window.truncated).toBe(false);
  });

  it("marks cohorts cut by a range that starts and ends mid-week", () => {
    const window = resolveRetentionWindow(
      { start_date: "2026-08-05", end_date: "2026-08-20", time_zone: NY },
      "week",
      NOW
    );

    expect(window.periods).toEqual(["2026-08-03", "2026-08-10", "2026-08-17"]);
    expect(window.firstPeriodPartial).toBe(true);
    expect(window.lastPeriodPartial).toBe(true);
    // Cut by the range, not still going on.
    expect(window.endsAtNow).toBe(false);
    expect(window.end.toUTC().toISO()).toBe("2026-08-21T04:00:00.000Z");
  });

  it("treats a past range that ends on a period boundary as complete", () => {
    const window = resolveRetentionWindow(
      { start_date: "2026-08-03", end_date: "2026-08-16", time_zone: "UTC" },
      "week",
      NOW
    );

    expect(window.periods).toEqual(["2026-08-03", "2026-08-10"]);
    expect(window.lastPeriodPartial).toBe(false);
  });

  it("keeps the latest periods of a window longer than the cohort cap", () => {
    const window = resolveRetentionWindow(
      { start_date: "2026-01-01", end_date: "2026-09-30", time_zone: "UTC" },
      "day",
      NOW
    );

    expect(window.periods).toHaveLength(MAX_COHORTS.day);
    expect(window.periods[window.periods.length - 1]).toBe("2026-09-30");
    expect(window.truncated).toBe(true);
    expect(window.firstPeriodPartial).toBe(false);
  });

  it("reads the dashboard's empty date range as all time, capped", () => {
    const window = resolveRetentionWindow({ start_date: "", end_date: "", time_zone: "UTC" }, "week", NOW);

    expect(window.periods).toHaveLength(MAX_COHORTS.week);
    expect(window.periods[window.periods.length - 1]).toBe("2026-09-28");
    expect(window.truncated).toBe(true);
  });

  it("answers the legacy range param, clamped to 7 to 365 days", () => {
    expect(resolveRetentionWindow({ range: "60" }, "day", NOW).start.toISODate()).toBe("2026-08-01");
    expect(resolveRetentionWindow({ range: "2" }, "day", NOW).start.toISODate()).toBe("2026-09-23");
  });

  it("defaults to the last 90 days when the request names no window", () => {
    const window = resolveRetentionWindow({}, "week", NOW);
    expect(window.start.toISODate()).toBe("2026-07-02");
    expect(window.end.toMillis()).toBe(NOW);
  });

  it("uses datetime and past-minutes windows as given", () => {
    const datetime = resolveRetentionWindow(
      { start_datetime: "2026-09-01 00:00:00", end_datetime: "2026-09-08 00:00:00", time_zone: "UTC" },
      "day",
      NOW
    );
    expect(datetime.periods).toHaveLength(7);

    const pastMinutes = resolveRetentionWindow({ past_minutes_start: "1440", past_minutes_end: "0" }, "day", NOW);
    expect(pastMinutes.periods).toEqual(["2026-09-29", "2026-09-30"]);
  });

  it("returns no periods for a window entirely in the future", () => {
    const window = resolveRetentionWindow(
      { start_date: "2026-10-05", end_date: "2026-10-10", time_zone: "UTC" },
      "day",
      NOW
    );
    expect(window.periods).toEqual([]);
  });
});

describe("buildRetentionQuery", () => {
  const window = resolveRetentionWindow(
    { start_date: "2026-08-03", end_date: "2026-09-30", time_zone: NY },
    "week",
    NOW
  );

  it("scans the window plus a bounded lookback, and drops users seen during it", () => {
    const sql = buildRetentionQuery(window, undefined, 1);

    expect(sql).toContain("AND timestamp >= toDateTime('2026-05-05 04:00:00', 'UTC')");
    expect(sql).toContain("AND timestamp < toDateTime('2026-09-30 15:00:00', 'UTC')");
    expect(sql).toContain("HAVING earlier_events = 0");
    expect(sql).toContain("COALESCE(NULLIF(identified_user_id, ''), user_id) AS effective_user_id");
    expect(sql).toContain("toStartOfWeek(timestamp, 1, {timeZone:String})");
    expect(sql).toContain("intDiv(dateDiff('day', cohort_period, activity_period), 7) AS period_difference");
    expect(sql).toContain("LIMIT {rowLimit:UInt32}");
    expect(sql).not.toContain("FilteredSessions");
    expect(RETENTION_LOOKBACK_DAYS).toBe(90);
  });

  it("selects cohorts by their first session when filters are set", () => {
    const filters = JSON.stringify([{ parameter: "country", type: "equals", value: ["US"] }]);
    const sql = buildRetentionQuery(window, filters, 1);

    expect(sql).toContain("FilteredSessions AS");
    expect(sql).toContain("WHERE first_session_id IN (SELECT session_id FROM FilteredSessions)");
    // The filter CTE covers the window only, not the lookback.
    expect(sql).toContain("AND timestamp >= toDateTime('2026-08-03 04:00:00', 'UTC')");
    expect(sql.match(/country = 'US'/g)).toHaveLength(1);
  });

  it("counts days in the window's timezone", () => {
    const daily = resolveRetentionWindow(
      { start_date: "2026-09-01", end_date: "2026-09-30", time_zone: NY },
      "day",
      NOW
    );
    const sql = buildRetentionQuery(daily, undefined, 1);

    expect(sql).toContain("toDate(timestamp, {timeZone:String})");
    expect(sql).toContain("dateDiff('day', cohort_period, activity_period) AS period_difference");
  });

  it("binds the timezone, window start and a triangle-sized row limit as parameters", () => {
    expect(buildRetentionQueryParams(window, 7)).toEqual({
      siteId: 7,
      timeZone: NY,
      windowStart: "2026-08-03 04:00:00",
      rowLimit: (MAX_COHORTS.week * (MAX_COHORTS.week + 1)) / 2,
    });
  });
});

describe("processRetentionData", () => {
  const periods = ["2026-08-03", "2026-08-10", "2026-08-17"];

  it("keeps retained user counts and fills unobserved returns with 0 inside the triangle", () => {
    const cohorts = processRetentionData(
      [
        { cohort_period: "2026-08-03", period_difference: 0, retained_users: 4 },
        { cohort_period: "2026-08-03", period_difference: 2, retained_users: 1 },
        { cohort_period: "2026-08-10", period_difference: 0, retained_users: 3 },
        { cohort_period: "2026-08-10", period_difference: 1, retained_users: 1 },
      ],
      periods
    );

    expect(cohorts["2026-08-03"]).toEqual({ size: 4, counts: [4, 0, 1], percentages: [100, 0, 25] });
    expect(cohorts["2026-08-10"]).toEqual({ size: 3, counts: [3, 1, null], percentages: [100, 33.33, null] });
    expect(cohorts["2026-08-17"]).toBeUndefined();
    expect(Object.keys(cohorts)).toEqual(["2026-08-03", "2026-08-10"]);
  });

  it("ignores rows outside the window's periods", () => {
    const cohorts = processRetentionData(
      [
        { cohort_period: "2026-07-27", period_difference: 0, retained_users: 9 },
        { cohort_period: "2026-08-17", period_difference: 0, retained_users: 2 },
        { cohort_period: "2026-08-17", period_difference: 1, retained_users: 2 },
      ],
      periods
    );

    expect(cohorts).toEqual({ "2026-08-17": { size: 2, counts: [2, null, null], percentages: [100, null, null] } });
  });
});

describe("getRetention", () => {
  const reply = () => {
    const res = { statusCode: 200, body: undefined as unknown };
    return Object.assign(res, {
      status(code: number) {
        res.statusCode = code;
        return this;
      },
      send(body: unknown) {
        res.body = body;
        return this;
      },
    });
  };
  const request = (querystring: Record<string, unknown>) =>
    ({ params: { siteId: "1" }, query: querystring, log: { error: vi.fn(), debug: vi.fn() } }) as any;

  it("rejects an unknown mode", async () => {
    const res = reply();
    await getRetention(request({ mode: "month" }), res as any);
    expect(res.statusCode).toBe(400);
    expect(query).not.toHaveBeenCalled();
  });

  it("returns the window's shape without querying when it holds no periods", async () => {
    const res = reply();
    await getRetention(
      request({ mode: "day", start_date: "2999-01-01", end_date: "2999-01-02", time_zone: "UTC" }),
      res as any
    );

    expect(res.statusCode).toBe(200);
    expect(query).not.toHaveBeenCalled();
    expect(res.body).toMatchObject({ data: { cohorts: {}, periods: [], maxPeriods: 0, mode: "day" } });
  });

  it("queries with bound parameters and returns counts alongside percentages", async () => {
    query.mockResolvedValueOnce({
      json: async () => [
        { cohort_period: "2026-09-28", period_difference: "0", retained_users: "5" },
        { cohort_period: "2026-09-28", period_difference: "1", retained_users: "2" },
      ],
    });
    const res = reply();
    await getRetention(
      request({ mode: "week", start_date: "2026-09-28", end_date: "2026-10-11", time_zone: "UTC" }),
      res as any
    );

    expect(query).toHaveBeenCalledTimes(1);
    expect(query.mock.calls[0][0].query_params).toMatchObject({ siteId: 1, timeZone: "UTC" });
    const body = res.body as { data: { cohorts: Record<string, unknown>; lookbackDays: number } };
    expect(body.data.lookbackDays).toBe(RETENTION_LOOKBACK_DAYS);
    expect(Object.values(body.data.cohorts)[0]).toMatchObject({ size: 5 });
  });
});