How many questions had accepted their answer by fib(hours)?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Get a sequence into the query. I'm not proud of this.
WITH trailing_hours AS (
  SELECT 0 AS hour
  UNION ALL
  SELECT 1 AS hour
  UNION ALL
  SELECT 2 AS hour
  UNION ALL
  SELECT 3 AS hour
  UNION ALL
  SELECT 4 AS hour
  UNION ALL
  SELECT 5 AS hour
  UNION ALL
  SELECT 6 AS hour
  UNION ALL
  SELECT 7 AS hour
  UNION ALL
  SELECT 8 AS hour
  UNION ALL
  SELECT 9 AS hour
  UNION ALL
  SELECT 10 AS hour
  UNION ALL
  SELECT 11 AS hour
  UNION ALL
  SELECT 12 AS hour
  UNION ALL
  SELECT 13 AS hour
  UNION ALL
  SELECT 14 AS hour
  UNION ALL
  SELECT 15 AS hour
  UNION ALL
  SELECT 16 AS hour
  UNION ALL
  SELECT 17 AS hour
  UNION ALL
  SELECT 18 AS hour
  UNION ALL
  SELECT 19 AS hour
  UNION ALL
  SELECT 20 AS hour
  UNION ALL
  SELECT 21 AS hour
  UNION ALL
  SELECT 22 AS hour
  UNION ALL
  SELECT 23 AS hour
  UNION ALL
  SELECT 24 AS hour
  UNION ALL
  SELECT 100000 AS hour
), fibonacci AS (  -- get a fibonacci sequence in too. Still not proud
  SELECT 0 AS hour
  UNION ALL
  SELECT 1 AS hour
  UNION ALL
  SELECT 2 AS hour
  UNION ALL
  SELECT 3 AS hour
  UNION ALL
  SELECT 5 AS hour
  UNION ALL
  SELECT 8 AS hour
  UNION ALL
  SELECT 13 AS hour
  UNION ALL
  SELECT 21 AS hour
  UNION ALL
  SELECT 34 AS hour
  UNION ALL
  SELECT 55 AS hour
  UNION ALL
  SELECT 89 AS hour
  UNION ALL
  SELECT 144 AS hour
  UNION ALL
  SELECT 233 AS hour
  UNION ALL
  SELECT 377 AS hour
  UNION ALL
  SELECT 610 AS hour
  UNION ALL
  SELECT 987 AS hour
  UNION ALL
  SELECT 1597 AS hour
  UNION ALL
  SELECT 2584 AS hour
  UNION ALL
  SELECT 4181 AS hour
  UNION ALL
  SELECT 6765 AS hour
  UNION ALL
  SELECT 10946 AS hour  -- we stop here as this is the hours in 456 days
),

gcp_questions AS (
  SELECT
    Posts.Id AS QuestionId,
    Posts.CreationDate AS QuestionCreation,
    Posts.AcceptedAnswerId AS AcceptedAnswerId
  FROM
    Posts,
    PostTags,
    Tags
  WHERE
    Posts.CreationDate BETWEEN
      CAST('2016-05-01' AS datetime) AND CAST('2017-05-01' AS datetime)
    AND
    Posts.Id = PostTags.PostId
    AND
    PostTags.TagId = Tags.Id
    AND
    TagName in (
      'app-engine-flexible',
      'appengine-gradle-plugin',
      'gae-datastore',
      'gcloud',
      'gcloud-intellij',
      'gcloud-java',
      'gcloud-node',
      'gcloud-python',
      'gcsfuse',
      'gcutil',
      'google-app-engine',
      'google-bigquery',
      'google-cloud-bigtable',
      'google-cloud-dataflow',
      'google-cloud-datalab',
      'google-cloud-dataproc',
      'google-cloud-datastore',
      'google-cloud-dns',
      'google-cloud-dotnet',
      'google-cloud-eclipse',
      'google-cloud-endpoints',
      'google-cloud-intellij',
      'google-cloud-logging',
      'google-cloud-monitoring',
      'google-cloud-platform',
      'google-cloud-powershell',
      'google-cloud-pubsub',
      'google-cloud-sdk',
      'google-cloud-source-repos',
      'google-cloud-spaner',
      'google-cloud-sql',
      'google-cloud-storage',
      'google-cloud-trace',
      'google-cloud-visualstudio',
      'google-compute-engine',
      'google-container-engine',
      'google-container-os',
      'google-hadoop',
      'google-plugin-eclipse',
      'google-prediction',
      'google-translate',
      'gsutil',
      'kubernetes',
      'stackdriver')
), q_accepted_answers AS (
  SELECT
    QuestionId,
    QuestionCreation,
    gcp_questions.AcceptedAnswerId AS AcceptedAnswerId,
    Posts.CreationDate AS AcceptedAnswerCreation
  FROM
    gcp_questions
    LEFT JOIN Posts
      ON gcp_questions.AcceptedAnswerId = Posts.Id
), q_acceptance_times AS (
SELECT
  QuestionId,
  QuestionCreation,
  AcceptedAnswerId,
  AcceptedAnswerCreation,
  Votes.CreationDate AS AcceptanceTime
FROM
  q_accepted_answers, Votes
WHERE
  q_accepted_answers.AcceptedAnswerId = Votes.PostId
  AND
  Votes.VoteTypeId = 1
),
time_buckets AS (
  SELECT
    hour,
    LAG(hour) OVER (ORDER BY hour) AS prior_hour
  FROM
    trailing_hours
), bucketed_acceptance AS (
SELECT
  hour,
  COUNT(*) AS this_bucket
FROM
  q_acceptance_times, time_buckets
WHERE  
  DATEDIFF(hour, QuestionCreation, AcceptanceTime) <= hour
  AND
  DATEDIFF(hour, QuestionCreation, AcceptanceTime) > prior_hour
GROUP BY ROLLUP(hour)
)
SELECT 
  hour,
  this_bucket,
  SUM(this_bucket) OVER (ORDER BY hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
  bucketed_acceptance
WHERE hour IS NOT NULL
;

Enter Parameters

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