- Stack Exchange Data Explorer

SELECT '2017-01-01' as date, COUNT(DISTINCT(Posts.OwnerUs...

Please login or register to vote for this query.

(click on this box to dismiss)
プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答
SELECT '2017-01-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-01-01' UNION
SELECT '2017-02-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-02-01' UNION
SELECT '2017-03-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-03-01' UNION
SELECT '2017-04-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-04-01' UNION
SELECT '2017-05-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-05-01' UNION
SELECT '2017-06-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-06-01' UNION
SELECT '2017-07-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-07-01' UNION
SELECT '2017-08-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-08-01' UNION
SELECT '2017-09-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-09-01' UNION
SELECT '2017-10-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-10-01' UNION
SELECT '2017-11-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-11-01' UNION
SELECT '2017-12-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2017-12-01' UNION
SELECT '2018-01-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-01-01' UNION
SELECT '2018-02-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-02-01' UNION
SELECT '2018-03-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-03-01' UNION
SELECT '2018-04-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-04-01' UNION
SELECT '2018-05-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-05-01' UNION
SELECT '2018-06-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-06-01' UNION
SELECT '2018-07-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-07-01' UNION
SELECT '2018-08-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-08-01' UNION
SELECT '2018-09-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-09-01' UNION
SELECT '2018-10-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-10-01' UNION
SELECT '2018-11-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-11-01' UNION
SELECT '2018-12-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2018-12-01' UNION
SELECT '2019-01-01' as date, COUNT(DISTINCT(Posts.OwnerUserId)) FROM Posts LEFT JOIN Votes on Votes.PostId = Posts.Id LEFT JOIN PostTags on PostTags.PostId = Posts.Id LEFT JOIN Tags ON Tags.Id = PostTags.TagId WHERE Tags.TagName IN (  'slack', 'slack-api', 'slack-commands','slack-block-kit',  'slack-dialog')  AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01') ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  >= '2008-10-01' AND CASE WHEN Votes.CreationDate IS NOT NULL THEN CONCAT(YEAR(Votes.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Votes.CreationDate)), 2) , '-01')  ELSE CONCAT(YEAR(Posts.CreationDate), '-', RIGHT('0' + CONVERT(varchar(10), MONTH(Posts.CreationDate)), 2) , '-01') END  < '2019-01-01'
edited mar 2
anonymous
created feb 23
Ben.Wilson
Enter Parameters

Options: Text-only results Include execution plan
Switch to main site
Hold tight while we fetch your results
Results[:current/:total] Messages Graph Execution Plan
Download CSV Download XML
:records returned in :time ms:cached