Drive-by idents

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

WITH
all_posts AS
(
  SELECT
    p.id,
    p.owneruserid AS userid,
    CASE
      WHEN p.tags LIKE '%ident%'
      THEN 1
      ELSE 0
    END AS identify,
    DATEPART(YEAR, creationdate) AS yy,
    DATEPART(QUARTER, creationdate) AS qq
  FROM posts p
  WHERE p.posttypeid = 1
),
singlequestionusers AS
(
  SELECT
    u.id AS userid,
    p.identify,
    p.yy,
    p.qq
  FROM all_posts p
  JOIN users u ON (p.userid = u.id)
  GROUP BY
    u.id,
    p.identify,
    p.yy,
    p.qq
  HAVING COUNT(*) = 1
)
SELECT
  COUNT(*) AS posts,
  SUM(sq.identify) AS indentification,
  SUM(1-sq.identify) AS other,
  CONCAT(
    yy,
    CASE GROUPING(qq)
      WHEN 1 THEN ''
      ELSE CONCAT(' Q', qq)
    END) AS q
FROM singlequestionusers sq
GROUP BY CUBE (
  yy,
  qq
)
HAVING
  GROUPING(qq) = 1
  OR
  GROUPING(qq) + GROUPING(yy) = 0
ORDER BY
  GROUPING(yy),
  yy,
  GROUPING(qq),
  qq

Enter Parameters

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