-- a statement of 21 common table expressions and 16 lateral sub-selects over `events`, the first statement of a new backend: the choice between each shared CTE and its inline plan is read from the table every backend shares
-- label: a report of 21 CTEs (two scalar readers each) and 16 lateral sub-selects, the first statement of a new backend
-- ordered: yes
-- fresh: yes
--@ pg
WITH c1 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '0 month' AND e.ts < date '2024-01-01' + interval '0 month' + interval '3 day' AND e.parent_id BETWEEN 1 AND 40 GROUP BY 1, 2),
c2 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '1 month' AND e.ts < date '2024-01-01' + interval '1 month' + interval '6 day' AND e.parent_id BETWEEN 38 AND 77 AND e.kind = 'cat_b' GROUP BY 1, 2),
c3 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '2 month' AND e.ts < date '2024-01-01' + interval '2 month' + interval '9 day' AND e.parent_id BETWEEN 75 AND 114 AND e.kind = 'cat_c' GROUP BY 1, 2),
c4 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '3 month' AND e.ts < date '2024-01-01' + interval '3 month' + interval '3 day' AND e.parent_id BETWEEN 112 AND 151 GROUP BY 1, 2),
c5 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '4 month' AND e.ts < date '2024-01-01' + interval '4 month' + interval '6 day' AND e.parent_id BETWEEN 149 AND 188 AND e.kind = 'cat_a' GROUP BY 1, 2),
c6 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '5 month' AND e.ts < date '2024-01-01' + interval '5 month' + interval '9 day' AND e.parent_id BETWEEN 186 AND 225 AND e.kind = 'cat_b' GROUP BY 1, 2),
c7 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '6 month' AND e.ts < date '2024-01-01' + interval '6 month' + interval '3 day' AND e.parent_id BETWEEN 223 AND 262 GROUP BY 1, 2),
c8 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '7 month' AND e.ts < date '2024-01-01' + interval '7 month' + interval '6 day' AND e.parent_id BETWEEN 260 AND 299 AND e.kind = 'cat_d' GROUP BY 1, 2),
c9 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '8 month' AND e.ts < date '2024-01-01' + interval '8 month' + interval '9 day' AND e.parent_id BETWEEN 297 AND 336 AND e.kind = 'cat_a' GROUP BY 1, 2),
c10 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '9 month' AND e.ts < date '2024-01-01' + interval '9 month' + interval '3 day' AND e.parent_id BETWEEN 334 AND 373 GROUP BY 1, 2),
c11 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '10 month' AND e.ts < date '2024-01-01' + interval '10 month' + interval '6 day' AND e.parent_id BETWEEN 371 AND 410 AND e.kind = 'cat_c' GROUP BY 1, 2),
c12 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '11 month' AND e.ts < date '2024-01-01' + interval '11 month' + interval '9 day' AND e.parent_id BETWEEN 408 AND 447 AND e.kind = 'cat_d' GROUP BY 1, 2),
c13 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '12 month' AND e.ts < date '2024-01-01' + interval '12 month' + interval '3 day' AND e.parent_id BETWEEN 445 AND 484 GROUP BY 1, 2),
c14 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '13 month' AND e.ts < date '2024-01-01' + interval '13 month' + interval '6 day' AND e.parent_id BETWEEN 482 AND 521 AND e.kind = 'cat_b' GROUP BY 1, 2),
c15 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '14 month' AND e.ts < date '2024-01-01' + interval '14 month' + interval '9 day' AND e.parent_id BETWEEN 519 AND 558 AND e.kind = 'cat_c' GROUP BY 1, 2),
c16 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '15 month' AND e.ts < date '2024-01-01' + interval '15 month' + interval '3 day' AND e.parent_id BETWEEN 556 AND 595 GROUP BY 1, 2),
c17 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '16 month' AND e.ts < date '2024-01-01' + interval '16 month' + interval '6 day' AND e.parent_id BETWEEN 593 AND 632 AND e.kind = 'cat_a' GROUP BY 1, 2),
c18 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '17 month' AND e.ts < date '2024-01-01' + interval '17 month' + interval '9 day' AND e.parent_id BETWEEN 630 AND 669 AND e.kind = 'cat_b' GROUP BY 1, 2),
c19 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '18 month' AND e.ts < date '2024-01-01' + interval '18 month' + interval '3 day' AND e.parent_id BETWEEN 667 AND 706 GROUP BY 1, 2),
c20 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '19 month' AND e.ts < date '2024-01-01' + interval '19 month' + interval '6 day' AND e.parent_id BETWEEN 704 AND 743 AND e.kind = 'cat_d' GROUP BY 1, 2),
c21 AS (SELECT e.parent_id, e.kind, count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, max(e.amount) AS top, count(DISTINCT e.skew_id) AS skews FROM events e WHERE e.deleted_at IS NULL AND e.ts >= date '2024-01-01' + interval '20 month' AND e.ts < date '2024-01-01' + interval '20 month' + interval '9 day' AND e.parent_id BETWEEN 741 AND 780 AND e.kind = 'cat_a' GROUP BY 1, 2)
SELECT b.bucket, (SELECT sum(a) FROM c1) AS t1, (SELECT count(*) FROM c1 WHERE n > 10) AS b1, (SELECT max(top) FROM c1 WHERE kind = 'cat_a') AS m1,
  (SELECT sum(a) FROM c2) AS t2, (SELECT count(*) FROM c2 WHERE n > 11) AS b2, (SELECT max(top) FROM c2 WHERE kind = 'cat_b') AS m2,
  (SELECT sum(a) FROM c3) AS t3, (SELECT count(*) FROM c3 WHERE n > 12) AS b3, (SELECT max(top) FROM c3 WHERE kind = 'cat_c') AS m3,
  (SELECT sum(a) FROM c4) AS t4, (SELECT count(*) FROM c4 WHERE n > 13) AS b4, (SELECT max(top) FROM c4 WHERE kind = 'cat_d') AS m4,
  (SELECT sum(a) FROM c5) AS t5, (SELECT count(*) FROM c5 WHERE n > 14) AS b5, (SELECT max(top) FROM c5 WHERE kind = 'cat_a') AS m5,
  (SELECT sum(a) FROM c6) AS t6, (SELECT count(*) FROM c6 WHERE n > 15) AS b6, (SELECT max(top) FROM c6 WHERE kind = 'cat_b') AS m6,
  (SELECT sum(a) FROM c7) AS t7, (SELECT count(*) FROM c7 WHERE n > 16) AS b7, (SELECT max(top) FROM c7 WHERE kind = 'cat_c') AS m7,
  (SELECT sum(a) FROM c8) AS t8, (SELECT count(*) FROM c8 WHERE n > 17) AS b8, (SELECT max(top) FROM c8 WHERE kind = 'cat_d') AS m8,
  (SELECT sum(a) FROM c9) AS t9, (SELECT count(*) FROM c9 WHERE n > 18) AS b9, (SELECT max(top) FROM c9 WHERE kind = 'cat_a') AS m9,
  (SELECT sum(a) FROM c10) AS t10, (SELECT count(*) FROM c10 WHERE n > 19) AS b10, (SELECT max(top) FROM c10 WHERE kind = 'cat_b') AS m10,
  (SELECT sum(a) FROM c11) AS t11, (SELECT count(*) FROM c11 WHERE n > 20) AS b11, (SELECT max(top) FROM c11 WHERE kind = 'cat_c') AS m11,
  (SELECT sum(a) FROM c12) AS t12, (SELECT count(*) FROM c12 WHERE n > 21) AS b12, (SELECT max(top) FROM c12 WHERE kind = 'cat_d') AS m12,
  (SELECT sum(a) FROM c13) AS t13, (SELECT count(*) FROM c13 WHERE n > 22) AS b13, (SELECT max(top) FROM c13 WHERE kind = 'cat_a') AS m13,
  (SELECT sum(a) FROM c14) AS t14, (SELECT count(*) FROM c14 WHERE n > 23) AS b14, (SELECT max(top) FROM c14 WHERE kind = 'cat_b') AS m14,
  (SELECT sum(a) FROM c15) AS t15, (SELECT count(*) FROM c15 WHERE n > 24) AS b15, (SELECT max(top) FROM c15 WHERE kind = 'cat_c') AS m15,
  (SELECT sum(a) FROM c16) AS t16, (SELECT count(*) FROM c16 WHERE n > 25) AS b16, (SELECT max(top) FROM c16 WHERE kind = 'cat_d') AS m16,
  (SELECT sum(a) FROM c17) AS t17, (SELECT count(*) FROM c17 WHERE n > 26) AS b17, (SELECT max(top) FROM c17 WHERE kind = 'cat_a') AS m17,
  (SELECT sum(a) FROM c18) AS t18, (SELECT count(*) FROM c18 WHERE n > 27) AS b18, (SELECT max(top) FROM c18 WHERE kind = 'cat_b') AS m18,
  (SELECT sum(a) FROM c19) AS t19, (SELECT count(*) FROM c19 WHERE n > 28) AS b19, (SELECT max(top) FROM c19 WHERE kind = 'cat_c') AS m19,
  (SELECT sum(a) FROM c20) AS t20, (SELECT count(*) FROM c20 WHERE n > 29) AS b20, (SELECT max(top) FROM c20 WHERE kind = 'cat_d') AS m20,
  (SELECT sum(a) FROM c21) AS t21, (SELECT count(*) FROM c21 WHERE n > 30) AS b21, (SELECT max(top) FROM c21 WHERE kind = 'cat_a') AS m21,
  l1.n AS ln1, l1.a AS la1, l1.parents AS lp1, l2.n AS ln2, l2.a AS la2, l2.parents AS lp2, l3.n AS ln3, l3.a AS la3, l3.parents AS lp3, l4.n AS ln4, l4.a AS la4, l4.parents AS lp4, l5.n AS ln5, l5.a AS la5, l5.parents AS lp5, l6.n AS ln6, l6.a AS la6, l6.parents AS lp6, l7.n AS ln7, l7.a AS la7, l7.parents AS lp7, l8.n AS ln8, l8.a AS la8, l8.parents AS lp8, l9.n AS ln9, l9.a AS la9, l9.parents AS lp9, l10.n AS ln10, l10.a AS la10, l10.parents AS lp10, l11.n AS ln11, l11.a AS la11, l11.parents AS lp11, l12.n AS ln12, l12.a AS la12, l12.parents AS lp12, l13.n AS ln13, l13.a AS la13, l13.parents AS lp13, l14.n AS ln14, l14.a AS la14, l14.parents AS lp14, l15.n AS ln15, l15.a AS la15, l15.parents AS lp15, l16.n AS ln16, l16.a AS la16, l16.parents AS lp16
FROM buckets b
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '1 day' AND e.kind = 'cat_a' AND e.qty > 0 AND e.parent_id <= 30) l1 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '2 day' AND e.kind = 'cat_b' AND e.qty > 1 AND e.parent_id <= 31) l2 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '3 day' AND e.kind = 'cat_c' AND e.qty > 2 AND e.parent_id <= 32) l3 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '1 day' AND e.kind = 'cat_d' AND e.qty > 3 AND e.parent_id <= 33) l4 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '2 day' AND e.kind = 'cat_a' AND e.qty > 4 AND e.parent_id <= 34) l5 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '3 day' AND e.kind = 'cat_b' AND e.qty > 0 AND e.parent_id <= 35) l6 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '1 day' AND e.kind = 'cat_c' AND e.qty > 1 AND e.parent_id <= 36) l7 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '2 day' AND e.kind = 'cat_d' AND e.qty > 2 AND e.parent_id <= 37) l8 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '3 day' AND e.kind = 'cat_a' AND e.qty > 3 AND e.parent_id <= 38) l9 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '1 day' AND e.kind = 'cat_b' AND e.qty > 4 AND e.parent_id <= 39) l10 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '2 day' AND e.kind = 'cat_c' AND e.qty > 0 AND e.parent_id <= 40) l11 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '3 day' AND e.kind = 'cat_d' AND e.qty > 1 AND e.parent_id <= 41) l12 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '1 day' AND e.kind = 'cat_a' AND e.qty > 2 AND e.parent_id <= 42) l13 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '2 day' AND e.kind = 'cat_b' AND e.qty > 3 AND e.parent_id <= 43) l14 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '3 day' AND e.kind = 'cat_c' AND e.qty > 4 AND e.parent_id <= 44) l15 ON true
LEFT JOIN LATERAL (SELECT count(*) AS n, sum(e.amount) AS a, avg(e.qty) AS q, count(DISTINCT e.parent_id) AS parents FROM events e WHERE e.deleted_at IS NULL AND e.ts >= b.bucket AND e.ts < b.bucket + interval '1 day' AND e.kind = 'cat_d' AND e.qty > 0 AND e.parent_id <= 45) l16 ON true
WHERE b.bucket < date '2024-04-01'
ORDER BY b.bucket