3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for ebook publishers and readers

-- Post type 1 = Question
-- Post type 2 = Answer
-- Apparently some form of SQL Injection is possible,
-- So if you want both Post type should be:
-- 1 or posttypeid = 2
SELECT 
	x.week, 
	x.posts as '0-100', 
	y.posts as '100-1000', 
	z.posts as '1000-5000', 
	x1.posts as '5000-10000',
	y1.posts as '10000+'
FROM
(SELECT
	datepart(week, creationdate) as 'week',
	year(creationdate) as 'year',
	COUNT(id) as 'posts'
FROM
	posts
WHERE 
    year(creationdate) = ##year## and
    (posttypeid = ##PostType##) and
	OwnerUserId in 
		(select 
			id 
		from 
			users 
		where 
			reputation >= 0 
			and reputation <= 100
		)
GROUP BY datepart(week, creationdate), year(creationdate)
) x
join
(SELECT
	datepart(week, creationdate) as 'week',
	year(creationdate) as 'year',
	COUNT(id) as 'posts'
FROM
	posts
WHERE 
    year(creationdate) = ##year## and
    (posttypeid = ##PostType##) and
	OwnerUserId in 
		(select 
			id 
		from 
			users 
		where 
			reputation >= 101
			and reputation <= 1000
		)
GROUP BY datepart(week, creationdate), year(creationdate)
) y
on x.week = y.week and x.year = y.year
join
(SELECT
	datepart(week, creationdate) as 'week',
	year(creationdate) as 'year',
	COUNT(id) as 'posts'
FROM
	posts
WHERE 
    year(creationdate) = ##year## and
    (posttypeid = ##PostType##) and
	OwnerUserId in 
		(select 
			id 
		from 
			users 
		where 
			reputation >= 1001
			and reputation <= 5000
		)
GROUP BY datepart(week, creationdate), year(creationdate)
) z
on x.week = z.week and x.year = z.year
JOIN
(SELECT
	datepart(week, creationdate) as 'week',
	year(creationdate) as 'year',
	COUNT(id) as 'posts'
FROM
	posts
WHERE 
    year(creationdate) = ##year## and
    (posttypeid = ##PostType##) and
	OwnerUserId in 
		(select 
			id 
		from 
			users 
		where 
			reputation >= 5001
			and reputation <= 10000
		)
GROUP BY datepart(week, creationdate), year(creationdate)
) x1
on x.week = x1.week and x.year = x1.year
JOIN
(SELECT
	datepart(week, creationdate) as 'week',
	year(creationdate) as 'year',
	COUNT(id) as 'posts'
FROM
	posts
WHERE 
    year(creationdate) = ##year## and
    (posttypeid = ##PostType##) and
	OwnerUserId in 
		(select 
			id 
		from 
			users 
		where 
			reputation >= 10001
		)
GROUP BY datepart(week, creationdate), year(creationdate)
) y1
on x.week = y1.week and x.year = y1.year
ORDER BY x.year desc, x.week desc

fork query

edited oct 29 15

created oct 29 15 Reaces