# 查找上个月的内容分享数量

**URL:** <https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2018年九月25日 22:37 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050 "2018-09-25T22:37:53Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jerdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jerdog/32/122843_2.png) [@jerdog](https://meta.discourse.org/u/jerdog)\
**Post date:** [2018年九月25日 22:37 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/1 "2018-09-25T22:37:54Z")

</div>

我正在为管理层编写一些查询，其中他们想了解共享内容的数量，即有人分享链接并被点击。这是我创建的查询，但它给出的数字无法通过合理性检查——8万。我是否遗漏了什么？

```
-- [params]
-- string :interval = 1 month
-- string :trunc = month
with t as (
    select 
    date_trunc(:trunc, CURRENT_TIMESTAMP - INTERVAL :interval) as start,
    date_trunc(:trunc, CURRENT_TIMESTAMP) as end
)    
SELECT count(id) as "Total" 
FROM incoming_links i, t
WHERE i.created_at between t.start and t.end

```

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [2018年九月25日 23:30 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/2 "2018-09-25T23:30:42Z")

</div>

> [@jerdog](#):
>
> doesn’t pass the plausibility check

If you query the topics / posts tables do the numbers match up?

> <https://github.com/discourse/discourse/blob/main/app/models/incoming_link.rb#L93-L101>

Maybe you’re not asking the right question? eg. if there is a link to a post and it gets clicked 100 times, does that count as 1 (the same post) or 100 (the clicks)

---

<div class="post-metadata">

**Author:** ![jerdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jerdog/32/122843_2.png) [@jerdog](https://meta.discourse.org/u/jerdog)\
**Post date:** [2018年九月26日 04:54 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/3 "2018-09-26T04:54:30Z")

</div>

No, wanting to know how many times shared content gets viewed (clicked) in a month - and I am not buying that in a month where I have only about 300 new topics created (through either the `share a link to this post` option or the timestamp link - intentional activities) I am having 80k visits from that content. It certainly seems high unless it’s including crawler content?

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [2018年九月26日 05:12 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/4 "2018-09-26T05:12:15Z")

</div>

What do you get if you temporarily change the count(id) to counting distinct `post_id`s?

---

<div class="post-metadata">

**Author:** ![jerdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jerdog/32/122843_2.png) [@jerdog](https://meta.discourse.org/u/jerdog)\
**Post date:** [2018年九月26日 13:15 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/5 "2018-09-26T13:15:59Z")

</div>

Right - so that’s a more reasonable number (5k) but I am now trying to rationalise what I am seeing… So correct me if I am wrong here:

- 80k (counting each click from a share)
- 5k (counting the number of posts shared)

Both of course are distinctly different, and I get that, but the 80k number still doesn’t make sense to me. If I read that correctly, that means that links which were shared (5k) got clicked 80k times in the past month, incidentally accounting for ~20% of all our pageviews, and 40 times more than our user visits. That is the stat which doesn’t make sense to me as I am not seeing that behavior

---

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [2018年九月26日 20:54 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/6 "2018-09-26T20:54:55Z")

</div>

I’m almost certain the 80K must be including anon (not logged in registered members) visits. Not necessarily bot crawl visits, which AFAIK, are not counted.

I don’t know about other forums, but at SitePoint the anon numbers are consistently higher then the logged in numbers.

---

<div class="post-metadata">

**Author:** ![jerdog](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jerdog/32/122843_2.png) [@jerdog](https://meta.discourse.org/u/jerdog)\
**Post date:** [2018年九月27日 03:46 UTC](https://meta.discourse.org/t/looking-to-find-the-number-of-content-shares-in-the-last-month/98050/7 "2018-09-27T03:46:27Z")

</div>

Yeah.. makes sense, though again still not making sense that 80k clicks in one month are from shared content (anonymous or not).