How many questions got their eventually-accepted answer by fib(hours)?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

-- 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
), 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,
    Posts.CreationDate AS AcceptedAnswerCreation
  FROM
    gcp_questions
    LEFT JOIN Posts
      ON gcp_questions.AcceptedAnswerId = Posts.Id
), time_buckets AS (
  SELECT
    hour,
    LAG(hour) OVER (ORDER BY hour) AS prior_hour
  FROM
    fibonacci
)
SELECT
  hour,
  COUNT(*)
FROM
  q_accepted_answers, time_buckets
WHERE  
  DATEDIFF(hour, QuestionCreation, AcceptedAnswerCreation) <= hour
  AND
  DATEDIFF(hour, QuestionCreation, AcceptedAnswerCreation) > prior_hour
GROUP BY ROLLUP(hour)

;

Enter Parameters

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