0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

with counts_by_answered_and_range as (
  select
    score_range,
    answered,
    COUNT(*) N
  from (
    select
      CreationDate,
      case 
        when isnull(AnswerCount, 0) > 0 then 'yes'
        else 'no'
      end answered,
      case 
        when isnull(Score, 0) < 0 then 'negative'
        when isnull(Score, 0) = 0 then 'zero'
        when isnull(Score, 0) > 0 then 'positive'
      end score_range
    from PostsWithDeleted
    where PostTypeId = 1 -- Questions
  ) questions_with_score_range
  where CreationDate >= '2015-01-01'
  group by answered, score_range
),
answered_counts as (
  select *
  from counts_by_answered_and_range
  where answered = 'yes'
),
total_by_range as (
  select score_range, sum(N) questions
  from counts_by_answered_and_range
  group by score_range
),
total_answered as (
  select sum(N) questions
  from counts_by_answered_and_range
  where answered = 'yes'
),
total as (
  select sum(N) questions
  from counts_by_answered_and_range
)
select
  answered_counts.score_range,
  answered_counts.N num_answered,
  total_by_range.questions,
  100.0 * answered_counts.N / total_by_range.questions as probability_answered,
  100.0 * answered_counts.N / total_answered.questions as percent_of_answered,
  100.0 * total_by_range.questions / total.questions as percent_of_total
from answered_counts
join total_by_range on total_by_range.score_range = answered_counts.score_range
join total_answered on 1 = 1
join total on 1 = 1

fork query

edited nov 21 17 anonymous

created nov 21 17 Jon "not doe" Ericson