SQL utile (stats, etc.)

Statistiques - Metabase

Ajout de l'utilisateur Metabase

CREATE USER metabase WITH PASSWORD 'xxx';
GRANT CONNECT ON DATABASE aplus TO metabase;
\c aplus
GRANT USAGE ON SCHEMA public TO metabase;
GRANT SELECT ON event TO metabase;
GRANT SELECT ON "user" TO metabase;
GRANT SELECT ON user_group TO metabase;
GRANT SELECT ON answer_metadata TO metabase;
GRANT SELECT ON application_metadata TO metabase;
GRANT SELECT ON login_token_metadata TO metabase;
GRANT SELECT ON mandat_metadata TO metabase;
GRANT SELECT ON area TO metabase;
GRANT SELECT ON organisation TO metabase;
GRANT SELECT ON user_group_is_invited_on_application TO metabase;
GRANT SELECT ON user_is_invited_on_application TO metabase;
GRANT SELECT ON user_is_in_user_group TO metabase;
GRANT SELECT ON user_group_is_in_area TO metabase;
GRANT SELECT ON signup_request TO metabase;

Liste d'utilisateurs désactivés

SELECT name, 'https://aplus.beta.gouv.fr/groups/' || id AS url
FROM (
  SELECT "user_group".id as id, "user_group".name, "user".disabled FROM "user_group", "user" WHERE "user_group".id = ANY("user".group_ids)
) as groups
GROUP BY id, name
HAVING EVERY(disabled)
ORDER BY name;

Groupes dupliqués

WITH tsgroups AS (
SELECT
l.id AS l_id,
l.name AS l_name,
tsvector_to_array(to_tsvector('french', l.name)) AS l_words,
r.id AS r_id,
r.name AS r_name,
tsvector_to_array(to_tsvector('french', r.name)) AS r_words
FROM user_group l, user_group r
WHERE l.organisation = r.organisation
AND l.area_ids && r.area_ids
AND l.id != r.id
)
SELECT
l_name,
'https://aplus.beta.gouv.fr/groups/' || l_id AS l_url,
r_name,
'https://aplus.beta.gouv.fr/groups/' || r_id AS r_url
FROM tsgroups
WHERE (l_words <@ r_words);

Temps entre la génération d'un token et l'authentification

SELECT email, gen_date AS date_generation_token, auth_date AS date_next_authentication, delay_minutes, gen_uid AS user_id FROM (
SELECT DISTINCT ON (gen_id)
  gen_id, gen_uid, gen_date, auth_date, (EXTRACT(EPOCH FROM auth_date) - EXTRACT(EPOCH FROM gen_date))/60 AS delay_minutes
FROM (
  SELECT id AS gen_id, creation_date AS gen_date, from_user_id AS gen_uid
  FROM event
  WHERE code='GENERATE_TOKEN'
  ORDER BY gen_date DESC
) AS gen
LEFT JOIN (
  SELECT creation_date AS auth_date, from_user_id AS auth_uid
  FROM event
  WHERE code='AUTH_BY_KEY'
  ORDER BY auth_date DESC
) AS auth
ON gen_uid = auth_uid
WHERE gen_date < auth_date
) AS sub
LEFT JOIN "user" ON "user".id = sub.gen_uid
ORDER BY gen_date DESC;

Nombre de réponses par demande

WITH answer_counts AS (
  SELECT application_id, COUNT(application_id) AS num_answers
  FROM answer_metadata
  GROUP BY application_id
)
SELECT
  creator_user_name,
  num_answers,
  creation_date,
  closed_date,
  'https://aplus.beta.gouv.fr/toutes-les-demandes/' || application_id AS url,
  'https://aplus.beta.gouv.fr/utilisateurs/' || creator_user_id AS user_url
FROM application_metadata, answer_counts
WHERE application_metadata.id = answer_counts.application_id
ORDER BY num_answers DESC;

Nombre d'administrations receptrices

WITH app_users AS (
  SELECT id, creation_date, closed_date, creator_user_id, a.creator_user_name, u.key::uuid invited_user_id
  FROM application_metadata a, jsonb_each_text(a.invited_users) u
)
SELECT
  invited_users.creator_user_name,
  invited_users.creation_date,
  invited_users.closed_date,
  array_length(array_agg(DISTINCT user_group.organisation), 1) num_orgs,
  array_agg(DISTINCT user_group.organisation) orgs,
  'https://aplus.beta.gouv.fr/toutes-les-demandes/' || app_id url, 
  'https://aplus.beta.gouv.fr/utilisateurs/' || invited_users.creator_user_id user_url
FROM (
  SELECT 
    a.id AS app_id, 
    a.creation_date,
    a.closed_date,
    a.creator_user_id,
    a.creator_user_name, 
    u.group_ids AS group_ids
  FROM app_users a, "user" u
  WHERE a.invited_user_id = u.id
) invited_users, unnest(invited_users.group_ids)
JOIN user_group ON unnest = user_group.id
WHERE user_group.organisation IS NOT NULL
GROUP BY
  app_id,
  invited_users.creation_date,
  invited_users.closed_date,
  invited_users.creator_user_id,
  invited_users.creator_user_name
ORDER BY num_orgs DESC;

Demandes qui n'ont que des participants désactivés

WITH app_users AS (
  SELECT id, creation_date, closed_date, creator_user_id, a.creator_user_name, u.key::uuid invited_user_id
  FROM application_metadata a, jsonb_each_text(a.invited_users) u
)
SELECT
  creator_user_name,
  creation_date,
  closed_date,
  'https://aplus.beta.gouv.fr/toutes-les-demandes/' || id url
FROM application_metadata
WHERE id NOT IN (
  SELECT a.id
  FROM app_users a, "user" u
  WHERE a.invited_user_id = u.id
  AND u.disabled = false
)
ORDER BY creation_date DESC;