Help optimize SQL query for a custom badge extracting number of words and images, and counting days posted in a topic

Community Building Data & reporting

AI-generated summary

Optimizing SQL Query for Custom Badge

The discussion revolves around optimizing a SQL query for a custom badge that tracks the number of words and images posted in topics within a specific category, and grants the badge based on certain conditions.

meglio initially shares the original query and requests feedback from the community. The query uses multiple Common Table Expressions (CTEs) to calculate the number of words and images, as well as the number of days posted in a topic.

meglio later posts an updated version of the query, which has been optimized to reduce complexity and improve performance. The revised query uses a single CTE and directly calculates the number of words and images from the posts table. The query also filters out deleted posts and applies the conditions for granting the badge.

Key Changes:

  • Reduced number of CTEs from 4 to 1
  • Directly calculated word count and image count from the posts table
  • Applied filters for deleted posts and badge conditions within the CTE
  • Added ORDER BY clause to sort results by creation date in descending order
1

Continuing the discussion from How to force grant all badges from command line:

Dear community, can anyone help with optimizing this SQL query for a custom badge?

It counts number of words and number of images posted in topics in a particular category, and grants a badge based on that.

A fresh look of a few pairs of eyes might be very helpful :slight_smile: Many thanks.

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

Fixed on my own:

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