1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for developers and end users of the Sitecore CMS and multichannel marketing software

WITH Raw AS (
  SELECT OwnerUserId AS UserId
  FROM Posts
  WHERE
    CreationDate > DATEADD(d, -60, CURRENT_TIMESTAMP)
    AND
    OwnerUserId > 0

  UNION

  SELECT UserId
  FROM Comments
  WHERE
    CreationDate > DATEADD(d, -60, CURRENT_TIMESTAMP)
    AND
    UserId > 0
),
Raw2 AS (
  SELECT UserId
  FROM Raw
  GROUP BY UserId
),
Raw3 AS (
  SELECT
    SUM(Users.Reputation) AS AllRep,
    SUM(
      CASE
        WHEN LOWER(Users.DisplayName) LIKE '%monica%'
        THEN Users.Reputation
        ELSE 0
      END) AS MonicaRep
  FROM Users, Raw2
  WHERE Raw2.UserId = Users.Id
)
SELECT AllRep, MonicaRep, 100.0 * MonicaRep / AllRep AS MonicaPct FROM Raw3;

fork query

edited nov 23 19

created nov 23 19 peterh - PLEASE DELETE THIS ACCOUNT