forecast_api/route_sql/list_changes.sql

WITH
agg as (
SELECT
    ol.doc->>'user' "user"
    ,quota_rep_descr 
    ,to_char((ol.doc->>'stamp')::timestamptz,'Mon-DD HH24:MI:SS') stamp
    ,ol.doc->>'tag' tag
    ,ol.doc->>'message' "comment"
    ,jsonb_pretty(ol.doc) doc
    ,ol.id
    ,to_char(round(sum(value_usd),2),'$999,999,999D00') sales
from
    rlarp.osm_log ol
    inner join rlarp.osm_pool on
        id = logid
WHERE
    quota_rep_descr = 'replace_user'
    AND tag NOT IN ('Initial Build','last price')
group BY
    ol.doc->>'user'
    ,quota_rep_descr 
    ,ol.doc->>'stamp'
    ,ol.doc->>'tag'
    ,ol.doc->>'comment'
    ,jsonb_pretty(ol.doc)
    ,ol.id
ORDER BY
    ol.id desc
)
SELECT
    jsonb_agg(row_to_json(agg)::jsonb ORDER BY agg.id desc) x from agg
update history log 2020-02-14 02:24:52 -05:00			`WITH`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`agg as (`
format 2019-03-23 17:17:46 -04:00			`SELECT`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`ol.doc->>'user' "user"`
			`,quota_rep_descr`
24 hour format on the stamp 2020-02-25 16:49:46 -05:00			`,to_char((ol.doc->>'stamp')::timestamptz,'Mon-DD HH24:MI:SS') stamp`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`,ol.doc->>'tag' tag`
			`,ol.doc->>'message' "comment"`
pretty print doc 2020-02-25 00:27:30 -05:00			`,jsonb_pretty(ol.doc) doc`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`,ol.id`
update history log 2020-02-14 02:24:52 -05:00			`,to_char(round(sum(value_usd),2),'$999,999,999D00') sales`
			`from`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`rlarp.osm_log ol`
update history log 2020-02-14 02:24:52 -05:00			`inner join rlarp.osm_pool on`
			`id = logid`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`WHERE`
			`quota_rep_descr = 'replace_user'`
prevent last price iteration from being available to delete, and rebuild price iteration to only look at baseline activity 2021-04-12 15:10:45 -04:00			`AND tag NOT IN ('Initial Build','last price')`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`group BY`
			`ol.doc->>'user'`
			`,quota_rep_descr`
			`,ol.doc->>'stamp'`
			`,ol.doc->>'tag'`
			`,ol.doc->>'comment'`
pretty print doc 2020-02-25 00:27:30 -05:00			`,jsonb_pretty(ol.doc)`
validate forecast matches actual, build in log entries for initial build 2020-02-20 14:26:39 -05:00			`,ol.id`
change time stamp and add sort 2020-02-25 10:54:45 -05:00			`ORDER BY`
sort result sets by id 2021-06-02 11:50:21 -04:00			`ol.id desc`
format 2019-03-23 17:17:46 -04:00			`)`
			`SELECT`
sort result sets by id 2021-06-02 11:50:21 -04:00			`jsonb_agg(row_to_json(agg)::jsonb ORDER BY agg.id desc) x from agg`