SCD Type 2

with_window.sql

CREATE TABLE posts_stats_new
(
    effective_from TIMESTAMP,
    effective_to TIMESTAMP,
    post_id INTEGER,
    views INTEGER,
    likes INTEGER,
    shares INTEGER,
    PRIMARY KEY (effective_from, post_id)
);

INSERT INTO posts_stats_new
    SELECT
        dttm as effective_from,
        LEAD(dttm, 1, '9999-12-31 23:59:59') OVER (
            PARTITION BY
              post_id
            ORDER BY
              post_id,
              dttm
        ) as effective_to,
        post_id,
        views,
        likes,
        shares
    FROM (
      SELECT
          dttm,
          (
            LAG(views, 1) OVER (PARTITION BY post_id ORDER BY dttm) = views
            AND
            LAG(likes, 1) OVER (PARTITION BY post_id ORDER BY dttm) = likes
            AND
            LAG(shares, 1) OVER (PARTITION BY post_id ORDER BY dttm) = shares
          ) as redundant,
          post_id,
          views,
          likes,
          shares
      FROM posts_stats
    ) t
    WHERE redundant != true
;
ALTER TABLE posts_stats RENAME TO posts_stats_old;
ALTER TABLE posts_stats_new RENAME TO posts_stats;

without_window.sql

CREATE TABLE posts_stats_new
(
    effective_from TIMESTAMP,
    effective_to TIMESTAMP,
    post_id INTEGER,
    views INTEGER,
    likes INTEGER,
    shares INTEGER,
    PRIMARY KEY (effective_from, post_id)
);

CREATE INDEX ON posts_stats (post_id, views, likes, shares);
INSERT INTO posts_stats_new (post_id, views, likes, shares, effective_from, effective_to)
(SELECT
    t.post_id,
    t.views,
    t.likes,
    t.shares,
    min(t.dttm) as  effective_from,
    t.effective_to
FROM (
    SELECT
        p.dttm,
        (
            SELECT
               (CASE WHEN MIN(p2.dttm) is NULL THEN '9999-12-31 23:59:59' ELSE MIN(p2.dttm) END)
            FROM posts_stats as p2
            WHERE
                p.post_id = p2.post_id
                AND
                p.dttm < p2.dttm
                AND
                (
                    p.views != p2.views
                    OR
                    p.shares != p2.shares
                    OR
                    p.likes != p2.likes
                )
        )  as effective_to,
        p.post_id,
        p.views,
        p.likes,
        p.shares
    FROM
        posts_stats as p
) t
GROUP BY
    t.post_id,
    t.views,
    t.likes,
    t.shares,
    t.effective_to
);


ALTER TABLE posts_stats RENAME TO posts_stats_old;
ALTER TABLE posts_stats_new RENAME TO posts_stats;