New Users with more than 1 Post per Month in regard to id-request blacklisting

0

Please login or register to vote for this query.

(click on this box to dismiss)

Anime and Manga

Q&A for anime and manga fans

DECLARE @PAST12 DATETIME
DECLARE @PAST11 DATETIME
DECLARE @PAST10 DATETIME
DECLARE @PAST9 DATETIME
DECLARE @PAST8 DATETIME
DECLARE @PAST7 DATETIME
DECLARE @PAST6 DATETIME
DECLARE @PAST5 DATETIME
DECLARE @PAST4 DATETIME
DECLARE @PAST3 DATETIME
DECLARE @PAST2 DATETIME
DECLARE @PAST1 DATETIME
DECLARE @FUTURE1 DATETIME
DECLARE @FUTURE2 DATETIME
DECLARE @FUTURE3 DATETIME

SET @PAST12 = CONVERT(DATETIME, '2015-05-24 00:00:00')
SET @PAST11 = CONVERT(DATETIME, '2015-06-24 00:00:00')
SET @PAST10 = CONVERT(DATETIME, '2015-07-24 00:00:00')
SET @PAST9 = CONVERT(DATETIME, '2015-08-24 00:00:00')
SET @PAST8 = CONVERT(DATETIME, '2015-09-24 00:00:00')
SET @PAST7 = CONVERT(DATETIME, '2015-10-24 00:00:00')
SET @PAST6 = CONVERT(DATETIME, '2015-11-24 00:00:00')
SET @PAST5 = CONVERT(DATETIME, '2015-12-24 00:00:00')
SET @PAST4 = CONVERT(DATETIME, '2016-01-24 00:00:00')
SET @PAST3 = CONVERT(DATETIME, '2016-02-24 00:00:00')
SET @PAST2 = CONVERT(DATETIME, '2016-03-24 00:00:00')
SET @PAST1 = CONVERT(DATETIME, '2016-04-24 00:00:00')
SET @FUTURE1 = CONVERT(DATETIME, '2016-05-24 00:00:00')
SET @FUTURE2 = CONVERT(DATETIME, '2016-06-24 00:00:00')
SET @FUTURE3 = CONVERT(DATETIME, '2016-07-24 00:00:00')

SELECT '12 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST12
  AND CreationDate < @PAST11
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST12 
    AND Users.CreationDate < @PAST11
    AND Posts.CreationDate < @PAST11
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '11 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST11
  AND CreationDate < @PAST10
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST11 AND Users.CreationDate < @PAST10
    AND Posts.CreationDate < @PAST10
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '10 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST10
  AND CreationDate < @PAST9
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST10 AND Users.CreationDate < @PAST9
    AND Posts.CreationDate < @PAST9
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '9 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST9
  AND CreationDate < @PAST8
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST9 AND Users.CreationDate < @PAST8
    AND Posts.CreationDate < @PAST8
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '8 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST8
  AND CreationDate < @PAST7
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST8 AND Users.CreationDate < @PAST7
    AND Posts.CreationDate < @PAST7
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '7 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST7
  AND CreationDate < @PAST6
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST7 AND Users.CreationDate < @PAST6
    AND Posts.CreationDate < @PAST6
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '6 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST6
  AND CreationDate < @PAST5
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST6 AND Users.CreationDate < @PAST5
    AND Posts.CreationDate < @PAST5
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '5 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST5
  AND CreationDate < @PAST4
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST5 AND Users.CreationDate < @PAST4
    AND Posts.CreationDate < @PAST4
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '4 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST4
  AND CreationDate < @PAST3
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST4 AND Users.CreationDate < @PAST3
    AND Posts.CreationDate < @PAST3
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '3 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST3
  AND CreationDate < @PAST2
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST3 AND Users.CreationDate < @PAST2
    AND Posts.CreationDate < @PAST2
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '2 Months before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST2
  AND CreationDate < @PAST1
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST2 AND Users.CreationDate < @PAST1
    AND Posts.CreationDate < @PAST1
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '1 Month before', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @PAST1
  AND CreationDate < @FUTURE1
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @PAST1 AND Users.CreationDate < @FUTURE1
    AND Posts.CreationDate < @FUTURE1
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '1 Month after', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @FUTURE1
  AND CreationDate < @FUTURE2
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @FUTURE1 AND Users.CreationDate < @FUTURE2
    AND Posts.CreationDate < @FUTURE2
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1
UNION
SELECT '2 Months after', a.q as 'ALL', b.w as 'More than 1 Post', 1.0 * b.w / a.q * 100 as 'Percentage' FROM
(
  SELECT COUNT(*) as q
  FROM Users
  WHERE CreationDate > @FUTURE2
  AND CreationDate < @FUTURE3
) a
JOIN
(
  SELECT COUNT(*) as w FROM 
  (
    SELECT Users.id, COUNT(*) as c 
    FROM Users 
    LEFT JOIN Posts on Users.id=Posts.OwnerUserId 
    WHERE Users.CreationDate > @FUTURE2 AND Users.CreationDate < @FUTURE3
    AND Posts.CreationDate < @FUTURE3
    Group by Users.id 
    Having count(*)>1
  ) i
) b
on 1=1

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results