CREATE VIEW activite_mensuelle AS WITH evenements AS ((SELECT auteur_uid AS acteur_uid, date_trunc('month', date_depot) AS mois, 1 AS amendements_deposes, 0 AS amendements_cosignes, CAST((sort = 'Adopté') AS INTEGER) AS amendements_adoptes, 0 AS questions_ecrites, 0 AS votes FROM main.amendements WHERE ((auteur_uid IS NOT NULL) AND (date_depot IS NOT NULL))) UNION ALL (((SELECT unnest(cosignataires_uids), date_trunc('month', date_depot), 0, 1, 0, 0, 0 FROM main.amendements WHERE (date_depot IS NOT NULL)) UNION ALL (SELECT q.auteur_identite_acteur_ref, date_trunc('month', t.date_jo), 0, 0, 0, 1, 0 FROM questions_ecrites.question AS q INNER JOIN (SELECT _racine_id, min(COALESCE(TRY_CAST(info_jo_date_jo AS DATE), CAST(try_strptime(info_jo_date_jo, '%d/%m/%Y') AS DATE))) AS date_jo FROM questions_ecrites.question_texte_question GROUP BY ALL) AS t ON ((t._racine_id = q._id)) WHERE (q.auteur_identite_acteur_ref IS NOT NULL) QUALIFY (row_number() OVER (PARTITION BY q.uid ORDER BY q._legislature DESC) = 1))) UNION ALL (SELECT acteur_uid, date_trunc('month', date_scrutin), 0, 0, 0, 0, 1 FROM main.votes_deputes WHERE ("position" IN ('pour', 'contre', 'abstention')))))SELECT acteur_uid, CAST(mois AS DATE) AS mois, sum(amendements_deposes) AS amendements_deposes, sum(amendements_cosignes) AS amendements_cosignes, sum(amendements_adoptes) AS amendements_adoptes, sum(questions_ecrites) AS questions_ecrites, sum(votes) AS votes FROM evenements GROUP BY ALL;