# בכמה פעמים הוזכר מילה

**URL:** https://meta.discourse.org/t/counting-number-of-times-a-word-has-been-said/310282
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2 ביוני,‏ 2024,‏ 8:49pm UTC](https://meta.discourse.org/t/counting-number-of-times-a-word-has-been-said/310282 "2024-06-02T20:49:39Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![codergautam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/codergautam/32/261083_2.png) [@codergautam](https://meta.discourse.org/u/codergautam)
#### Post date: [2 ביוני,‏ 2024,‏ 8:49pm UTC](https://meta.discourse.org/t/counting-number-of-times-a-word-has-been-said/310282/1 "2024-06-02T20:49:39Z")

</div>

I have been curious about checking trends of specific word usage on my forum, is there any query I could use to see how many times a substring has been mentioned by week?

---

<div class="post-metadata">

### Author: ![codergautam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/codergautam/32/261083_2.png) [@codergautam](https://meta.discourse.org/u/codergautam)
#### Post date: [2 ביוני,‏ 2024,‏ 8:49pm UTC](https://meta.discourse.org/t/counting-number-of-times-a-word-has-been-said/310282/2 "2024-06-02T20:49:48Z")

</div>

Found a working one with the help of ChatGPT

```plaintext
WITH date_series AS (
  SELECT generate_series(
           DATE_TRUNC('week', MIN(created_at)), -- Start of the first week
           DATE_TRUNC('week', MAX(created_at)), -- Start of the last week
           '1 week'::interval -- Weekly interval
         ) AS week_start
  FROM posts
),
posts_with_substring AS (
  SELECT
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS total_posts,
    SUM((LENGTH(raw) - LENGTH(REPLACE(lower(raw), lower('your_substring'), ''))) / LENGTH('your_substring')) AS substring_count
  FROM
    posts
  WHERE
    raw ILIKE '%test%'
  GROUP BY
    DATE_TRUNC('week', created_at)
)
SELECT
  ds.week_start,
  COALESCE(pws.total_posts, 0) AS total_posts
FROM
  date_series ds
LEFT JOIN
  posts_with_substring pws ON ds.week_start = pws.week_start
ORDER BY
  ds.week_start

```

---

<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: [2 ביולי,‏ 2024,‏ 8:50pm UTC](https://meta.discourse.org/t/counting-number-of-times-a-word-has-been-said/310282/3 "2024-07-02T20:50:23Z")

</div>

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