Skip to content

A platform for community discussion. Free, open, simple.

70.4 KB
Raw Blame
# frozen_string_literal: true

module DiscourseDataExplorer
  class Queries
    def self.default
      definitions.transform_values { |attributes| attributes.except("tags") }
    end

    # Tags shipped with default queries, in addition to the virtual "default" tag
    def self.tags
      @tags ||= definitions.to_h { |id, attributes| [id.to_i, attributes["tags"] || []] }.freeze
    end

    def self.definitions
      # WARNING: Edit the query hash carefully
      # For each query, add id, name, description and optional tags here and add sql below
      # Feel free to add new queries at the bottom of the hash in numerical order
      # The seed task synchronizes these definitions without changing local query state.

      queries = {
        "most-common-likers": {
          id: -1,
          name: "Most Common Likers",
          description: "Which users like particular other users the most?",
          tags: %w[users],
        },
        "most-messages": {
          id: -2,
          name: "Who has been sending the most messages in the last week?",
          description: "tracking down suspicious PM activity",
          tags: %w[engagement],
        },
        "edited-post-spam": {
          id: -3,
          name: "Last 500 posts that were edited by TL0/TL1 users",
          description: "fighting human-driven copy-paste spam",
          tags: %w[users],
        },
        "new-topics": {
          id: -4,
          name: "New Topics by Category",
          description:
            "Lists all new topics ordered by category and creation_date. The query accepts a ‘months_ago’ parameter. It defaults to 0 to give you the stats for the current month.",
          tags: %w[content],
        },
        "active-topics": {
          id: -5,
          name: "Top 100 Active Topics",
          description:
            "based on the number of replies, it accepts a ‘months_ago’ parameter, defaults to 1 to give results for the last calendar month.",
          tags: %w[content],
        },
        "top-likers": {
          id: -6,
          name: "Top 100 Likers",
          description:
            "returns the top 100 likers for a given monthly period ordered by like_count. It accepts a ‘months_ago’ parameter, defaults to 1 to give results for the last calendar month.",
          tags: %w[users],
        },
        "quality-users": {
          id: -7,
          name: "Top 50 Quality Users",
          description:
            "based on post score calculated using reply count, likes, incoming links, bookmarks, time spent and read count.",
          tags: %w[users],
        },
        "user-participation": {
          id: -8,
          name: "User Participation Statistics",
          description: "Detailed statistics for the most active users.",
          tags: %w[engagement],
        },
        "largest-uploads": {
          id: -9,
          name: "Top 50 Largest Uploads",
          description: "sorted by file size.",
          tags: %w[admin],
        },
        "inactive-users": {
          id: -10,
          name: "Inactive Users with no posts",
          description: "analyze pre-Discourse signups.",
          tags: %w[users],
        },
        "active-lurkers": {
          id: -11,
          name: "Most Active Lurkers",
          description:
            "active users without posts and excessive read times, it accepts a post_read_count parameter that sets the threshold for posts read.",
          tags: %w[engagement],
        },
        "topic-user-notification-level": {
          id: -12,
          name: "List of topics a user is watching/tracking/muted",
          description:
            "The query requires a ‘notification_level’ parameter. Use 0 for muted, 1 for regular, 2 for tracked and 3 for watched topics.",
          tags: %w[users],
        },
        "assigned-topics-report": {
          id: -13,
          name: "List of assigned topics by user",
          description: "This report requires the assign plugin, it will find all assigned topics",
          tags: %w[assign],
        },
        "group-members-reply-count": {
          id: -14,
          name: "Group Members Reply Count",
          description:
            "Number of replies by members of a group over a given time period. Requires 'group_name', 'start_date', and 'end_date' parameters. Dates need to be in the form 'yyyy-mm-dd'. Accepts an 'include_pms' parameter.",
          tags: %w[assign],
        },
        "total-assigned-topics-report": {
          id: -15,
          name: "Total topics assigned per user",
          description: "Count of assigned topis per user linking to assign list",
          tags: %w[assign],
        },
        "poll-results": {
          id: -16,
          name: "Poll results report",
          description:
            "Details of a poll result, including details about each vote and voter, useful for analyzing results in external software.",
          tags: %w[polls],
        },
        "top-tags-per-year": {
          id: -17,
          name: "Top tags per year",
          description: "List the top tags per year.",
          tags: %w[content],
        },
        number_of_replies_by_category: {
          id: -18,
          name: "Number of replies by category",
          description: "List the number of replies by category.",
          tags: %w[content],
        },
        "poll-results-ranked-choice": {
          id: -19,
          name: "Poll results report (for Ranked Choice polls)",
          description:
            "Details of a Ranked Choice poll result, including details about each vote and voter inc. rank, useful for analyzing results in external software.",
          tags: %w[polls],
        },
        "weekly-unique-visitors": {
          id: -20,
          name: "Weekly Unique Visitors",
          description:
            "Number of distinct users who visited the site each week. Accepts a 'weeks_ago' parameter, defaults to the last 12 weeks.",
          tags: %w[traffic],
        },
        "top-topics-by-views": {
          id: -21,
          name: "Top 100 Topics by Views",
          description:
            "The most viewed topics in a recent period, split into anonymous and logged-in views. Accepts a 'days_ago' parameter, defaults to the last 7 days.",
          tags: %w[content],
        },
        "top-search-terms": {
          id: -22,
          name: "Top 200 Search Terms",
          description:
            "The most popular search terms by number of distinct users searching for them, useful for spotting content gaps. Accepts a 'days_ago' parameter, defaults to the last 30 days.",
          tags: %w[content],
        },
        "topic-views-and-clicks": {
          id: -23,
          name: "Topic Views and Link Clicks Over Time",
          description:
            "Daily views (anonymous and logged in) and outbound link clicks for a single topic, useful for measuring how an announcement performed. Requires a 'topic_id' parameter, accepts a 'days_ago' parameter.",
          tags: %w[content],
        },
        "avg-first-response-time": {
          id: -24,
          name: "Average Time to First Response",
          description:
            "Average number of hours before a new topic receives its first reply from someone other than the topic author, grouped by week. Accepts a 'weeks_ago' parameter, defaults to the last 12 weeks.",
          tags: %w[engagement],
        },
        "new-topic-response-rate": {
          id: -25,
          name: "New Topic Response Rate",
          description:
            "Percentage of new public topics that receive any reply, and a staff reply, within 30 days of creation, grouped by month. Only counts topics old enough to have had a fair chance at a reply. Accepts a 'months_ago' parameter.",
          tags: %w[engagement],
        },
        "community-participation-trend": {
          id: -26,
          name: "Community Participation Trend",
          description:
            "Monthly count of distinct non-staff users replying in public topics, with average replies per replier, useful to distinguish 'fewer people participating' from 'the same people posting less'. Accepts a 'months_ago' parameter.",
          tags: %w[engagement],
        },
        "trust-level-growth": {
          id: -27,
          name: "Trust Level Growth Summary",
          description:
            "Number of users who reached each trust level in a recent period, alongside the current total population at each level. Accepts a 'days_ago' parameter, defaults to the last 28 days.",
          tags: %w[users],
        },
        "topics-with-no-response": {
          id: -28,
          name: "Topics With No Response",
          description:
            "Number of topics per period that never received a reply from anyone other than the topic author. Accepts 'days_ago', 'category_id', 'include_subcategories' and 'interval' (day, week, month or year) parameters.",
          tags: %w[content],
        },
        "top-posters": {
          id: -29,
          name: "Top Posters in a Given Timeframe",
          description:
            "Ranks users by topics created and replies posted in a date range. Requires 'start_date' and 'end_date' parameters (yyyy-mm-dd). Accepts a 'top_x' parameter, defaults to 10.",
          tags: %w[engagement],
        },
        "category-activity": {
          id: -30,
          name: "Category Activity Breakdown",
          description:
            "Topic count, post count, likes and reads per category for a date range. Requires 'start_date' and 'end_date' parameters (yyyy-mm-dd).",
          tags: %w[content],
        },
        "tl3-promotion-candidates": {
          id: -31,
          name: "Trust Level 3 Promotion Progress",
          description:
            "Checks every trust level 3 promotion requirement for current trust level 2 users, mirroring the logic of the built-in promotion job, including promotion blockers (locked trust level, active or recent suspensions and silences). Each threshold is a parameter defaulting to the matching site setting's default. Set 'show_all_results' to false to only list users currently meeting every requirement.",
          tags: %w[users],
        },
        "silenced-users": {
          id: -32,
          name: "Silenced Users Report",
          description:
            "Currently silenced users, when they were silenced and by whom ('system' for automatic silences). Accepts optional 'start_date', 'end_date' and 'silenced_by' parameters.",
          tags: %w[moderation],
        },
        "user-warnings": {
          id: -33,
          name: "Recent Official Warnings",
          description:
            "Official warnings issued recently, showing who was warned, who issued the warning, and the related topic. Accepts a 'days_ago' parameter, defaults to the last 28 days.",
          tags: %w[moderation],
        },
        "most-flagged-users": {
          id: -34,
          name: "Users With Most Agreed-Upon Flags",
          description:
            "Ranks users by the number of their posts that had a flag agreed with by staff, useful for finding repeat offenders.",
          tags: %w[moderation],
        },
        "subcategory-permission-drift": {
          id: -35,
          name: "Subcategory Permission Audit",
          description:
            "Finds subcategories granting a group permission that the parent category does not grant — a permission-hygiene check not surfaced by the admin UI.",
          tags: %w[admin],
        },
        "reading-participation-histogram": {
          id: -36,
          name: "Reading Participation Histogram",
          description:
            "Buckets users by how many posts they read in a given period, from a single post up to 2048+, showing the shape of your reading base (lurkers vs power readers). Accepts 'from_days_ago' and 'duration_days' parameters.",
          tags: %w[engagement],
        },
        "flags-by-type": {
          id: -37,
          name: "Flags by Type",
          description:
            "Number of flags per flag type, split into flags reported by real users and flags raised by automated accounts (system, bots). Accepts a 'days_ago' parameter, defaults to the last 90 days.",
          tags: %w[moderation],
        },
        "flags-handled-by-staff": {
          id: -38,
          name: "Flags Handled by Staff Member",
          description:
            "Number of review queue items handled per staff member, useful for recognizing moderation workload. Accepts a 'days_ago' parameter, defaults to the last 90 days.",
          tags: %w[moderation],
        },
        "suspended-users": {
          id: -39,
          name: "Suspended Users Report",
          description:
            "Currently suspended users, when they were suspended, until when, by whom and why. Accepts optional 'start_date', 'end_date' and 'suspended_by' parameters.",
          tags: %w[moderation],
        },
        "foreign-language-topics": {
          id: -40,
          name: "Topics Not in the Site's Primary Language",
          description:
            "Recently created topics whose detected language differs from the site's primary language, useful for routing content for translation or moderation. WARNING: requires content localization with locale detection to be enabled (e.g. the content_localization_enabled and ai_translation_enabled site settings); without it topics have no locale recorded and this report will be empty. Accepts 'primary_locale' and 'days_ago' parameters.",
          tags: %w[translation],
        },
        "foreign-language-posts": {
          id: -41,
          name: "Posts Not in the Site's Primary Language",
          description:
            "Recent posts whose detected language differs from the site's primary language, with a short excerpt. WARNING: requires content localization with locale detection to be enabled (e.g. the content_localization_enabled and ai_translation_enabled site settings); without it posts have no locale recorded and this report will be empty. Accepts 'primary_locale' and 'days_ago' parameters.",
          tags: %w[translation],
        },
        "crawler-traffic-overview": {
          id: -42,
          name: "Crawler and Bot Traffic Overview",
          description:
            "Buckets recent beacon pageviews by bot-likelihood score from Discourse's built-in crawler detection. Scoring lags live traffic, so the newest pageviews sit in the 'not scored' bucket alongside those that carried no bot signals at all, rather than counting as users. WARNING: requires scoring (hidden site setting experimental_detect_crawler_pageviews); without it, every pageview lands in 'not scored'. Accepts an 'hours' parameter, defaults to the last 24 hours.",
          tags: %w[traffic],
        },
        "crawler-traffic-detailed": {
          id: -43,
          name: "Crawler and Bot Traffic Detailed Report",
          description:
            "Row-per-IP breakdown of likely bot pageview activity, with the individual signals that drove the score (automated user agent, known crawler network, velocity, session churn, rapid navigation, bad referrer, no measured interaction). WARNING: requires scoring (hidden site setting experimental_detect_crawler_pageviews); without it this report will be empty. Accepts 'hours' and 'min_score' parameters.",
          tags: %w[traffic],
        },
        "suspected-bot-networks": {
          id: -44,
          name: "Suspected Automated Traffic by IP and Network",
          description:
            "Networks (ASNs) and IPs generating high pageview volume with bot-like session patterns (near 1.0 views per session, rotating user agents, systematic topic harvesting), sorted so a scrape spread across many IPs on one network floats to the top. Accepts a 'days_ago' parameter, defaults to the last 3 days.",
          tags: %w[traffic],
        },
        "ask-ai-asks-over-time": {
          id: -45,
          name: "Ask AI - Asks over time",
          description:
            "Daily Ask AI counts between start_date and end_date, inclusive (UTC). Requires Discourse AI.",
          tags: %w[ai],
        },
        "ask-ai-ask-outcomes": {
          id: -46,
          name: "Ask AI - Ask outcomes",
          description:
            "Ask AI outcome counts and percentages between start_date and end_date, inclusive (UTC). Requires Discourse AI.",
          tags: %w[ai],
        },
        "ask-ai-report-questions": {
          id: -47,
          name: "Ask AI - Questions included in this report",
          description:
            "Questions included in a particular Ask AI report, with their subjects and logged answers. Questions assigned to multiple subjects appear once per subject. Requires Discourse AI.",
          tags: %w[ai],
        },
        "ask-ai-questions": {
          id: -48,
          name: "Ask AI - What users are asking",
          description:
            "All Ask AI questions between start_date and end_date, inclusive (UTC), with logged answers. Includes groups excluded from generated reports and is not limited by the report sample size. Requires Discourse AI.",
          tags: %w[ai],
        },
      }.with_indifferent_access

      queries["most-common-likers"]["sql"] = <<~SQL
      WITH pairs AS (
          SELECT p.user_id liked, pa.user_id liker
          FROM post_actions pa
          LEFT JOIN posts p ON p.id = pa.post_id
          WHERE post_action_type_id = 2
      )
      SELECT liker liker_user_id, liked liked_user_id, count(*)
      FROM pairs
      GROUP BY liked, liker
      ORDER BY count DESC
      SQL

      queries["most-messages"]["sql"] = <<~SQL
      SELECT user_id, count(*) AS message_count
      FROM topics
      WHERE archetype = 'private_message' AND subtype = 'user_to_user'
      AND age(created_at) < interval '7 days'
      GROUP BY user_id
      ORDER BY message_count DESC
      SQL

      queries["edited-post-spam"]["sql"] = <<~SQL
      SELECT
          p.id AS post_id,
          topic_id
      FROM posts p
          JOIN users u
              ON u.id = p.user_id
          JOIN topics t
              ON t.id = p.topic_id
      WHERE p.last_editor_id = p.user_id
          AND p.self_edits > 0
          AND (u.trust_level = 0 OR u.trust_level = 1)
          AND p.deleted_at IS NULL
          AND t.deleted_at IS NULL
          AND t.archetype = 'regular'
      ORDER BY p.updated_at DESC
      LIMIT 500
      SQL

      queries["new-topics"]["sql"] = <<~SQL
      -- [params]
      -- int :months_ago = 1

      WITH query_period as (
          SELECT
              date_trunc('month', CURRENT_DATE) - :months_ago * INTERVAL '1 month' as period_start,
              date_trunc('month', CURRENT_DATE) - :months_ago * INTERVAL '1 month' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
      )

      SELECT
          t.id as topic_id,
          t.category_id
      FROM topics t
      RIGHT JOIN query_period qp
          ON t.created_at >= qp.period_start
              AND t.created_at <= qp.period_end
      WHERE t.user_id > 0
          AND t.category_id IS NOT NULL
      ORDER BY t.category_id, t.created_at DESC
      SQL

      queries["active-topics"]["sql"] = <<~SQL
      -- [params]
      -- int :months_ago = 1

      WITH query_period AS
      (SELECT date_trunc('month', CURRENT_DATE) - :months_ago * INTERVAL '1 month' AS period_start,
                                                          date_trunc('month', CURRENT_DATE) - :months_ago * INTERVAL '1 month' + INTERVAL '1 month' - INTERVAL '1 second' AS period_end)
      SELECT t.id AS topic_id,
          t.category_id,
          COUNT(p.id) AS reply_count
      FROM topics t
      JOIN posts p ON t.id = p.topic_id
      JOIN query_period qp ON p.created_at >= qp.period_start
      AND p.created_at <= qp.period_end
      WHERE t.archetype = 'regular'
      AND t.user_id > 0
      GROUP BY t.id
      ORDER BY COUNT(p.id) DESC, t.score DESC
      LIMIT 100
      SQL

      queries["top-likers"]["sql"] = <<~SQL
      -- [params]
      -- int :months_ago = 1

      WITH query_period AS (
          SELECT
              date_trunc('month', CURRENT_DATE) - :months_ago * INTERVAL '1 month' as period_start,
              date_trunc('month', CURRENT_DATE) - :months_ago * INTERVAL '1 month' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
              )

          SELECT
              ua.user_id,
              count(1) AS like_count
          FROM user_actions ua
          INNER JOIN query_period qp
          ON ua.created_at >= qp.period_start
          AND ua.created_at <= qp.period_end
          WHERE ua.action_type = 1
          GROUP BY ua.user_id
          ORDER BY like_count DESC
          LIMIT 100
      SQL

      queries["quality-users"]["sql"] = <<~SQL
      SELECT sum(p.score) / count(p) AS "average score per post",
          count(p.id) AS post_count,
          p.user_id
      FROM posts p
      JOIN users u ON u.id = p.user_id
      WHERE p.created_at >= CURRENT_DATE - INTERVAL '6 month'
      AND NOT u.admin
      AND u.active
      GROUP BY user_id,
          u.views
      HAVING count(p.id) > 50
      ORDER BY sum(p.score) / count(p) DESC
      LIMIT 50
      SQL

      queries["user-participation"]["sql"] = <<~SQL
      -- [params]
      -- int :from_days_ago = 0
      -- int :duration_days = 30
      WITH t AS (
          SELECT CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS START,
              CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) AS END
      ),
      pr AS (
          SELECT user_id, COUNT(1) AS visits,
              SUM(posts_read) AS posts_read
          FROM user_visits, t
          WHERE posts_read > 0
              AND visited_at > t.START
              AND visited_at < t.
              END
          GROUP BY
              user_id
      ),
      pc AS (
          SELECT p.user_id, COUNT(1) AS posts_created
          FROM posts p
          JOIN t ON p.created_at > t.START AND p.created_at < t.END
          JOIN pr ON p.user_id = pr.user_id
          GROUP BY
              p.user_id
      ),
      ttopics AS (
          SELECT topics.user_id, topics.posts_count
          FROM topics
          JOIN t ON topics.created_at > t.START AND topics.created_at < t.END
          JOIN pr ON topics.user_id = pr.user_id
      ),
      tc AS (
          SELECT user_id, COUNT(1) AS topics_created
          FROM ttopics
          GROUP BY user_id
      ),
      twr AS (
          SELECT user_id, COUNT(1) AS topics_with_replies
          FROM ttopics
          WHERE posts_count > 1
          GROUP BY user_id
      ),
      tv AS (
          SELECT topic_views.user_id,
              COUNT(DISTINCT(topic_views.topic_id)) AS topics_viewed
          FROM topic_views
          JOIN t ON topic_views.viewed_at > t.START AND topic_views.viewed_at < t.END
          JOIN pr ON topic_views.user_id = pr.user_id
          WHERE topic_views.user_id IS NOT NULL
          GROUP BY topic_views.user_id
      ),
      likes AS (
          SELECT post_actions.user_id AS given_by_user_id,
              posts.user_id AS received_by_user_id
          FROM post_actions
          JOIN t ON post_actions.created_at > t.START AND post_actions.created_at < t.END
          LEFT JOIN posts ON post_actions.post_id = posts.id
          WHERE
              post_action_type_id = 2
              AND (
                  post_actions.user_id IN (SELECT user_id FROM pr)
                  OR posts.user_id IN (SELECT user_id FROM pr)
              )
      ),
      lg AS (
          SELECT given_by_user_id AS user_id,
              COUNT(1) AS likes_given
          FROM likes
          GROUP BY user_id
      ),
      lr AS (
          SELECT received_by_user_id AS user_id,
              COUNT(1) AS likes_received
          FROM likes
          GROUP BY user_id
      )
      SELECT
          pr.user_id,
          username,
          name,
          email,
          visits,
          COALESCE(topics_viewed, 0) AS topics_viewed,
          COALESCE(posts_read, 0) AS posts_read,
          COALESCE(posts_created, 0) AS posts_created,
          COALESCE(topics_created, 0) AS topics_created,
          COALESCE(topics_with_replies, 0) AS topics_with_replies,
          COALESCE(likes_given, 0) AS likes_given,
          COALESCE(likes_received, 0) AS likes_received
      FROM pr
      LEFT JOIN tv USING (user_id)
      LEFT JOIN pc USING (user_id)
      LEFT JOIN tc USING (user_id)
      LEFT JOIN twr USING (user_id)
      LEFT JOIN lg USING (user_id)
      LEFT JOIN lr USING (user_id)
      LEFT JOIN user_emails e ON e.user_id = pr.user_id AND e.PRIMARY = TRUE
      LEFT JOIN users ON pr.user_id = users.id
      ORDER BY
          visits DESC,
          posts_read DESC,
          posts_created DESC
      SQL

      queries["largest-uploads"]["sql"] = <<~SQL
      SELECT posts.id AS post_id,
          uploads.original_filename,
          ROUND(uploads.filesize / 1000000.0, 2) AS size_in_mb,
          uploads.extension,
          uploads.created_at,
          uploads.url
      FROM upload_references
      JOIN uploads ON uploads.id = upload_references.upload_id
      JOIN posts ON posts.id = upload_references.target_id AND upload_references.target_type = 'Post'
      ORDER BY uploads.filesize DESC
      LIMIT 50
      SQL

      queries["inactive-users"]["sql"] = <<~SQL
      SELECT
          u.id,
          u.username_lower AS "username",
          u.created_at,
          u.last_seen_at
      FROM users u
      WHERE u.active = false
      ORDER BY u.id
      SQL

      queries["active-lurkers"]["sql"] = <<~SQL
      -- [params]
      -- int :post_read_count = 100
      WITH posts_by_user AS (
          SELECT COUNT(*) AS posts, user_id
          FROM posts
          GROUP BY user_id
      ), posts_read_by_user AS (
          SELECT SUM(posts_read) AS posts_read, user_id
          FROM user_visits
          GROUP BY user_id
      )
      SELECT
          u.id,
          u.username_lower AS "username",
          u.created_at,
          u.last_seen_at,
          COALESCE(pbu.posts, 0) AS "posts_created",
          COALESCE(prbu.posts_read, 0) AS "posts_read"
      FROM users u
      LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id
      LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id
      WHERE u.active = true
      AND posts IS NULL
      AND posts_read > :post_read_count
      ORDER BY u.id
      SQL

      queries["topic-user-notification-level"]["sql"] = <<~SQL
      -- [params]
      -- null int :user
      -- null int :notification_level

      SELECT t.category_id AS category_id, t.id AS topic_id, tu.last_visited_at AS topic_last_visited_at
      FROM topics t
      JOIN topic_users tu ON tu.topic_id = t.id AND tu.user_id = :user AND tu.notification_level = :notification_level
      ORDER BY tu.last_visited_at DESC
      SQL

      queries["assigned-topics-report"]["sql"] = <<~SQL
        SELECT a.assigned_to_id user_id, a.topic_id
        FROM assignments a
        JOIN topics t on t.id = a.topic_id
        JOIN users u on u.id = a.assigned_to_id
          WHERE a.assigned_to_type = 'User'
          AND t.deleted_at IS NULL
        ORDER BY username, topic_id
      SQL

      queries["group-members-reply-count"]["sql"] = <<~SQL
        -- [params]
        -- date :start_date
        -- date :end_date
        -- string :group_name
        -- boolean :include_pms = false

        WITH target_users AS (
        SELECT
        u.id AS user_id
        FROM users u
        JOIN group_users gu
        ON gu.user_id = u.id
        JOIN groups g
        ON g.id = gu.group_id
        WHERE g.name = :group_name
        AND gu.created_at::date <= :end_date
        ),
        target_posts AS (
        SELECT
        p.id,
        p.user_id
        FROM posts p
        JOIN topics t
        ON t.id = p.topic_id
        WHERE CASE WHEN :include_pms THEN true ELSE t.archetype = 'regular' END
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND p.created_at::date >= :start_date
        AND p.created_at::date <= :end_date
        AND p.post_number > 1
        )

        SELECT
        tu.user_id,
        COALESCE(COUNT(tp.id), 0) AS reply_count
        FROM target_users tu
        LEFT OUTER JOIN target_posts tp
        ON tp.user_id = tu.user_id
        GROUP BY tu.user_id
        ORDER BY reply_count DESC, tu.user_id
      SQL

      queries["total-assigned-topics-report"]["sql"] = <<~SQL
        SELECT a.assigned_to_id AS user_id,
        count(*)::varchar || ',/u/' || username_lower || '/activity/assigned' assigned_url
        FROM assignments a
        JOIN topics t on t.id = a.topic_id
        JOIN users u on u.id = a.assigned_to_id
        WHERE a.assigned_to_type = 'User'
          AND t.deleted_at IS NULL
        GROUP BY a.assigned_to_id, username_lower
        ORDER BY count(*) DESC, username_lower
      SQL

      queries["poll-results"]["sql"] = <<~SQL
        -- [params]
        -- string :poll_name
        -- int :post_id

        SELECT
          poll_votes.updated_at AS vote_time,
          poll_votes.poll_option_id AS vote_option,
          users.id AS user_id,
          users.username,
          users.name,
          users.trust_level,
          poll_options.html AS vote_option_full
        FROM
          poll_votes
        INNER JOIN
          polls ON polls.id = poll_votes.poll_id
        INNER JOIN
          users ON users.id = poll_votes.user_id
        INNER JOIN
          poll_options ON poll_votes.poll_id = poll_options.poll_id AND poll_votes.poll_option_id = poll_options.id
        WHERE
          polls.name = :poll_name AND
          polls.post_id = :post_id
      SQL

      queries["poll-results-ranked-choice"]["sql"] = <<~SQL
      -- [params]
      -- string :poll_name
      -- int :post_id

        SELECT
          poll_votes.updated_at AS vote_time,
          poll_votes.poll_option_id AS vote_option,
          poll_votes.rank AS vote_rank,
          users.id AS user_id,
          users.username,
          users.name,
          users.trust_level,
          poll_options.html AS vote_option_full
        FROM
          poll_votes
        INNER JOIN
          polls ON polls.id = poll_votes.poll_id
        INNER JOIN
          users ON users.id = poll_votes.user_id
        INNER JOIN
          poll_options ON poll_votes.poll_id = poll_options.poll_id AND poll_votes.poll_option_id = poll_options.id
        WHERE
          polls.name = :poll_name AND
          polls.post_id = :post_id
      SQL

      queries["top-tags-per-year"]["sql"] = <<~SQL
    -- [params]
    -- integer :rank_max = 5

    WITH data AS (SELECT
        tag_id,
        EXTRACT(YEAR FROM created_at) AS year
    FROM topic_tags)

    SELECT year, rank, name, qt FROM (
        SELECT
      tag_id,
      COUNT(tag_id) AS qt,
      year,
      rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank
        FROM
      data
        GROUP BY year, tag_id) as rnk
    INNER JOIN tags ON tags.id = rnk.tag_id
    WHERE rank <= :rank_max
    ORDER BY year DESC, qt DESC
      SQL

      queries["number_of_replies_by_category"]["sql"] = <<~SQL
    -- [params]
    -- boolean :enable_null_category = false

    WITH post AS (SELECT
        id AS post_id,
        topic_id,
        EXTRACT(YEAR FROM created_at) AS year
    FROM posts
    WHERE post_type = 1
        AND deleted_at ISNULL
        AND post_number != 1)

    SELECT
        p.year,
        t.category_id AS id,
        c.name category,
        COUNT(p.post_id) AS qt
    FROM post p
    INNER JOIN topics t ON t.id = p.topic_id
    LEFT JOIN categories c ON c.id = t.category_id
    WHERE t.deleted_at ISNULL
        AND (:enable_null_category = true OR t.category_id NOTNULL)
    GROUP BY t.category_id, c.name, p.year
    ORDER BY p.year DESC, qt DESC
      SQL

      queries["weekly-unique-visitors"]["sql"] = <<~SQL
      -- [params]
      -- int :weeks_ago = 12

      SELECT
          DATE_TRUNC('week', visited_at)::date AS week,
          COUNT(DISTINCT user_id) AS active_users
      FROM user_visits
      WHERE visited_at >= CURRENT_DATE - (:weeks_ago * INTERVAL '1 week')
      GROUP BY week
      ORDER BY week
      SQL

      queries["top-topics-by-views"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 7

      SELECT
          t.id AS topic_id,
          t.category_id,
          COALESCE(SUM(tvs.anonymous_views), 0) AS anonymous_views,
          COALESCE(SUM(tvs.logged_in_views), 0) AS logged_in_views,
          COALESCE(SUM(tvs.anonymous_views + tvs.logged_in_views), 0) AS total_views
      FROM topics t
      JOIN topic_view_stats tvs ON tvs.topic_id = t.id
          AND tvs.viewed_at >= CURRENT_DATE - CAST(:days_ago AS integer)
      WHERE t.deleted_at IS NULL
          AND t.archetype = 'regular'
      GROUP BY t.id, t.category_id
      ORDER BY total_views DESC
      LIMIT 100
      SQL

      queries["top-search-terms"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 30

      SELECT
          term,
          COUNT(*) AS searches,
          COUNT(DISTINCT user_id) AS distinct_users
      FROM search_logs
      WHERE created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
      GROUP BY term
      ORDER BY distinct_users DESC, searches DESC
      LIMIT 200
      SQL

      queries["topic-views-and-clicks"]["sql"] = <<~SQL
      -- [params]
      -- topic_id :topic_id
      -- int :days_ago = 30

      -- gapless day series, so days with zero activity still show up as rows
      WITH day_series AS (
          SELECT generate_series(
              CURRENT_DATE - CAST(:days_ago AS integer),
              CURRENT_DATE,
              '1 day'::interval
          )::date AS day
      ),
      daily_clicks AS (
          SELECT
              tlc.created_at::date AS day,
              COUNT(*) AS link_clicks
          FROM topic_link_clicks tlc
          JOIN topic_links tl ON tl.id = tlc.topic_link_id
          WHERE tl.topic_id = :topic_id
              AND tlc.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
          GROUP BY tlc.created_at::date
      ),
      daily_views AS (
          SELECT
              viewed_at AS day,
              COALESCE(SUM(anonymous_views), 0) AS anonymous_views,
              COALESCE(SUM(logged_in_views), 0) AS logged_in_views
          FROM topic_view_stats
          WHERE topic_id = :topic_id
              AND viewed_at >= CURRENT_DATE - CAST(:days_ago AS integer)
          GROUP BY viewed_at
      )
      SELECT
          ds.day,
          COALESCE(dv.anonymous_views, 0) + COALESCE(dv.logged_in_views, 0) AS total_views,
          COALESCE(dv.anonymous_views, 0) AS anonymous_views,
          COALESCE(dv.logged_in_views, 0) AS logged_in_views,
          COALESCE(dc.link_clicks, 0) AS link_clicks
      FROM day_series ds
      LEFT JOIN daily_views dv ON dv.day = ds.day
      LEFT JOIN daily_clicks dc ON dc.day = ds.day
      ORDER BY ds.day
      SQL

      queries["avg-first-response-time"]["sql"] = <<~SQL
      -- [params]
      -- int :weeks_ago = 12

      SELECT
          week,
          ROUND(AVG(hours_to_response)::numeric, 1) AS avg_hours_to_first_response
      FROM (
          SELECT
              DATE_TRUNC('week', t.created_at)::date AS week,
              EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at) / 3600.0 AS hours_to_response
          FROM topics t
          JOIN posts p ON p.topic_id = t.id
          WHERE t.created_at >= CURRENT_DATE - (:weeks_ago * INTERVAL '1 week')
              AND t.archetype = 'regular'
              AND t.deleted_at IS NULL
              AND p.deleted_at IS NULL
              AND p.post_number > 1
              AND p.user_id <> t.user_id
              AND p.post_type = 1
          GROUP BY t.id, week
      ) per_topic
      GROUP BY week
      ORDER BY week
      SQL

      queries["new-topic-response-rate"]["sql"] = <<~SQL
      -- [params]
      -- int :months_ago = 6

      WITH base_topics AS (
          SELECT
              t.id,
              t.created_at,
              date_trunc('month', t.created_at)::date AS month
          FROM topics t
          LEFT JOIN categories c ON c.id = t.category_id
          WHERE t.created_at >= date_trunc('month', CURRENT_DATE) - (:months_ago * INTERVAL '1 month')
              -- only count topics old enough to have had the full 30-day reply window
              AND t.created_at < CURRENT_DATE - INTERVAL '30 days'
              AND t.deleted_at IS NULL
              AND t.archetype = 'regular'
              AND COALESCE(c.read_restricted, false) = false
              AND t.user_id > 0
      ),
      topic_responses AS (
          SELECT
              bt.month,
              EXISTS (
                  SELECT 1
                  FROM posts p
                  WHERE p.topic_id = bt.id
                      AND p.post_number > 1
                      AND p.post_type = 1
                      AND p.deleted_at IS NULL
                      AND p.created_at <= bt.created_at + INTERVAL '30 days'
              ) AS has_any_reply,
              EXISTS (
                  SELECT 1
                  FROM posts p
                  JOIN users u ON u.id = p.user_id
                  WHERE p.topic_id = bt.id
                      AND p.post_number > 1
                      AND p.post_type = 1
                      AND p.deleted_at IS NULL
                      AND p.created_at <= bt.created_at + INTERVAL '30 days'
                      AND (u.admin OR u.moderator)
              ) AS has_staff_reply
          FROM base_topics bt
      )
      SELECT
          month,
          ROUND(100.0 * COUNT(*) FILTER (WHERE has_any_reply) / COUNT(*), 1) AS any_reply_pct,
          ROUND(100.0 * COUNT(*) FILTER (WHERE has_staff_reply) / COUNT(*), 1) AS staff_reply_pct
      FROM topic_responses
      GROUP BY month
      ORDER BY month
      SQL

      queries["community-participation-trend"]["sql"] = <<~SQL
      -- [params]
      -- int :months_ago = 6

      WITH monthly AS (
          SELECT
              date_trunc('month', p.created_at)::date AS month,
              COUNT(*) AS replies,
              COUNT(DISTINCT p.user_id) AS distinct_repliers
          FROM posts p
          JOIN topics t ON t.id = p.topic_id
          JOIN users u ON u.id = p.user_id
          LEFT JOIN categories c ON c.id = t.category_id
          WHERE p.created_at >= date_trunc('month', CURRENT_DATE) - (:months_ago * INTERVAL '1 month')
              AND p.deleted_at IS NULL
              AND p.post_type = 1
              AND p.post_number > 1
              AND t.deleted_at IS NULL
              AND t.archetype = 'regular'
              AND COALESCE(c.read_restricted, false) = false
              AND u.id > 0
              AND NOT u.admin
              AND NOT u.moderator
          GROUP BY 1
      )
      SELECT
          month,
          distinct_repliers,
          ROUND(replies::numeric / NULLIF(distinct_repliers, 0), 2) AS replies_per_person
      FROM monthly
      ORDER BY month
      SQL

      queries["trust-level-growth"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 28

      -- new signups with no recorded trust level change are still at trust level 0
      WITH trust_level_0_users AS (
          SELECT
              0 AS trust_level,
              COUNT(*) AS users_gained
          FROM users u
          LEFT JOIN (
              SELECT DISTINCT target_user_id
              FROM user_histories
              WHERE created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
                  AND action IN (2, 15) -- change_trust_level, auto_trust_level_change
          ) tlc ON u.id = tlc.target_user_id
          WHERE u.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
              AND tlc.target_user_id IS NULL
      ),
      trust_level_changes AS (
          SELECT
              uh.new_value::int AS trust_level,
              COUNT(DISTINCT uh.target_user_id) AS users_gained
          FROM user_histories uh
          JOIN users u ON uh.target_user_id = u.id
          WHERE uh.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
              AND uh.action IN (2, 15) -- change_trust_level, auto_trust_level_change
              AND uh.new_value IN ('1', '2', '3', '4')
          GROUP BY uh.new_value
      ),
      trust_levels_combined AS (
          SELECT generate_series(0, 4) AS trust_level
      )
      SELECT
          t.trust_level,
          CASE
              WHEN t.trust_level = 0 THEN COALESCE(tl0.users_gained, 0)
              ELSE COALESCE(tlc.users_gained, 0)
          END AS users_gained_recently,
          (SELECT COUNT(*) FROM users WHERE trust_level = t.trust_level) AS total_users_at_level
      FROM trust_levels_combined t
      LEFT JOIN trust_level_changes tlc ON t.trust_level = tlc.trust_level
      LEFT JOIN trust_level_0_users tl0 ON t.trust_level = 0
      ORDER BY t.trust_level
      SQL

      queries["topics-with-no-response"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 90
      -- null category_id :category_id
      -- boolean :include_subcategories = false
      -- string :interval = day

      WITH no_response_topics AS (
          SELECT * FROM (
              SELECT t.id, t.created_at, MIN(p.post_number) AS first_reply
              FROM topics t
              -- reply conditions live in the join, so topics with no replies
              -- survive with first_reply NULL instead of being filtered out
              LEFT JOIN posts p ON p.topic_id = t.id
                  AND p.user_id <> t.user_id
                  AND p.deleted_at IS NULL
                  AND p.post_type = 1
              WHERE t.archetype = 'regular'
                  AND t.deleted_at IS NULL
                  AND t.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
                  AND (
                      CAST(:category_id AS integer) IS NULL
                      OR t.category_id = :category_id
                      OR (:include_subcategories AND t.category_id IN (
                          SELECT id FROM categories WHERE parent_category_id = :category_id
                      ))
                  )
              GROUP BY t.id
          ) tt
          WHERE tt.first_reply IS NULL OR tt.first_reply < 2
      )
      SELECT
          date_trunc(:interval, created_at)::date AS period,
          COUNT(id) AS topics_without_response
      FROM no_response_topics
      GROUP BY period
      ORDER BY period
      SQL

      queries["top-posters"]["sql"] = <<~SQL
      -- [params]
      -- date :start_date
      -- date :end_date
      -- int :top_x = 10

      SELECT
          p.user_id,
          COUNT(*) AS topics_plus_replies,
          COUNT(*) FILTER (WHERE p.post_number = 1) AS topics,
          COUNT(*) FILTER (WHERE p.post_number <> 1) AS replies
      FROM posts p
      JOIN topics t ON t.id = p.topic_id
      WHERE p.created_at::date BETWEEN :start_date AND :end_date
          AND t.archetype = 'regular'
          AND p.deleted_at IS NULL
          AND t.deleted_at IS NULL
          AND p.post_type = 1
          AND p.user_id > 0
      GROUP BY p.user_id
      ORDER BY topics_plus_replies DESC
      LIMIT :top_x
      SQL

      queries["category-activity"]["sql"] = <<~SQL
      -- [params]
      -- date :start_date
      -- date :end_date

      SELECT
          c.id AS category_id,
          COUNT(DISTINCT t.id) AS topics,
          COUNT(p.id) AS posts,
          SUM(p.like_count) AS likes,
          SUM(p.reads) AS reads
      FROM categories c
      JOIN topics t ON t.category_id = c.id
      JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
      WHERE p.created_at::date BETWEEN :start_date AND :end_date
          AND p.deleted_at IS NULL
          AND t.deleted_at IS NULL
      GROUP BY c.id
      ORDER BY COUNT(p.id) DESC
      SQL

      queries["tl3-promotion-candidates"]["sql"] = <<~SQL
      -- [params]
      -- int :tl_time_period = 100
      -- int :tl_requires_days_visited = 50
      -- int :tl_requires_topics_replied_to = 10
      -- int :tl_requires_topics_viewed = 25
      -- int :tl_requires_topics_viewed_cap = 500
      -- int :tl_requires_posts_read = 25
      -- int :tl_requires_posts_read_cap = 20000
      -- int :tl_requires_max_flagged = 5
      -- int :tl_requires_topics_viewed_all_time = 200
      -- int :tl_requires_posts_read_all_time = 500
      -- int :tl_requires_likes_given = 30
      -- int :tl_requires_likes_received = 20
      -- boolean :show_all_results = true

      WITH tl3_candidates AS (
          SELECT id AS user_id, manual_locked_trust_level, silenced_till, suspended_till
          FROM users
          WHERE trust_level = 2
          AND last_seen_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
      ),
      min_topics_viewed AS (
          SELECT LEAST(COUNT(*) * (:tl_requires_topics_viewed / 100.0), :tl_requires_topics_viewed_cap) AS min_topics_viewed
          FROM topics
          WHERE visible = true AND archetype = 'regular'
              AND created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
      ),
      min_posts_read AS (
          SELECT LEAST(COUNT(*) * (:tl_requires_posts_read / 100.0), :tl_requires_posts_read_cap) AS min_posts_read
          FROM posts p
          JOIN topics t ON t.id = p.topic_id
          WHERE t.deleted_at IS NULL AND t.archetype = 'regular'
              AND p.deleted_at IS NULL AND p.post_type = 1
              AND p.created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
      ),
      min_likes_received_days AS (
          SELECT LEAST(:tl_requires_likes_received::float / 3.0, 0.75 * :tl_time_period::float)
      ),
      days_visited AS (
          SELECT uv.user_id, COUNT(uv.user_id) AS days_visited
          FROM user_visits uv
          JOIN tl3_candidates c ON c.user_id = uv.user_id
          WHERE visited_at > CURRENT_DATE - (:tl_time_period || ' days')::interval AND posts_read > 0
          GROUP BY uv.user_id
      ),
      num_topics_replied_to AS (
          SELECT p.user_id, COUNT(DISTINCT p.topic_id) AS topic_reply_count
          FROM posts p
          JOIN topics t ON t.id = p.topic_id
          JOIN tl3_candidates c ON c.user_id = p.user_id
          WHERE p.user_id <> t.user_id AND t.archetype <> 'private_message'
              AND p.deleted_at IS NULL AND t.deleted_at IS NULL
              AND p.created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
          GROUP BY p.user_id
      ),
      topics_viewed AS (
          SELECT tv.user_id, COUNT(tv.user_id) AS topic_view_count
          FROM topic_views tv
          JOIN topics t ON t.id = tv.topic_id
          JOIN tl3_candidates c ON c.user_id = tv.user_id
          WHERE t.archetype <> 'private_message'
              AND viewed_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
          GROUP BY tv.user_id
      ),
      posts_read AS (
          SELECT uv.user_id, SUM(posts_read) AS posts_read
          FROM user_visits uv
          JOIN tl3_candidates c ON c.user_id = uv.user_id
          WHERE visited_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
          GROUP BY uv.user_id
      ),
      num_flagged_posts AS (
          SELECT p.user_id, COUNT(DISTINCT pa.post_id) AS num_flagged_posts
          FROM post_actions pa
          JOIN posts p ON p.id = pa.post_id
          JOIN tl3_candidates c ON c.user_id = p.user_id
          WHERE p.created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
              AND (spam_count > 0 OR inappropriate_count > 0)
              AND agreed_at IS NOT NULL AND pa.user_id <> p.user_id
          GROUP BY p.user_id
      ),
      num_flagged_by_users AS (
          SELECT p.user_id, COUNT(DISTINCT pa.user_id) AS num_flagged_by_users
          FROM post_actions pa
          JOIN posts p ON p.id = pa.post_id
          JOIN tl3_candidates c ON c.user_id = p.user_id
          WHERE p.created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
              AND (spam_count > 0 OR inappropriate_count > 0)
              AND agreed_at IS NOT NULL AND pa.user_id <> p.user_id
          GROUP BY p.user_id
      ),
      topics_viewed_all_time AS (
          SELECT tv.user_id, COUNT(topic_id) AS topics_viewed_all_time
          FROM topic_views tv
          JOIN topics t ON t.id = tv.topic_id
          JOIN tl3_candidates c ON c.user_id = tv.user_id
          WHERE t.archetype = 'regular'
          GROUP BY tv.user_id
      ),
      posts_read_all_time AS (
          SELECT uv.user_id, SUM(posts_read) AS posts_read_all_time
          FROM user_visits uv
          JOIN tl3_candidates c ON c.user_id = uv.user_id
          GROUP BY uv.user_id
      ),
      num_likes_given AS (
          SELECT ua.user_id, COUNT(*) AS num_likes_given
          FROM user_actions ua
          JOIN topics t ON t.id = ua.target_topic_id
          JOIN tl3_candidates c ON c.user_id = ua.user_id
          WHERE ua.created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
              AND t.archetype = 'regular' AND ua.action_type = 1
          GROUP BY ua.user_id
      ),
      num_likes_received AS (
          SELECT ua.user_id, COUNT(*) AS num_likes_received,
              COUNT(DISTINCT acting_user_id) AS num_likes_received_users,
              COUNT(DISTINCT ua.created_at::date) AS num_likes_received_days
          FROM user_actions ua
          JOIN topics t ON t.id = ua.target_topic_id
          JOIN tl3_candidates c ON c.user_id = ua.user_id
          WHERE ua.created_at >= CURRENT_DATE - (:tl_time_period || ' days')::interval
              AND t.archetype = 'regular' AND ua.action_type = 2
          GROUP BY ua.user_id
      ),
      penalty_counts AS (
          -- silences/suspensions in the last 6 months (FORGIVENESS_PERIOD) block promotion,
          -- but a staff-issued unsilence/unsuspend forgives them (a system-issued one does not)
          SELECT
              uh.target_user_id AS user_id,
              SUM(
                  CASE
                      WHEN uh.action = 30 THEN 1 -- silence_user
                      WHEN uh.action = 31 AND uh.acting_user_id <> -1 THEN -1 -- unsilence_user
                      WHEN uh.action = 10 THEN 1 -- suspend_user
                      WHEN uh.action = 11 AND uh.acting_user_id <> -1 THEN -1 -- unsuspend_user
                      ELSE 0
                  END
              ) AS penalty_count
          FROM user_histories uh
          JOIN tl3_candidates c ON c.user_id = uh.target_user_id
          WHERE uh.action IN (10, 11, 30, 31)
              AND uh.created_at > NOW() - INTERVAL '6 months'
          GROUP BY uh.target_user_id
      ),
      candidate_results AS (
          SELECT
              c.user_id,
              c.manual_locked_trust_level IS NULL AS trust_level_unlocked_criteria_met,
              (c.suspended_till IS NULL OR c.suspended_till <= CURRENT_TIMESTAMP)
                  AND (c.silenced_till IS NULL OR c.silenced_till <= CURRENT_TIMESTAMP) AS not_penalized_criteria_met,
              COALESCE(pc.penalty_count, 0) AS penalty_count,
              COALESCE(pc.penalty_count, 0) <= 0 AS penalty_history_criteria_met,
              COALESCE(days_visited, 0) AS days_visited,
              COALESCE(days_visited, 0) >= :tl_requires_days_visited AS visits_criteria_met,
              COALESCE(topic_reply_count, 0) AS topic_reply_count,
              COALESCE(topic_reply_count, 0) >= :tl_requires_topics_replied_to AS replies_criteria_met,
              COALESCE(topic_view_count, 0) AS topic_view_count,
              COALESCE(topic_view_count, 0) >= (SELECT * FROM min_topics_viewed) AS topic_views_criteria_met,
              COALESCE(posts_read, 0) AS posts_read,
              COALESCE(posts_read, 0) >= (SELECT * FROM min_posts_read) AS posts_read_criteria_met,
              COALESCE(num_flagged_posts, 0) AS num_flagged_posts,
              COALESCE(num_flagged_posts, 0) <= :tl_requires_max_flagged AS flagged_post_criteria_met,
              COALESCE(num_flagged_by_users, 0) AS num_flagged_by_users,
              COALESCE(num_flagged_by_users, 0) <= :tl_requires_max_flagged AS flagged_by_users_criteria_met,
              COALESCE(topics_viewed_all_time, 0) AS topics_viewed_all_time,
              COALESCE(topics_viewed_all_time, 0) >= :tl_requires_topics_viewed_all_time AS all_time_topic_views_criteria_met,
              COALESCE(posts_read_all_time, 0) AS posts_read_all_time,
              COALESCE(posts_read_all_time, 0) >= :tl_requires_posts_read_all_time AS posts_read_all_time_criteria_met,
              COALESCE(num_likes_given, 0) AS num_likes_given,
              COALESCE(num_likes_given, 0) >= :tl_requires_likes_given AS likes_given_criteria_met,
              COALESCE(num_likes_received, 0) AS num_likes_received,
              COALESCE(num_likes_received, 0) >= :tl_requires_likes_received AS likes_received_criteria_met,
              COALESCE(num_likes_received_users, 0) AS num_likes_received_users,
              COALESCE(num_likes_received_users, 0) >= :tl_requires_likes_received::float / 4.0 AS likes_received_users_criteria_met,
              COALESCE(num_likes_received_days, 0) AS num_likes_received_days,
              COALESCE(num_likes_received_days, 0) >= (SELECT * FROM min_likes_received_days) AS likes_received_days_criteria_met
          FROM tl3_candidates c
          LEFT JOIN days_visited dv ON dv.user_id = c.user_id
          LEFT JOIN num_topics_replied_to ntr ON ntr.user_id = c.user_id
          LEFT JOIN topics_viewed tv ON tv.user_id = c.user_id
          LEFT JOIN posts_read pr ON pr.user_id = c.user_id
          LEFT JOIN num_flagged_posts nfp ON nfp.user_id = c.user_id
          LEFT JOIN num_flagged_by_users nfu ON nfu.user_id = c.user_id
          LEFT JOIN topics_viewed_all_time tvat ON tvat.user_id = c.user_id
          LEFT JOIN posts_read_all_time prat ON prat.user_id = c.user_id
          LEFT JOIN num_likes_given nlg ON nlg.user_id = c.user_id
          LEFT JOIN num_likes_received nlr ON nlr.user_id = c.user_id
          LEFT JOIN penalty_counts pc ON pc.user_id = c.user_id
      )
      SELECT * FROM candidate_results
      WHERE CASE WHEN :show_all_results THEN true ELSE visits_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE replies_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE topic_views_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE posts_read_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE flagged_post_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE flagged_by_users_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE all_time_topic_views_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE posts_read_all_time_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE likes_given_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE likes_received_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE likes_received_users_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE likes_received_days_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE trust_level_unlocked_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE not_penalized_criteria_met END
          AND CASE WHEN :show_all_results THEN true ELSE penalty_history_criteria_met END
      ORDER BY days_visited DESC
      SQL

      queries["silenced-users"]["sql"] = <<~SQL
      -- [params]
      -- null date :start_date
      -- null date :end_date
      -- null string :silenced_by

      SELECT
          silenced_users.id AS user_id,
          silenced_users.silenced_till AS silenced_till,
          COALESCE(staff.username, 'system') AS silenced_by,
          user_histories.created_at AS silenced_at
      FROM users silenced_users
      -- lateral join picks only the most recent silence record per user
      LEFT JOIN LATERAL (
          SELECT uh.*
          FROM user_histories uh
          WHERE uh.target_user_id = silenced_users.id
              AND uh.action = 30 -- silence_user
          ORDER BY uh.id DESC
          LIMIT 1
      ) user_histories ON true
      LEFT JOIN users staff
          ON staff.id = user_histories.acting_user_id
      WHERE silenced_users.silenced_till > CURRENT_TIMESTAMP
          AND (:start_date IS NULL OR user_histories.created_at >= :start_date)
          AND (:end_date IS NULL OR user_histories.created_at <= :end_date)
          AND (
              :silenced_by IS NULL
              OR ((:silenced_by = 'system' AND staff.username IS NULL) OR staff.username = :silenced_by)
          )
      ORDER BY user_histories.created_at DESC
      SQL

      queries["user-warnings"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 28

      SELECT
          w.user_id AS warned_user_id,
          w.created_by_id AS warning_creator_user_id,
          w.topic_id,
          w.created_at
      FROM user_warnings w
      WHERE w.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
      ORDER BY w.created_at DESC
      SQL

      queries["most-flagged-users"]["sql"] = <<~SQL
      SELECT
          p.user_id,
          COUNT(DISTINCT pa.post_id) AS flagged_posts,
          COUNT(*) AS agreed_flags
      FROM post_actions pa
      JOIN posts p ON pa.post_id = p.id
      WHERE pa.agreed_at IS NOT NULL
          AND p.user_id > 0
      GROUP BY p.user_id
      ORDER BY agreed_flags DESC, flagged_posts DESC
      LIMIT 100
      SQL

      queries["subcategory-permission-drift"]["sql"] = <<~SQL
      SELECT subcategories.* FROM (
          SELECT
              category.parent_category_id, category.id AS category_id, category.name AS category_name,
              category_group.permission_type,
              groups.name AS group_name, groups.id AS group_id
          FROM categories category
          INNER JOIN category_groups category_group ON category_group.category_id = category.id
          INNER JOIN groups ON groups.id = category_group.group_id
          WHERE parent_category_id IS NOT NULL
      ) subcategories
      LEFT JOIN (
          SELECT
              category.id AS category_id, category_group.permission_type,
              groups.id AS group_id
          FROM categories category
          INNER JOIN category_groups category_group ON category_group.category_id = category.id
          INNER JOIN groups ON groups.id = category_group.group_id
          WHERE parent_category_id IS NULL
      ) parent_categories
          ON parent_categories.category_id = subcategories.parent_category_id
          AND parent_categories.group_id = subcategories.group_id
          AND parent_categories.permission_type = subcategories.permission_type
      -- anti-join: keep only grants with no matching grant on the parent category
      WHERE parent_categories.category_id IS NULL
      SQL

      queries["reading-participation-histogram"]["sql"] = <<~SQL
      -- [params]
      -- int :from_days_ago = 0
      -- int :duration_days = 28

      WITH t AS (
          SELECT
              CURRENT_DATE::timestamp - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS period_start,
              CURRENT_DATE::timestamp - (:from_days_ago * (INTERVAL '1 days')) AS period_end
      ),
      read_visits AS (
          SELECT user_id, SUM(posts_read) AS posts_read
          FROM user_visits, t
          WHERE posts_read >= 1
              AND visited_at > t.period_start
              AND visited_at < t.period_end
          GROUP BY user_id
      )
      SELECT
          CASE
              WHEN posts_read <= 1 THEN '0001'
              WHEN posts_read <= 3 THEN '0002 - 0003'
              WHEN posts_read <= 7 THEN '0004 - 0007'
              WHEN posts_read <= 15 THEN '0008 - 0015'
              WHEN posts_read <= 31 THEN '0016 - 0031'
              WHEN posts_read <= 63 THEN '0032 - 0063'
              WHEN posts_read <= 127 THEN '0064 - 0127'
              WHEN posts_read <= 255 THEN '0128 - 0255'
              WHEN posts_read <= 511 THEN '0256 - 0511'
              WHEN posts_read <= 1023 THEN '0512 - 1023'
              WHEN posts_read <= 2047 THEN '1024 - 2047'
              ELSE '2048+'
          END AS posts_read_bucket,
          COUNT(*) AS num_users
      FROM read_visits
      GROUP BY posts_read_bucket
      ORDER BY posts_read_bucket
      SQL

      queries["flags-by-type"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 90

      SELECT
          COALESCE(f.name, 'score_type_' || rs.reviewable_score_type) AS flag_type,
          -- system and bot accounts have non-positive user ids
          COUNT(*) FILTER (WHERE rs.user_id > 0) AS reported_by_users,
          COUNT(*) FILTER (WHERE rs.user_id <= 0) AS automated,
          COUNT(*) AS total
      FROM reviewable_scores rs
      LEFT JOIN flags f ON f.id = rs.reviewable_score_type
      WHERE rs.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
      GROUP BY 1
      ORDER BY total DESC
      SQL

      queries["flags-handled-by-staff"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 90

      SELECT
          rs.reviewed_by_id AS user_id,
          COUNT(*) AS flags_handled
      FROM reviewable_scores rs
      JOIN users u ON u.id = rs.reviewed_by_id
      WHERE (u.admin OR u.moderator)
          AND rs.reviewed_at >= CURRENT_DATE - CAST(:days_ago AS integer)
      GROUP BY rs.reviewed_by_id
      ORDER BY flags_handled DESC
      LIMIT 100
      SQL

      queries["suspended-users"]["sql"] = <<~SQL
      -- [params]
      -- null date :start_date
      -- null date :end_date
      -- null string :suspended_by

      SELECT
          u.id AS user_id,
          u.suspended_at,
          u.suspended_till,
          COALESCE(staff.username, 'system') AS suspended_by,
          uh.created_at AS suspension_logged_at,
          uh.details
      FROM users u
      -- lateral join picks only the most recent suspension record per user
      LEFT JOIN LATERAL (
          SELECT *
          FROM user_histories
          WHERE target_user_id = u.id
              AND action = 10 -- suspend_user
          ORDER BY id DESC
          LIMIT 1
      ) uh ON true
      LEFT JOIN users staff
          ON staff.id = uh.acting_user_id
      WHERE u.suspended_till > CURRENT_TIMESTAMP
          AND (:start_date IS NULL OR uh.created_at >= :start_date)
          AND (:end_date IS NULL OR uh.created_at <= :end_date)
          AND (
              :suspended_by IS NULL
              OR ((:suspended_by = 'system' AND staff.username IS NULL) OR staff.username = :suspended_by)
          )
      ORDER BY uh.created_at DESC NULLS LAST
      SQL

      queries["foreign-language-topics"]["sql"] = <<~SQL
      -- [params]
      -- string :primary_locale = en
      -- int :days_ago = 30

      SELECT
          t.locale,
          t.id AS topic_id,
          t.category_id,
          t.created_at
      FROM topics t
      WHERE t.locale IS NOT NULL
          AND t.locale <> ''
          AND split_part(t.locale, '_', 1) <> split_part(:primary_locale, '_', 1)
          AND t.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
          AND t.deleted_at IS NULL
      ORDER BY t.created_at DESC
      SQL

      queries["foreign-language-posts"]["sql"] = <<~SQL
      -- [params]
      -- string :primary_locale = en
      -- int :days_ago = 30

      SELECT
          p.locale,
          p.id AS post_id,
          p.topic_id,
          LEFT(p.raw, 100) AS excerpt,
          p.created_at
      FROM posts p
      WHERE p.locale IS NOT NULL
          AND p.locale <> ''
          AND split_part(p.locale, '_', 1) <> split_part(:primary_locale, '_', 1)
          AND p.created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
          AND p.deleted_at IS NULL
      ORDER BY p.created_at DESC
      SQL

      queries["crawler-traffic-overview"]["sql"] = <<~SQL
      -- [params]
      -- int :hours = 24

      WITH events AS (
          SELECT score
          FROM browser_pageview_events
          WHERE created_at >= NOW() - (:hours * INTERVAL '1 hour')
      )
      SELECT 'Not scored (pending or no signals)' AS bucket, COUNT(*) FILTER (WHERE score IS NULL) AS pageviews FROM events
      UNION ALL
      SELECT 'Very likely user (under 60)', COUNT(*) FILTER (WHERE score < 60) FROM events
      UNION ALL
      SELECT 'Likely crawler (60+)', COUNT(*) FILTER (WHERE score >= 60) FROM events
      SQL

      queries["crawler-traffic-detailed"]["sql"] = <<~SQL
      -- [params]
      -- int :hours = 24
      -- int :min_score = 40

      SELECT
          MAX(e.score) AS max_score,
          e.session_id,
          e.ip_address,
          e.user_id,
          e.user_agent,
          e.asn,
          e.country_code,
          COUNT(*) AS pageviews,
          MAX(s.automation_ua_score) AS automation_ua,
          MAX(s.known_asn_score) AS known_asn,
          MAX(s.datacenter_asn_score) AS datacenter_asn,
          MAX(s.single_request_no_referrer_score) AS single_request_no_referrer,
          MAX(s.stale_browser_score) AS stale_browser,
          MAX(s.velocity_score) AS velocity,
          MAX(s.churn_score) AS churn,
          MAX(s.rapid_nav_score) AS rapid_nav,
          MAX(s.ip_rotation_score) AS ip_rotation,
          MAX(s.referrer_score) AS referrer,
          MAX(s.engagement_score) AS no_engagement,
          NULLIF(
              CONCAT_WS(', ',
                  CASE WHEN MAX(s.automation_ua_score) > 0 THEN 'automation UA' END,
                  CASE WHEN MAX(s.known_asn_score) > 0 THEN 'known crawler ASN' END,
                  CASE WHEN MAX(s.datacenter_asn_score) > 0 THEN 'datacenter ASN' END,
                  CASE
                    WHEN MAX(s.single_request_no_referrer_score) = #{CrawlerScorer::SINGLE_REQUEST_NO_REFERRER_SCORE + CrawlerScorer::SINGLE_REQUEST_LOCALE_PARAM_BONUS}
                      THEN 'single direct locale request (+' || MAX(s.single_request_no_referrer_score) || ')'
                    WHEN MAX(s.single_request_no_referrer_score) > 0
                      THEN 'single direct request (+' || MAX(s.single_request_no_referrer_score) || ')'
                  END,
                  CASE WHEN MAX(s.stale_browser_score) > 0 THEN 'stale Chromium (+' || MAX(s.stale_browser_score) || ')' END,
                  CASE WHEN MAX(s.velocity_score) > 0 THEN 'high velocity (+' || MAX(s.velocity_score) || ')' END,
                  CASE WHEN MAX(s.churn_score) > 0 THEN 'session churn (+' || MAX(s.churn_score) || ')' END,
                  CASE WHEN MAX(s.rapid_nav_score) > 0 THEN 'rapid navigation' END,
                  CASE WHEN MAX(s.ip_rotation_score) > 0 THEN 'ip rotation (+' || MAX(s.ip_rotation_score) || ')' END,
                  CASE WHEN MAX(s.referrer_score) > 0 THEN 'bad referrer (+' || MAX(s.referrer_score) || ')' END,
                  CASE WHEN MAX(s.engagement_score) > 0 THEN 'no measured interaction (+' || MAX(s.engagement_score) || ')' END
              ),
              ''
          ) AS reasons
      FROM browser_pageview_events e
      JOIN browser_pageview_event_scores s ON s.event_id = e.id
      WHERE e.created_at >= NOW() - (:hours * INTERVAL '1 hour')
          AND e.score > :min_score
      GROUP BY e.ip_address, e.user_agent, e.asn, e.country_code, e.user_id, e.session_id
      ORDER BY max_score DESC, pageviews DESC
      SQL

      queries["suspected-bot-networks"]["sql"] = <<~SQL
      -- [params]
      -- int :days_ago = 3

      SELECT
          ip_address,
          asn,
          country_code,
          COUNT(*) AS pageviews,
          COUNT(DISTINCT session_id) AS sessions,
          ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT session_id), 0), 1) AS views_per_session,
          COUNT(DISTINCT user_agent) AS user_agents,
          COUNT(DISTINCT topic_id) FILTER (WHERE topic_id IS NOT NULL) AS topics_touched,
          -- network-wide total, so a scrape spread thin across many IPs
          -- on one network still sorts to the top
          SUM(COUNT(*)) OVER (PARTITION BY asn) AS asn_total_pageviews,
          MIN(created_at) AS first_seen,
          MAX(created_at) AS last_seen,
          (array_agg(user_agent ORDER BY created_at DESC))[1] AS sample_user_agent
      FROM browser_pageview_events
      WHERE created_at >= CURRENT_DATE - CAST(:days_ago AS integer)
      GROUP BY ip_address, asn, country_code
      ORDER BY asn_total_pageviews DESC, pageviews DESC
      LIMIT 100
      SQL

      queries["ask-ai-asks-over-time"]["sql"] = <<~SQL
      -- [params]
      -- date :start_date
      -- date :end_date

      WITH daily_asks AS (
        SELECT asked_at::date AS date, COUNT(*) AS asks
        FROM ask_ai_logs
        WHERE asked_at >= :start_date::date
          AND asked_at < :end_date::date + INTERVAL '1 day'
          AND ask_outcome IS DISTINCT FROM 4 -- skipped: the reader never requested an answer
        GROUP BY asked_at::date
      )
      SELECT days.date::date AS date, COALESCE(daily_asks.asks, 0) AS asks
      FROM generate_series(:start_date::date, :end_date::date, INTERVAL '1 day') AS days(date)
      LEFT JOIN daily_asks ON daily_asks.date = days.date::date
      ORDER BY days.date
      SQL

      queries["ask-ai-ask-outcomes"]["sql"] = <<~SQL
      -- [params]
      -- date :start_date
      -- date :end_date

      SELECT
        CASE ask_outcome
          WHEN 0 THEN 'answered'
          WHEN 1 THEN 'no_answer'
          WHEN 2 THEN 'failed'
          WHEN 3 THEN 'cancelled'
          ELSE 'pending'
        END AS outcome,
        COUNT(*) AS asks,
        ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS percentage
      FROM ask_ai_logs
      WHERE asked_at >= :start_date::date
        AND asked_at < :end_date::date + INTERVAL '1 day'
        AND ask_outcome IS DISTINCT FROM 4 -- skipped: the reader never requested an answer
      GROUP BY ask_outcome
      ORDER BY ask_outcome NULLS LAST
      SQL

      queries["ask-ai-report-questions"]["sql"] = <<~SQL
      -- [params]
      -- int :report_id

      SELECT
        subjects.name AS subject,
        logs.user_id,
        TO_CHAR(logs.asked_at, 'Mon DD HH24:MI "UTC"') AS asked_at,
        logs.query,
        logs.answer_title,
        logs.answer,
        CASE logs.ask_outcome
          WHEN 0 THEN 'answered'
          WHEN 1 THEN 'no_answer'
          WHEN 2 THEN 'failed'
          WHEN 3 THEN 'cancelled'
          ELSE 'pending'
        END AS outcome
      FROM ask_ai_reports reports
      JOIN ask_ai_report_subjects subjects ON subjects.ask_ai_report_id = reports.id
      JOIN ask_ai_report_subject_asks memberships ON memberships.ask_ai_report_subject_id = subjects.id
      JOIN ask_ai_logs logs ON logs.id = memberships.ask_ai_log_id
      WHERE reports.id = :report_id
      ORDER BY subjects.position, subjects.id, logs.asked_at DESC, logs.id DESC
      SQL

      queries["ask-ai-questions"]["sql"] = <<~SQL
      -- [params]
      -- date :start_date
      -- date :end_date

      SELECT
        user_id,
        TO_CHAR(asked_at, 'Mon DD HH24:MI "UTC"') AS asked_at,
        query,
        answer_title,
        answer,
        CASE ask_outcome
          WHEN 0 THEN 'answered'
          WHEN 1 THEN 'no_answer'
          WHEN 2 THEN 'failed'
          WHEN 3 THEN 'cancelled'
          ELSE 'pending'
        END AS outcome
      FROM ask_ai_logs
      WHERE asked_at >= :start_date::date
        AND asked_at < :end_date::date + INTERVAL '1 day'
        AND ask_outcome IS DISTINCT FROM 4 -- skipped: the reader never requested an answer
      ORDER BY ask_ai_logs.asked_at DESC, id DESC
      SQL

      # convert query ids from "mostcommonlikers" to "-1", "mostmessages" to "-2" etc.
      queries.transform_keys!.with_index { |key, idx| "-#{idx + 1}" }
      queries
    end
    private_class_method :definitions
  end
end