3

I'm trying to query for some stats on how many posts have been deleted as spam/abusive. I've got what I think should work, but the numbers it throws out don't make sense.

Since posts deleted this way are characterized by being both deleted and locked, I'm querying for those attributes by looking at the PostHistory table.

My initial query looks like this:

SELECT
  COUNT(DISTINCT ph0.PostId)
FROM
  PostHistory ph0
INNER JOIN
  PostHistory ph1
  ON
    ph0.PostId = ph1.PostId AND
    ph1.PostHistoryTypeId = 12
WHERE
  ph0.PostHistoryTypeId = 14

That one throws up a count of 397, which doesn't make sense. There are at least 6485 posts that have been identified as spam on Stack Overflow. So, to check that query, I'm using a debugging query that outputs the post bodies:

SELECT
  Body
FROM
  PostsWithDeleted
WHERE
  Id IN
    (SELECT
        DISTINCT ph0.PostId
      FROM
        PostHistory ph0
      INNER JOIN
        PostHistory ph1
        ON
          ph0.PostId = ph1.PostId AND
          ph1.PostHistoryTypeId = 12
      WHERE
        ph0.PostHistoryTypeId = 14)

The bodies that outputs just plain aren't spam - certainly not of the kind I'm used to seeing. A sample:

All I can say is that you need to subclass UIView and make it a delegate of UIGestureRecognizerDelegate and UICollectionViewDelegate, then in your UIView subclass, do the following, I can't give out anymore information on this because the code, although owned by myself, is proprietary to the point of probably enraging quite a few organizations that I've used this for, so here's the secret...

I'm looking for an application or a social wall plugin to be added to a project. After looking at Wordpress and finally sifting through all the plugins (maybe all), I have come to a conclusion the plugins are not giving me enough customization options. For example, customizing the registration form. I need to add javascript for a combo box in order to display different options depend

I added a movieclip here, and a number. To get an effect, like a star with a number...

The message is "starCount is not a child of a caller". I don't know...

So, what am I doing wrong that means I'm not selecting deleted, locked posts, and what do I need to do to fix it?

Community
  • 1
  • 1
ArtOfCode
  • 5,702
  • 5
  • 37
  • 56
  • 1
    Please describe the rules for defining spam. Your question is not clear. Also, a self-join seems like an odd choice for this type of query. – Gordon Linoff Jul 31 '16 at 12:53
  • @GordonLinoff Unclear in what way exactly? I'm talking about *spam* as the kind of stuff that we remove on this website for being spam; there's a respository of the stuff [here](https://metasmoke.erwaysoftware.com/search?body=&commit=Search&feedback=true+positive&reason=&site=1&title=&user_rep_direction=%3E%3D&user_reputation=0&username=&utf8=%E2%9C%93&why=). In terms of SQL, spam can be represented by being both deleted and locked. – ArtOfCode Jul 31 '16 at 12:54

1 Answers1

2

Although your query output is exactly the same as mine I believe my attempt gives a clear view of what you're trying to achieve.

If you find this not giving you the desired output, there must be some more logic into finding these posts that you mention.

Below query returns a number of posts that in their history have been marked as both Locked and Deleted at least once.

SELECT COUNT(*)
FROM (
  SELECT
    ph.PostId
  FROM
    PostHistory ph
    INNER JOIN PostHistoryTypes pht ON
      ph.PostHistoryTypeId = pht.id
  WHERE
    pht.Name IN ('Post Locked', 'Post Deleted')
  GROUP BY ph.PostId
  HAVING COUNT(DISTINCT ph.PostHistoryTypeId) >= 2
  ) foo

You've included below message and it seems alright with your query.

Since posts deleted this way are characterized by being both deleted and locked, I'm querying for those attributes by looking at the PostHistory table.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72