-- a request for the events of 200 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 (200 literals)`, two periods, `jsonb_to_recordset` and a lateral lookup, `count(DISTINCT)` by period and kind
-- ordered: yes
-- fresh: 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,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200)), 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