Posts per rep group

3

Please login or register to vote for this query.

(click on this box to dismiss)

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

SELECT 
	x.year,
	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 
	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 
	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 
	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 
	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 
	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

Enter Parameters

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