Period-over-Period Comparison

import {
  type PeriodOverPeriodComparison,
  validPeriodOverPeriodGranularities,
  periodOverPeriodGranularityLabels,
  isSupportedPeriodOverPeriodGranularity,
  POP_PREVIOUS_PERIOD_SUFFIX,
  getPopFieldId,
  getBaseFieldIdFromPop,
} from "@lightdash/common";

/**
 * Configuration for period-over-period comparison
 */
interface PeriodOverPeriodComparison {
  /** Comparison type (currently only 'previousPeriod' supported) */
  type: 'previousPeriod';

  /** Time granularity for comparison (DAY, WEEK, MONTH, QUARTER, YEAR) */
  granularity: TimeFrames;

  /** Number of periods to offset (e.g., 3 for "compare to 3 months ago") */
  periodOffset?: number;

  /** Time dimension to use for comparison */
  field: {
    name: string;
    table: string;
  };
}

import { type PeriodOverPeriodComparison, TimeFrames } from "@lightdash/common";

// Compare this month to last month
const monthOverMonth: PeriodOverPeriodComparison = {
  type: 'previousPeriod',
  granularity: TimeFrames.MONTH,
  field: {
    name: 'created_at',
    table: 'orders'
  }
};

// Compare this quarter to the same quarter last year (4 quarters ago)
const quarterYearOverYear: PeriodOverPeriodComparison = {
  type: 'previousPeriod',
  granularity: TimeFrames.QUARTER,
  periodOffset: 4,
  field: {
    name: 'order_date',
    table: 'sales'
  }
};

// Compare this week to last week
const weekOverWeek: PeriodOverPeriodComparison = {
  type: 'previousPeriod',
  granularity: TimeFrames.WEEK,
  periodOffset: 1,
  field: {
    name: 'created_date',
    table: 'events'
  }
};

/**
 * Array of valid granularities for period-over-period comparison
 */
const validPeriodOverPeriodGranularities: TimeFrames[];
// Contains: [TimeFrames.DAY, TimeFrames.WEEK, TimeFrames.MONTH, TimeFrames.QUARTER, TimeFrames.YEAR]

/**
 * Human-readable labels for each granularity
 */
const periodOverPeriodGranularityLabels: Record<TimeFrames, string>;
// Example values: { [TimeFrames.MONTH]: 'Month', [TimeFrames.QUARTER]: 'Quarter', ... }

/**
 * Check if a granularity is supported for period-over-period comparison
 * @param granularity - Time frame to check
 * @returns true if granularity is valid for PoP comparison
 */
function isSupportedPeriodOverPeriodGranularity(granularity: TimeFrames): boolean;

import {
  validPeriodOverPeriodGranularities,
  periodOverPeriodGranularityLabels,
  isSupportedPeriodOverPeriodGranularity,
  TimeFrames
} from "@lightdash/common";

// Get all valid granularities
console.log(validPeriodOverPeriodGranularities);
// Output: [DAY, WEEK, MONTH, QUARTER, YEAR]

// Get label for a granularity
const label = periodOverPeriodGranularityLabels[TimeFrames.MONTH];
console.log(label); // Output: "Month"

// Validate granularity
const isValid = isSupportedPeriodOverPeriodGranularity(TimeFrames.HOUR);
console.log(isValid); // Output: false (HOUR not supported for PoP)

const isValidMonth = isSupportedPeriodOverPeriodGranularity(TimeFrames.MONTH);
console.log(isValidMonth); // Output: true

/**
 * Naming suffix for period-over-period comparison columns
 * This is the single source of truth for PoP column naming convention
 */
const POP_PREVIOUS_PERIOD_SUFFIX = '_previous';

/**
 * Generate the PoP field ID from a base metric field ID
 * @param baseFieldId - The field ID of the base metric (e.g., "orders_total_revenue")
 * @returns The PoP field ID (e.g., "orders_total_revenue_previous")
 */
function getPopFieldId(baseFieldId: string): string;

/**
 * Extract the base field ID from a PoP field ID
 * @param fieldId - The field ID to check
 * @returns The base field ID if this is a PoP field, null otherwise
 */
function getBaseFieldIdFromPop(fieldId: string): string | null;

import {
  POP_PREVIOUS_PERIOD_SUFFIX,
  getPopFieldId,
  getBaseFieldIdFromPop
} from "@lightdash/common";

// Create PoP field ID
const baseMetric = "orders_total_revenue";
const popFieldId = getPopFieldId(baseMetric);
console.log(popFieldId); // Output: "orders_total_revenue_previous"

// Extract base field from PoP field
const popField = "sales_count_previous";
const baseField = getBaseFieldIdFromPop(popField);
console.log(baseField); // Output: "sales_count"

// Check if a field is NOT a PoP field
const regularField = "customer_name";
const notPopField = getBaseFieldIdFromPop(regularField);
console.log(notPopField); // Output: null

// Use the suffix constant
console.log(POP_PREVIOUS_PERIOD_SUFFIX); // Output: "_previous"

import {
  type MetricQuery,
  type PeriodOverPeriodComparison,
  TimeFrames,
  FilterOperator,
  UnitOfTime
} from "@lightdash/common";

// Example query with period-over-period comparison
const query: MetricQuery = {
  exploreName: "sales",
  dimensions: ["sales_date"],
  metrics: ["sales_revenue"],
  filters: {
    dimensions: {
      id: "root",
      and: [{
        target: { fieldId: "sales_date" },
        operator: FilterOperator.IN_THE_PAST,
        values: [3],
        settings: { unitOfTime: UnitOfTime.months }
      }]
    }
  },
  sorts: [{ fieldId: "sales_date", descending: false }],
  limit: 1000,
  tableCalculations: []
};

// The PoP configuration would be applied by the backend
// to generate comparison columns like "sales_revenue_previous"

const monthOverMonth: PeriodOverPeriodComparison = {
  type: 'previousPeriod',
  granularity: TimeFrames.MONTH,
  periodOffset: 1,
  field: {
    name: 'order_date',
    table: 'orders'
  }
};

const yearOverYear: PeriodOverPeriodComparison = {
  type: 'previousPeriod',
  granularity: TimeFrames.YEAR,
  periodOffset: 1,
  field: {
    name: 'transaction_date',
    table: 'transactions'
  }
};

// Compare to 4 weeks ago
const fourWeekComparison: PeriodOverPeriodComparison = {
  type: 'previousPeriod',
  granularity: TimeFrames.WEEK,
  periodOffset: 4,
  field: {
    name: 'event_timestamp',
    table: 'events'
  }
};

import { TimeFrames } from "@lightdash/common";

// TimeFrames enum values for PoP
enum TimeFrames {
  DAY = 'DAY',
  WEEK = 'WEEK',
  MONTH = 'MONTH',
  QUARTER = 'QUARTER',
  YEAR = 'YEAR'
}