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