[SQL] État des demandes d'accès

La requête suivante sort la liste des demandes d'accès par mois et par territoire, en excluant le mois courant !

SELECT
    creation_month AS "Mois",
    territory AS "Territoire concerné",
    COUNT(DISTINCT t.user_id) AS "Demandes reçues",
    COUNT(DISTINCT (CASE WHEN ua.expires_at IS NOT NULL THEN ua.fk_user END)) AS "Demandes traitées",
    COUNT(DISTINCT (CASE WHEN t.fk_status != 'new' THEN t.user_id END)) AS "Demandes abouties",
    COUNT(ua.user_access_id) AS "Liens envoyés",
    COUNT(CASE WHEN ua.expires_at IS NOT NULL AND ua.expires_at <= NOW() AND ua.used_at IS NULL THEN 1 ELSE NULL END) AS "Liens expirés"
FROM (
  SELECT
    u.*,
    EXTRACT(YEAR FROM u.created_at) || '-' || EXTRACT(MONTH FROM u.created_at) AS "creation_month",
    CASE WHEN lo.location_type = 'nation' THEN 'National' ELSE COALESCE(lo.region_name, lo.departement_name) END AS territory
  FROM users u
  LEFT JOIN localized_organizations lo ON u.fk_organization = lo.organization_id
  WHERE u.created_by IS NULL
) AS t
LEFT JOIN user_accesses ua ON ua.fk_user = t.user_id
WHERE t.created_at < (EXTRACT(YEAR FROM NOW()) || '-' || EXTRACT(MONTH FROM NOW()) || '-01')::DATE
GROUP BY creation_month, territory
ORDER BY creation_month DESC;