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 #Tags (
  Year INT,
  TagId INT,
  Count INT
);

CREATE UNIQUE CLUSTERED INDEX IDX2 ON #Tags([Year], TagId);
CREATE INDEX IDX3 ON #Tags(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
), Raw3 AS (
  SELECT
    Raw2.PostYear,
    Raw2.TagId,
    COUNT(*) AS Count
  FROM Raw2
  GROUP BY Raw2.PostYear, Raw2.TagId
), Tags AS (
  SELECT DISTINCT TagId
  FROM Raw3
), Raw4 AS (
  SELECT
    Years.PostYear,
    Tags.TagId,
    A.Count AS ACount,
    B.Count AS BCount
  FROM Years
  CROSS JOIN Tags
  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
)
SELECT COUNT(*) FROM Raw4;

Enter Parameters

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