# Ajude a otimizar a consulta SQL para um emblema personalizado, extraindo o número de palavras e imagens e contando os dias postados em um tópico

**URL:** https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [Janeiro 7, 2020, 1:43pm UTC](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969 "2020-01-07T13:43:43Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![meglio](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/meglio/32/71444_2.png) [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [Janeiro 7, 2020, 1:43pm UTC](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969/1 "2020-01-07T13:43:43Z")

</div>

Continuando a discussão de [Como conceder forçadamente todas as medalhas via linha de comando](https://meta.discourse.org/t/how-to-force-grant-all-badges-from-command-line/137960/19):

* * *

Prezada comunidade, alguém pode ajudar a otimizar esta consulta SQL para uma medalha personalizada?

Ela conta o número de palavras e o número de imagens postadas em tópicos de uma categoria específica e concede uma medalha com base nisso.

Uma nova análise por alguns olhos atentos pode ser muito útil 🙂 Muito obrigado.

```sql
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

```

---

<div class="post-metadata">

### Author: ![meglio](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/meglio/32/71444_2.png) [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [Janeiro 9, 2020, 12:49pm UTC](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969/2 "2020-01-09T12:49:07Z")

</div>

Corrigido por conta própria:

```plaintext
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

```

---

<div class="post-metadata">

### Author: ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)
#### Post date: [Fevereiro 8, 2020, 12:49pm UTC](https://meta.discourse.org/t/help-optimize-sql-query-for-a-custom-badge-extracting-number-of-words-and-images-and-counting-days-posted-in-a-topic/137969/3 "2020-02-08T12:49:10Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.