Skip to content

Instantly share code, notes, and snippets.

@ichux
Last active May 11, 2021 05:42
Show Gist options
  • Save ichux/7183d427412838779e2284dcf65bb524 to your computer and use it in GitHub Desktop.
Save ichux/7183d427412838779e2284dcf65bb524 to your computer and use it in GitHub Desktop.
summary for a transactions table
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);
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;
@ichux
Copy link
Author

ichux commented Sep 9, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment