3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for gardeners and landscapers

SELECT x.week, x.year, x.posts as '0-100', y.posts as '100-1000', z.posts as '1000-5000'
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
ORDER BY x.year desc, x.week desc

view current version

edited oct 29 15

created oct 29 15 Reaces