Get most recent non null values from a table

most_recent_values.sql

CREATE TABLE table1 (
    id BIGSERIAL,
    main_id int not null,
    created TIMESTAMP DEFAULT NOW(),
    value1 VARCHAR,
    value2 VARCHAR,
    value3 VARCHAR,
);

INSERT INTO table1(main_id, value1) VALUES(1, 'a');
INSERT INTO table1(main_id, value1, value2) VALUES(1, 'b', 'c');
INSERT INTO table1(main_id, value1, value3) VALUES(2, 'd', 'e');
INSERT INTO table1(main_id, value2) VALUES(1, 'f');

CREATE VIEW table1_view AS
    SELECT main_id, value1, value2, value3
    FROM (
        SELECT
            main_id,
            FIRST_VALUE(value1) OVER (
                PARTITION BY main_id
                ORDER BY (created || value1) DESC NULLS LAST
            ) AS value1,
            FIRST_VALUE(value2) OVER (
                PARTITION BY main_id
                ORDER BY (created || value2) DESC NULLS LAST
            ) AS value2,
            FIRST_VALUE(value3) OVER (
                PARTITION BY main_id
                ORDER BY (created || value3) DESC NULLS LAST
            ) AS value3
        FROM table1
    ) AS test1
    GROUP BY main_id, value1, value2, value3;
    
-- TODO: Create table and attach a trigger for better efficiency. Views are just macros.