Answer length and score relationship

1

Please login or register to vote for this query.

(click on this box to dismiss)

Space Exploration

Q&A for spacecraft operators, scientists, engineers, and enthusiasts

DECLARE @UserID Int = ##UserID?9449426##

SELECT
  Id,
  Score,
  LEN(Body) Length
INTO #MyPosts
FROM Posts
WHERE OwnerUserId = @UserID
  AND PostTypeId = 2

SELECT
  Id [Post Link],
  Score,
  Length
FROM #MyPosts
ORDER BY Length DESC


-- create range table https://stackoverflow.com/a/8586622/9449426
DECLARE @Min  int = 0
DECLARE @Max  int = 8000
DECLARE @Step int = 250

SELECT @Min + Number * @Step AS Range
INTO  #Ranges
FROM  master..spt_values
WHERE Type = 'P' and
      Number between 0 and (@Max - @Min) / @Step


SELECT
  ar.Range,
  COUNT(r.Range) Length,
  COALESCE(SUM(r.Score), 0) Score
FROM #Ranges ar
LEFT JOIN (
  SELECT
    Score,
    CASE WHEN Length >=    0 AND Length <  250 THEN 250
         WHEN Length >=  250 AND Length <  500 THEN 500
         WHEN Length >=  500 AND Length <  750 THEN 750
         WHEN Length >=  750 AND Length < 1000 THEN 1000
         
         WHEN Length >= 1000 AND Length < 1250 THEN 1250
         WHEN Length >= 1250 AND Length < 1500 THEN 1500
         WHEN Length >= 1500 AND Length < 1750 THEN 1750
         WHEN Length >= 1750 AND Length < 2000 THEN 2000
         
         WHEN Length >= 2000 AND Length < 2250 THEN 2250
         WHEN Length >= 2250 AND Length < 2500 THEN 2500
         WHEN Length >= 2500 AND Length < 2750 THEN 2750
         WHEN Length >= 2750 AND Length < 3000 THEN 3000
         
         WHEN Length >= 3000 AND Length < 3250 THEN 3250
         WHEN Length >= 3250 AND Length < 3500 THEN 3500
         WHEN Length >= 3500 AND Length < 3750 THEN 3750
         WHEN Length >= 3750 AND Length < 4000 THEN 4000
         
         WHEN Length >= 4000 AND Length < 4250 THEN 4250
         WHEN Length >= 4250 AND Length < 4500 THEN 4500
         WHEN Length >= 4500 AND Length < 4750 THEN 4750
         WHEN Length >= 4750 AND Length < 5000 THEN 5000
         
         WHEN Length >= 5000 AND Length < 5250 THEN 5250
         WHEN Length >= 5250 AND Length < 5500 THEN 5500
         WHEN Length >= 5500 AND Length < 5750 THEN 5750
         WHEN Length >= 5750 AND Length < 6000 THEN 6000
         
         WHEN Length >= 6000 AND Length < 6250 THEN 6250
         WHEN Length >= 6250 AND Length < 6500 THEN 6500
         WHEN Length >= 6500 AND Length < 6750 THEN 6750
         WHEN Length >= 6750 AND Length < 7000 THEN 7000
         
         WHEN Length >= 7000 AND Length < 7250 THEN 7250
         WHEN Length >= 7250 AND Length < 7500 THEN 7500
         WHEN Length >= 7500 AND Length < 7750 THEN 7750
         WHEN Length >= 7750 AND Length < 8000 THEN 8000
         ELSE -1
     END AS Range
  FROM #MyPosts
) r ON r.Range = ar.Range
GROUP BY ar.Range
ORDER BY ar.Range DESC

SELECT AVG(Length) Average
FROM #MyPosts

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results