<commit_before>DROP SCHEMA IF EXISTS hr CASCADE;
CREATE SCHEMA hr;

CREATE TABLE hr.depts_journal (
  deptno SERIAL NOT NULL,
  version_number TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  subsequent_version_number TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
  PRIMARY KEY (deptno, version_number)
);

CREATE TABLE hr.emps_journal (
  empid SERIAL NOT NULL,
  deptno INT NOT NULL,
  version_number TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  subsequent_version_number TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
  PRIMARY KEY (empid, version_number)
);

CREATE VIEW hr.depts AS
  WITH link_last AS (
      SELECT *, MAX(version_number) OVER (PARTITION BY deptno) AS last_version_number
      FROM hr.depts_journal
  )
  SELECT deptno
  FROM link_last
  WHERE subsequent_version_number IS NULL
        AND version_number = last_version_number;

CREATE VIEW hr.emps AS
  WITH link_last AS (
      SELECT *, MAX(version_number) OVER (PARTITION BY empid) AS last_version_number
      FROM hr.emps_journal
  )
  SELECT empid, deptno
  FROM link_last
  WHERE subsequent_version_number IS NULL
        AND version_number = last_version_number;

INSERT INTO hr.depts_journal (deptno) VALUES (1),(2),(3),(4);
INSERT INTO hr.emps_journal (empid,deptno) VALUES (1,1),(2,1),(3,2),(4,2),(5,2),(6,4);

-- Employee 1 moves to department 2
INSERT INTO hr.emps_journal (empid,deptno) VALUES (1,2);<commit_msg>Add extra columns. Sample Insert and Update added<commit_after>DROP SCHEMA IF EXISTS hr CASCADE;
CREATE SCHEMA hr;

CREATE TABLE hr.depts_journal (
  deptno SERIAL NOT NULL,
  version_number TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  subsequent_version_number TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
  department_name VARCHAR NOT NULL,
  PRIMARY KEY (deptno, version_number)
);

CREATE TABLE hr.emps_journal (
  empid SERIAL NOT NULL,
  deptno INT NOT NULL,
  version_number TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  subsequent_version_number TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
  first_name VARCHAR NOT NULL,
  last_name VARCHAR NOT NULL,
  PRIMARY KEY (empid, version_number)
);

DROP VIEW IF EXISTS hr.depts;
CREATE VIEW hr.depts AS
  WITH link_last AS (
      SELECT *, MAX(version_number) OVER (PARTITION BY deptno) AS last_version_number
      FROM hr.depts_journal
  )
  SELECT deptno, department_name
  FROM link_last
  WHERE subsequent_version_number IS NULL
        AND version_number = last_version_number;

CREATE VIEW hr.emps AS
  WITH link_last AS (
      SELECT *, MAX(version_number) OVER (PARTITION BY empid) AS last_version_number
      FROM hr.emps_journal
  )
  SELECT empid, deptno
  FROM link_last
  WHERE subsequent_version_number IS NULL
        AND version_number = last_version_number;

INSERT INTO hr.depts_journal (deptno, department_name) VALUES (1, 'Dep1'),(2, 'Dep2'),(3, 'Dep3'),(4, 'Dep4');
INSERT INTO hr.emps_journal (empid,deptno,first_name,last_name) VALUES (1,1,'Peter','Pan'),(2,1,'Ian','Bibian'),(3,2,'Victor','Strugatski'),(4,2,'Stan','Ban'),(5,2,'Dimitar','Gergov'),(6,4,'Ivan','Baraban');

-- Employee 1 moves to department 2
INSERT INTO hr.emps_journal (empid,deptno,first_name,last_name) VALUES (1,2,'Peter','Pan');

-- INSERT
--INSERT INTO hr.depts (deptno, department_name) VALUES(666, 'Pivotal');
INSERT INTO hr.depts_journal (deptno, department_name) VALUES(666, 'Pivotal');

-- UPDATE
-- UPDATE hr.depts SET department_name='First' WHERE deptno = 1;
INSERT INTO hr.depts_journal (deptno, version_number, department_name)
  WITH link_last AS (
    SELECT *, MAX(version_number) OVER (PARTITION BY deptno) AS last_version_number
      FROM hr.depts_journal
      WHERE deptno = 1
  )
  SELECT deptno, version_number + interval '1 hour', 'Name2'
    FROM link_last
    WHERE subsequent_version_number IS NULL
        AND version_number = last_version_number;

SELECT * from hr.depts;
{'fraction_non_alphanumeric': 0.08565584249552544, 'fraction_numerical': 0.01380721043211455, 'mean_word_length': 4.727672035139093, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6518417', 'n_tokens_mistral': 1374, 'n_tokens_neox': 1297, 'n_words': 393}