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,
  Count INT
);

CREATE UNIQUE CLUSTERED INDEX IDX2 ON #YearTags([Year], TagId);
CREATE UNIQUE 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,
  COUNT(*) AS Count
FROM Raw2
GROUP BY Raw2.PostYear, Raw2.TagId;

CREATE TABLE #TagsRaw (
  TagId INT
);

INSERT INTO #TagsRaw SELECT DISTINCT TagId FROM #YearTags;

/*
SELECT
  #Years.Year,
  #Tags.TagId,
  A.Count AS ACount,
  B.Count AS BCount
FROM #Years
LEFT JOIN #YearsTags ON 
LEFT JOIN Raw3 A ON A.PostYear = #Years.PostYear AND A.TagId = #Tags.TagId
LEFT JOIN Raw3 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