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;

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;