Aide à l'optimisation de la requête SQL pour un badge personnalisé extrayant le nombre de mots et d'images, et comptant les jours publiés dans un sujet

Communauté Données et rapports
1

Suite à la discussion sur Comment forcer l’attribution de tous les badges depuis la ligne de commande :

Chère communauté, quelqu’un peut-il aider à optimiser cette requête SQL pour un badge personnalisé ?

Elle compte le nombre de mots et le nombre d’images publiés dans les sujets d’une catégorie particulière, et attribue un badge en fonction de cela.

Un nouvel examen par quelques paires d’yeux pourrait être très utile :slight_smile: Merci beaucoup.

WITH

personal_pages AS (
  SELECT *
  FROM topics
  WHERE category_id = 17
    AND archived = false
    AND closed = false
    AND visible = true
),

word_counts AS (
  SELECT p.id as post_id,
    p.word_count as num
  FROM posts p
),

image_count AS (
  SELECT p.id as post_id,
    length(raw) * 2
      - length(replace(raw, '<img', '123'))
      - length(replace(raw, '[img]', '1234')) as num
  FROM posts p
),

summary AS (
  SELECT 
    pp.id as topic_id,
    pp.user_id as user_id,
    COUNT (DISTINCT p.created_at::date) as num_days,
    SUM (wc.num) as num_words,
    SUM (ic.num) as num_images
  FROM personal_pages pp
  LEFT JOIN users u on u.id = pp.user_id
  LEFT JOIN posts p ON p.topic_id = pp.id
                   AND p.user_id = pp.user_id
  LEFT JOIN word_counts wc ON wc.post_id = p.id
  LEFT JOIN image_count ic ON ic.post_id = p.id
  WHERE u.staged = false
    AND p.deleted_at IS NULL
  GROUP BY pp.id, pp.user_id
)

SELECT summary.*,
  CURRENT_DATE as granted_at
FROM summary
WHERE num_days >= 10
  AND num_words >= 1000
  AND num_images >= 10
2

Corrigé de mon côté :

WITH summary AS (

SELECT
  t.id AS topic_id,
  t.created_at AS created_at,
  t.user_id AS user_id,
  COUNT (DISTINCT p.created_at::date) AS num_days,
  SUM (p.word_count) AS num_words,
  SUM (
    length(raw) * 2
      - length(replace(raw, '![IMG', '1234'))
      - length(replace(raw, '[img]', '1234'))
  ) AS num_images
FROM topics t
  LEFT JOIN posts p ON p.topic_id = t.id
WHERE
    t.category_id = 17
    AND t.archived = false
    AND t.closed = false
    AND t.visible = true
    AND p.deleted_at IS NULL
    AND p.user_id = t.user_id
GROUP BY t.id

)

SELECT s.*,
  CURRENT_DATE AS granted_at
FROM summary s
WHERE
    num_days >= 10
    AND num_words >= 1000
    AND num_images >= 10
ORDER BY created_at DESC