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.

Raspberry Pi

Q&A for users and developers of hardware and software for Raspberry Pi

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;

/*
CREATE TABLE #Tags (
  TagId INT
);
CREATE UNIQUE CLUSTERED INDEX IDX4 ON #Tags(TagId);

INSERT INTO #Tags SELECT DISTINCT TagId FROM #YearTags;

INSERT INTO #YearTags
SELECT #Years.Year, #Tags.TagId, 0
FROM #Years
CROSS JOIN #Tags
LEFT JOIN #YearTags
  ON #YearTags.Year = #Years.Year AND #YearTags.TagId = #Tags.TagId
WHERE #YearTags.Year IS NULL;
*/

SELECT
  A.Year AS Year1,
  B.Year AS Year2,
  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
ORDER BY B.Year, A.Year;

--UPDATE #YearTags

/*
SELECT
  #Years.Year,
  #Tags.TagId,
  A.Count AS ACount,
  B.Count AS BCount
FROM #Years
CROSS JOIN #Tags
LEFT JOIN #YearTags A
  ON A.PostYear = #Years.PostYear AND A.TagId = #Tags.TagId
LEFT JOIN #YearTags B
  ON A.PostYear = #Years.PostYear AND B.TagId = #Tags.TagId
WHERE A.PostYear < B.PostYear
GROUP BY

Enter Parameters

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