-- a request for the events of 20 parents (a CTE of the keys, two periods, a lateral over the parts of a document and a lateral lookup, two levels of aggregates): the periods lie after the last event, so the time is the planning and the answer is empty
-- label: CTE of `id IN (20 literals)`, two periods, `jsonb_to_recordset` and a lateral lookup, `count(DISTINCT)` by period and kind
-- ordered: yes
--@ pg
WITH keys AS MATERIALIZED (SELECT DISTINCT id FROM parents WHERE id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)), periods AS (SELECT * FROM (VALUES ('current', daterange('2030-01-01', '2030-10-31', '[]')), ('previous', daterange('2029-01-01', '2029-10-31', '[]'))) AS p(period, range)), base AS (SELECT ev.id, ev.parent_id, ev.doc, periods.period FROM events ev INNER JOIN periods ON ev.ts <@ periods.range INNER JOIN keys ON keys.id = ev.parent_id WHERE ev.deleted_at IS NULL), per_event AS (SELECT b.id, b.parent_id, b.period, o.kind, o.sub, avg(o.score) AS score, count(b.id) FILTER (WHERE o.dir_a = 'pos') AS positive, count(b.id) AS n FROM base b INNER JOIN LATERAL (SELECT CASE WHEN b.doc -> 'metrics' ? 'm_a' THEN x.kind ELSE dm.tier END AS kind, x.sub, x.dir_a, x.score FROM jsonb_to_recordset(b.doc -> 'parts') AS x(kind text, sub text, dir_a text, score float8) LEFT JOIN LATERAL (SELECT d.tier FROM dims d WHERE d.id = b.parent_id LIMIT 1) dm ON true WHERE x.score IS NOT NULL) o ON true GROUP BY b.id, b.parent_id, b.period, o.kind, o.sub) SELECT period, kind, count(DISTINCT id) AS events, sum(n) AS n, sum(positive) AS positive, round(avg(score)::numeric, 3) AS score FROM per_event GROUP BY period, kind ORDER BY period, kind