Implement query to show daily balance

trsimoes commented 6 years ago

SQL

SELECT
        account_balance,
        credit_balance,
        create_date_time
    FROM (
          SELECT day(create_date_time), MAX(create_date_time) as MaxTime
          FROM SNAPSHOT
          GROUP BY day(create_date_time)
    ) r
    INNER JOIN SNAPSHOT t
    ON t.create_date_time = r.MaxTime;

trsimoes commented 6 years ago

Handy commands:

drop table snapshot;
drop sequence hibernate_sequence;
drop sequence s_snapshot;

INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -139.0, '2018-03-17 01:52', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -133.0, '2018-03-18 01:52', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -130.0, '2018-03-18 01:53', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -130.0, '2018-03-19 01:54', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -129.8, '2018-03-19 01:55', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -129.4, '2018-03-19 01:55', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -129.4, '2018-03-19 01:56', 674.9);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -357.2, '2018-03-20 18:18', 689.2);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -357.2, '2018-03-21 02:52', 689.2);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -377.2, '2018-03-22 19:07', 738.4);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -377.2, '2018-03-23 00:09', 738.4);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -377.2, '2018-03-23 14:20', 743.2);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -377.2, '2018-03-23 18:05', 743.2);
INSERT INTO snapshot (id, account_balance, create_date_time, credit_balance) VALUES (nextval('s_snapshot'), -377.2, '2018-03-23 22:17', 757.2);

trsimoes commented 6 years ago

SELECT
        account_balance,
        credit_balance,
        create_date_time
    FROM (
          SELECT date_part('day', create_date_time), MAX(create_date_time) as MaxTime
          FROM SNAPSHOT
          GROUP BY date_part('day', create_date_time)
    ) r
    INNER JOIN SNAPSHOT t
    ON t.create_date_time = r.MaxTime;

trsimoes commented 6 years ago

CREATE OR REPLACE VIEW
    DAILY_SNAPSHOT_VIEW AS
SELECT
    account_balance,
    credit_balance,
    create_date_time
FROM
    (
        SELECT
            date_part('day', create_date_time),
            MAX(create_date_time) AS MaxTime
        FROM
            SNAPSHOT
        GROUP BY
            date_part('day', create_date_time) ) r
INNER JOIN
    SNAPSHOT t
ON
    t.create_date_time = r.MaxTime;

trsimoes / hbs

Implement query to show daily balance #5