Audit and History Tables

History Rows

Query an As-Of Status

A history table can store validity ranges so a query can ask what status was active on a chosen date.

Program

Play the script to choose an as-of date and find the matching account statuses.

as_of_day
status_history_asof.sql
Replay: real traced execution (multi-file project)
CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ('2026-02-15')) SELECT as_of_day FROM params;
CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);
SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;

CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ('2026-01-15')) SELECT as_of_day FROM params;
CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);
SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;

CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ('2026-03-15')) SELECT as_of_day FROM params;
CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);
SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;

  1. choice ← 1 row

    1CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ('2026-02-15')) SELECT as_of_day FROM params;2CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
    values this step1 rowchoice

  2. account_status_history ← 4 rows

    2CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);3INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);4SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
    values this step4 rowsaccount_status_history

  3. result ← 2 rows

    3INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);4SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ('2026-01-15')) SELECT as_of_day FROM params;2CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
    values this step1 rowchoice

  2. account_status_history ← 4 rows

    2CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);3INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);4SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
    values this step4 rowsaccount_status_history

  3. result ← 2 rows

    3INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);4SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE history_choice AS WITH params(as_of_day) AS (VALUES ('2026-03-15')) SELECT as_of_day FROM params;2CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);
    values this step1 rowchoice

  2. account_status_history ← 4 rows

    2CREATE TABLE account_status_history (account_id INTEGER, status TEXT, valid_from TEXT, valid_to TEXT);3INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);4SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
    values this step4 rowsaccount_status_history

  3. result ← 2 rows

    3INSERT INTO account_status_history VALUES (1, 'trial', '2026-01-01', '2026-02-01'), (1, 'active', '2026-02-01', NULL), (2, 'active', '2026-01-10', '2026-03-01'), (2, 'paused', '2026-03-01', NULL);4SELECT account_id, status, valid_from, valid_to FROM account_status_history WHERE valid_from <= (SELECT as_of_day FROM history_choice) AND (valid_to IS NULL OR valid_to > (SELECT as_of_day FROM history_choice)) ORDER BY account_id;
    values this step2 rowsresult
validity range `valid_from` and `valid_to` describe when each history row applies.
open-ended row `valid_to IS NULL` means the status is still current.
as-of query The selector changes the date used to read the history table.