By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798737 fiddles created (41857 in the last week).
CREATE TABLE classroom_observations (id SERIAL, created_at TIMESTAMPTZ);
CREATE TABLE training_modules (id SERIAL, created_at TIMESTAMPTZ);
CREATE TABLE teachers_workshops (id SERIAL, created_at TIMESTAMPTZ);
INSERT INTO classroom_observations VALUES
(1, '2019-04-20 10:36:06+02')
,(2, '2019-05-22 15:22:33+02')
,(3, '2019-05-23 15:22:33+02')
,(4, '2019-05-24 15:22:33+02');
INSERT INTO training_modules VALUES
(1, '2019-03-20 10:36:06+02')
,(2, '2019-04-22 15:22:33+02')
,(3, '2019-04-23 15:22:33+02')
,(4, '2019-05-24 15:22:33+02');
INSERT INTO teachers_workshops VALUES
(1, '2019-03-20 10:36:06+02');
✓
✓
✓
4 rows affected
4 rows affected
1 rows affected
✓
✓
✓
4 rows affected
4 rows affected
1 rows affected
hidden batch(es)
SELECT to_char(mon, 'YYYY-MM') AS month
, COALESCE(co.ct, 0) AS co_count
, COALESCE(tm.ct, 0) AS tm_count
, COALESCE(tw.ct, 0) AS tw_count
FROM (
SELECT date_trunc('month', created_at) AS mon, count(*) AS ct
FROM classroom_observations
GROUP BY mon
) co
FULL JOIN (
SELECT date_trunc('month', created_at) AS mon, count(*) AS ct
FROM training_modules
GROUP BY mon
) tm USING (mon)
FULL JOIN (
SELECT date_trunc('month', created_at) AS mon, count(*) AS ct
FROM teachers_workshops
GROUP BY mon
) tw USING (mon)
ORDER BY mon;
