Summary: The Reddit Marketing Dataset, edition 2026-10-31

SELECT COUNT(*) AS threads_found,
       SUM(decision = 'kept') AS threads_kept,
       SUM(decision = 'dropped') AS threads_dropped,
       COUNT(DISTINCT CASE WHEN lower(community) NOT LIKE 'u/%'
                            AND lower(community) NOT LIKE 'u\_%' ESCAPE '\'
                           THEN lower(community) END) AS communities_with_threads,
       (SELECT COUNT(DISTINCT j.value)
          FROM found f2, json_each(f2.queries) j) AS search_phrases
FROM found;

SELECT (julianday(MAX(first_at)) - julianday(MIN(first_at))) / 7.0 AS weeks,
       MIN(first_at) AS first_found, MAX(first_at) AS last_found
FROM found;

SELECT COUNT(*) AS candidates,
       SUM(state = 'posted') AS candidates_posted,
       SUM(state = 'skipped') AS candidates_skipped,
       SUM(state = 'expired') AS candidates_expired
FROM items WHERE kind <> 'dm';

SELECT COUNT(*) AS posted,
       COUNT(DISTINCT lower(community)) AS communities_posted_in,
       SUM(link = 1) AS posted_with_link,
       ROUND(100.0 * SUM(link = 1) / COUNT(*), 1) AS link_share_pct,
       SUM(offer = 1) AS posted_with_offer
FROM sends WHERE outcome = 'posted';

WITH last AS (
  SELECT permalink, state,
         ROW_NUMBER() OVER (PARTITION BY permalink
                            ORDER BY checked_at DESC, check_no DESC) AS rn
  FROM comment_checks WHERE state <> 'unknown')
SELECT COUNT(*) AS comments_checked,
       SUM(state = 'visible') AS visible,
       SUM(state = 'collapsed') AS collapsed,
       SUM(state IN ('removed', 'not_found')) AS removed,
       SUM(state = 'deleted') AS deleted,
       ROUND(100.0 * SUM(state IN ('removed', 'not_found')) / COUNT(*), 2) AS removal_rate_pct
FROM last WHERE rn = 1;

SELECT COUNT(*) AS ban_events,
       COUNT(DISTINCT lower(community)) AS communities_banned,
       MIN(at) AS first_ban, MAX(at) AS last_ban
FROM bans;

ATTACH 'reply-tags.sqlite3' AS rt;
SELECT SUM(p.n) AS visits,
       COUNT(DISTINCT p.content) AS replies_with_visits,
       COUNT(DISTINCT lower(t.community)) AS communities_with_visits
FROM pageview_campaigns p
JOIN rt.reply_tags t ON t.tag = p.content
WHERE p.source LIKE 'utm:reddit%'
  AND p.path NOT LIKE '%/engagement/%'
  AND p.path NOT LIKE '%/cohort/%';

SELECT lower(community) AS community,
       COUNT(*) AS threads_found,
       ROUND(COUNT(*) / ((SELECT julianday(MAX(first_at)) - julianday(MIN(first_at))
                          FROM found) / 7.0), 2) AS asks_per_week
FROM found
WHERE community IS NOT NULL AND community <> ''
  AND lower(community) NOT LIKE 'u/%'
  AND lower(community) NOT LIKE 'u\_%' ESCAPE '\'
GROUP BY lower(community)
ORDER BY threads_found DESC, community
LIMIT 25;

ATTACH 'reply-tags.sqlite3' AS rt;
WITH v AS (
  SELECT lower(t.community) AS community, SUM(p.n) AS visits
  FROM pageview_campaigns p
  JOIN rt.reply_tags t ON t.tag = p.content
  WHERE p.source LIKE 'utm:reddit%'
    AND p.path NOT LIKE '%/engagement/%'
    AND p.path NOT LIKE '%/cohort/%'
  GROUP BY lower(t.community))
SELECT community, visits,
       ROUND(100.0 * visits / (SELECT SUM(visits) FROM v), 3) AS visit_share_pct
FROM v
ORDER BY visits DESC, community
LIMIT 25;