Estimated technology shift compared to the past years

1

Please login or register to vote for this query.

(click on this box to dismiss)

"TechShift" is an estimated percentage of the probability, that you can use a random technology learned in Year1 on the job market of Year2.

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

CREATE TABLE #Years (
  [Year] INT,
  Count INT
);

CREATE UNIQUE CLUSTERED INDEX IDX1 ON #Years([Year]);

INSERT INTO #Years
SELECT DATEPART(yyyy, CreationDate), COUNT(*) AS Count
FROM Posts
WHERE PostTypeId=1
GROUP BY DATEPART(yyyy, CreationDate);

CREATE TABLE #YearTags (
  [Year] INT,
  TagId INT,
  P FLOAT
);

CREATE UNIQUE INDEX IDX2 ON #YearTags([Year], TagId);
CREATE UNIQUE CLUSTERED INDEX IDX3 ON #YearTags(TagId, [Year]);

WITH Raw AS (
  SELECT
    Posts.Id AS PostId,
    MIN(Posts.CreationDate) AS PostCreationDate,
    MAX(Tags.Count) AS MaxTagCount
  FROM Posts, PostTags, Tags
  WHERE
    Posts.Id = PostTags.PostId
    AND
    PostTags.TagId = Tags.Id
  GROUP BY Posts.Id
), Raw2 AS (
  SELECT
    Raw.PostId,
    DATEPART(yyyy, MIN(Raw.PostCreationDate)) AS PostYear,
    MIN(Tags.Id) AS TagId
  FROM Raw, PostTags, Tags
  WHERE
    Raw.PostId = PostTags.PostId
    AND
    PostTags.TagId = Tags.Id
    AND
    Raw.MaxTagCount = Tags.Count
  GROUP BY Raw.PostId
)
INSERT INTO #YearTags
SELECT
  Raw2.PostYear,
  Raw2.TagId,
  1.0 * COUNT(*) / MIN(#Years.Count) AS P
FROM Raw2, #Years
WHERE Raw2.PostYear = #Years.Year
GROUP BY Raw2.PostYear, Raw2.TagId;

WITH Raw AS (
  SELECT
    A.Year AS Year1,
    B.Year AS Year2,
    100.0 - 100.0 * SUM(ABS(COALESCE(A.P,0)-COALESCE(B.P,0))) AS TechShift
  FROM #YearTags A
  FULL OUTER JOIN #YearTags B ON
    A.Year < B.Year
    AND
    A.TagId = B.TagId
  GROUP BY A.Year, B.Year
)
SELECT Year1, CAST(Year2 AS VARCHAR(MAX)) AS Year2, TechShift FROM Raw
WHERE Year1 IS NOT NULL AND Year2 IS NOT NULL
ORDER BY Year2, Year1;

Enter Parameters

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