Views

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

DECLARE @MOM datetime = '2020-08-01'
DECLARE @YOY datetime = '2019-08-01'
DECLARE @EOM datetime = '2020-09-01'
DECLARE @YTD datetime = '2020-01-01'



DECLARE @UserId int = 158668

SELECT COUNT(DISTINCT A.Id) AS A_MOM
FROM Posts AS A
  INNER JOIN PostTags ON A.ParentId = PostTags.PostId
  INNER JOIN Tags ON PostTags.TagId = Tags.id
WHERE
  A.OwnerUserId = @UserId
  AND (TagName LIKE 'amazon%' OR TagName LIKE 'aws%')
  AND A.CreationDate >= @MOM
  AND A.CreationDate < @EOM;

SELECT COUNT(DISTINCT A.Id) AS A_YTD
FROM Posts AS A
  INNER JOIN PostTags ON A.ParentId = PostTags.PostId
  INNER JOIN Tags ON PostTags.TagId = Tags.id
WHERE
  A.OwnerUserId = @UserId
  AND (TagName LIKE 'amazon%' OR TagName LIKE 'aws%')
  AND A.CreationDate >= @YTD
  AND A.CreationDate < @EOM;
  

SELECT SUM(Q.ViewCount) AS V_MOM FROM (
  SELECT DISTINCT Q.Id, Q.ViewCount
  FROM Posts AS A
    INNER JOIN PostTags ON A.ParentId = PostTags.PostId
    INNER JOIN Posts AS Q ON A.ParentId = Q.Id
    INNER JOIN Tags ON PostTags.TagId = Tags.id
  WHERE
    A.OwnerUserId = @UserId
    AND (TagName LIKE 'amazon%' OR TagName LIKE 'aws%')
    AND A.CreationDate >= @MOM
    AND A.CreationDate < @EOM
) as Q 
  
SELECT SUM(Q.ViewCount) AS V_YOY FROM (
  SELECT DISTINCT Q.Id, Q.ViewCount
  FROM Posts AS A
    INNER JOIN PostTags ON A.ParentId = PostTags.PostId
    INNER JOIN Posts AS Q ON A.ParentId = Q.Id
    INNER JOIN Tags ON PostTags.TagId = Tags.id
  WHERE
    A.OwnerUserId = @UserId
    AND (TagName LIKE 'amazon%' OR TagName LIKE 'aws%')
    AND A.CreationDate >= @YOY
    AND A.CreationDate < @EOM
) as Q 
  
SELECT SUM(Q.ViewCount) AS V_YTD FROM (
  SELECT DISTINCT Q.Id, Q.ViewCount
  FROM Posts AS A
    INNER JOIN PostTags ON A.ParentId = PostTags.PostId
    INNER JOIN Posts AS Q ON A.ParentId = Q.Id
    INNER JOIN Tags ON PostTags.TagId = Tags.id
  WHERE
    A.OwnerUserId = @UserId
    AND (TagName LIKE 'amazon%' OR TagName LIKE 'aws%')
    AND A.CreationDate >= @YTD
    AND A.CreationDate < @EOM
) as Q

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results