summary for a transactions table

accumulates.sql

CREATE TABLE IF NOT EXISTS accumulates (
    added_on TIMESTAMP WITHOUT TIME ZONE NOT NULL, 
    added_by BIGINT NOT NULL, 
    id BIGSERIAL NOT NULL, 
    enabled BOOLEAN NOT NULL, 
    user_id BIGINT NOT NULL, 
    transaction BIGINT NOT NULL, 
    tablemeta JSONB NOT NULL, 
    CONSTRAINT pk_accumulates PRIMARY KEY (id)
    --, CONSTRAINT fk_accumulates_user_id_users FOREIGN KEY(user_id) REFERENCES users (id) ON UPDATE CASCADE
);

CREATE INDEX ix_accumulates_added_by ON accumulates (added_by);
CREATE INDEX ix_accumulates_tablemeta ON accumulates USING gin (tablemeta);

summary.sql

CREATE
OR REPLACE FUNCTION summary(agentId integer)
  RETURNS table(each_row json) AS
$func$
WITH first_day AS (SELECT date_trunc('MONTH', now()) :: DATE),
     last_day AS (SELECT (
                             date_trunc(
                               'month',
                               (SELECT * FROM first_day)
                                 ) + interval '1 month' - interval '1 day'
                             ) :: DATE),
     month_estimate AS (SELECT n.transactions_date,
                               n.transactions_total,
                               n.transactions_count,
                               SUM(o.transactions_count) As cumulative_transactions_count,
                               SUM(o.transactions_total) As cumulative_transactions_total
                        FROM (SELECT CAST(added_on As date) As transactions_date,
                                     COUNT(transaction)     AS transactions_count,
                                     SUM(transaction)       As transactions_total
                              FROM accumulates
                              WHERE user_id = agentId
                              GROUP BY CAST(added_on As date)
                              ORDER BY CAST(added_on As date)) n
                               INNER JOIN (SELECT CAST(added_on As date) As transactions_date,
                                                  COUNT(transaction)     AS transactions_count,
                                                  SUM(transaction)       As transactions_total
                                           FROM accumulates
                                           WHERE user_id = 1
                                           GROUP BY CAST(added_on As date)
                                           ORDER BY CAST(added_on As date)) o
                                 ON (n.transactions_date >= o.transactions_date)
                        WHERE n.transactions_date >= (SELECT * FROM first_day)
                          AND n.transactions_date <= (SELECT * FROM last_day)
                        GROUP BY n.transactions_total,
                                 n.transactions_count,
                                 n.transactions_date
                        ORDER BY n.transactions_date),
     today_estimate AS (SELECT * FROM month_estimate where transactions_date IN (SELECT now() :: DATE)),
     last_day_estimate AS (SELECT * FROM month_estimate ORDER BY transactions_date DESC LIMIT 1)

SELECT row_to_json(estimated) per_row
FROM (SELECT today_estimate.* FROM today_estimate
      UNION
      SELECT last_day_estimate.* FROM last_day_estimate) estimated
$func$
LANGUAGE sql;