1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

DECLARE @LANGA varchar(100) = ##LanguageA:string##
DECLARE @LANGB varchar(100) = ##LanguageB:string##
DECLARE @SMOOTHING int = 100

;WITH ScoreListA AS
(
  SELECT CreationDate as [PostTime],
  Score,
  ROW_NUMBER() OVER (ORDER BY CreationDate) AS rowindex
  FROM Posts
  WHERE PostTypeId = 2
  AND (
    Lower(Body) LIKE '%<h_>' + Lower(@LANGA) + '%'
    OR Lower(Body) LIKE '%' + Lower(@LANGA) + '</a>%</h_>%'
  )
), ScoreListB AS
(
  SELECT CreationDate as [PostTime],
  Score,
  ROW_NUMBER() OVER (ORDER BY CreationDate) AS rowindex
  FROM Posts
  WHERE PostTypeId = 2
  AND (
    Lower(Body) LIKE '%<h_>' + Lower(@LANGB) + '%'
    OR Lower(Body) LIKE '%' + Lower(@LANGB) + '</a>%</h_>%'
  )
)

SELECT PostTime,
AVG(Score) OVER (ORDER BY PostTime ROWS BETWEEN 100 PRECEDING AND 0 FOLLOWING) AS [Lang A],
NULL as [Lang B]
FROM ScoreListA
WHERE rowindex % 30 = 0 AND Score BETWEEN
(SELECT SUM(Score) / COUNT(*) FROM ScoreListA) - 25 AND
(SELECT SUM(Score) / COUNT(*) FROM ScoreListA) + 25

UNION

SELECT PostTime,
NULL as [Lang A],
AVG(Score) OVER (ORDER BY PostTime ROWS BETWEEN 100 PRECEDING AND 0 FOLLOWING) AS [Lang B]
FROM ScoreListB
WHERE rowindex % 30 = 0 AND Score BETWEEN
(SELECT SUM(Score) / COUNT(*) FROM ScoreListB) - 25 AND
(SELECT SUM(Score) / COUNT(*) FROM ScoreListB) + 25
ORDER BY PostTime

fork query

edited sep 26 16 downgoat1234

created jul 15 16 Downgoat