Bounties started by weekday, average amount, average awarded amount

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

-- Weekday: depends on @@DATEFIRST
-- SELECT @@DATEFIRST;
-- By default first day of the week aka @@DATEFIRST == Sunday (7)
-- DATEPART(weekday,date) returns an integer, 1 to 7, that depends
-- on @@DATEFIRST; by default (i.e. @@DATEFIRST == 7)
-- => 1 == Sunday, 2 == Monday, ...
WITH sq AS (
  -- Bounties closed by an answer
  SELECT
      pq.Id           AS question_id
    , vs.VoteTypeId   AS question_vote_type_id
    , vs.CreationDate AS bounty_start_date
    , vs.BountyAmount AS question_bounty_amount
      -- Number of answers posted in the bounty period
    , ( SELECT
            COUNT(1)
        FROM
            Posts aw
        WHERE
            aw.PostTypeId = 2
        AND aw.ParentId = pq.Id
        AND aw.CreationDate >= vs.CreationDate
        AND aw.CreationDate <= av.CreationDate
      )               AS bounty_period_answers
    , av.Id           AS closing_id
    , av.ParentId     AS answer_parent_id
    , av.VoteTypeId   AS answer_vote_type_id
    , av.CreationDate AS bounty_close_date
    , av.BountyAmount AS answer_bounty_amount
  FROM
      Posts pq
  INNER JOIN
      Votes vs
  ON
      vs.PostId = pq.Id
  INNER JOIN
    ( SELECT
          pa.Id
        , pa.ParentId
        , vc.VoteTypeId
        , vc.CreationDate
        , vc.BountyAmount
      FROM
          Posts pa
      INNER JOIN
          Votes vc
      ON
          vc.PostId = pa.Id
      WHERE
          pa.PostTypeId = 2
      AND vc.VoteTypeId = 9
    ) av
  ON
      av.ParentId = pq.Id
  AND av.CreationDate >= vs.CreationDate
  AND av.CreationDate <= DATEADD(day,8,vs.CreationDate)
  WHERE
      pq.PostTypeId = 1
  AND vs.VoteTypeId = 8
  UNION ALL
  -- Bounties not closed by an answer (the closing vote is on the question)
  SELECT
      pq.Id           AS question_id
    , vs.VoteTypeId   AS question_vote_type_id
    , vs.CreationDate AS bounty_start_date
    , vs.BountyAmount AS question_bounty_amount
      -- Number of answers posted in the bounty period
    , ( SELECT
            COUNT(1)
        FROM
            Posts aw
        WHERE
            aw.PostTypeId = 2
        AND aw.ParentId = pq.Id
        AND aw.CreationDate >= vs.CreationDate
        AND aw.CreationDate <= vc.CreationDate
      )               AS bounty_period_answers
    , pq.Id           AS closing_id
    , pq.ParentId     AS answer_parent_id
    , vc.VoteTypeId   AS answer_vote_type_id
    , vc.CreationDate AS bounty_close_date
    , vc.BountyAmount AS answer_bounty_amount
  FROM
      Posts pq
  INNER JOIN
      Votes vs
  ON
      vs.PostId = pq.Id
  INNER JOIN
      Votes vc
  ON
      vc.PostId = pq.Id
  AND vc.CreationDate >= vs.CreationDate
  AND vc.CreationDate <= DATEADD(day,8,vs.CreationDate)
  WHERE
      pq.PostTypeId = 1
  AND vs.VoteTypeId = 8
  AND vc.VoteTypeId = 9
  UNION ALL
  -- Bounties without a closing vote (exceptions?)
  SELECT
      pq.Id           AS question_id
    , vs.VoteTypeId   AS question_vote_type_id
    , vs.CreationDate AS bounty_start_date
    , vs.BountyAmount AS question_bounty_amount
      -- Number of answers posted in the bounty period
    , ( SELECT
            COUNT(1)
        FROM
            Posts aw
        WHERE
            aw.PostTypeId = 2
        AND aw.ParentId = pq.Id
        AND aw.CreationDate >= vs.CreationDate
        AND aw.CreationDate <= DATEADD(day,8,vs.CreationDate)
      )               AS bounty_period_answers
    , NULL            AS closing_id
    , NULL            AS answer_parent_id
    , NULL            AS answer_vote_type_id
    , NULL            AS bounty_close_date
    , NULL            AS answer_bounty_amount
  FROM
      Posts pq
  INNER JOIN
      Votes vs
  ON
      vs.PostId = pq.Id
  WHERE
      pq.PostTypeId = 1
  AND vs.VoteTypeId = 8
  AND NOT EXISTS
    ( SELECT
          1
      FROM
          Posts pa
      INNER JOIN
          Votes vc
      ON
          vc.PostId = pa.Id
      WHERE
          pa.PostTypeId = 2
      AND vc.VoteTypeId = 9
      AND pa.ParentId = pq.Id
      UNION
      SELECT
          1
      FROM
          Votes vc
      WHERE
          vc.PostId = pq.Id
      AND vc.VoteTypeId = 9
    )
)
/*
-- Detailed view
SELECT
    sq.question_id
  , sq.question_id AS [Post Link]
  , sq.question_vote_type_id
  , sq.bounty_start_date
  , sq.question_bounty_amount
  , sq.bounty_period_answers
  , sq.closing_id
  , sq.answer_parent_id
  , sq.answer_vote_type_id
  , sq.bounty_close_date
  , sq.answer_bounty_amount
FROM
    sq
--WHERE
--    sq.question_bounty_amount > 350
ORDER BY
    sq.question_id
  , sq.bounty_start_date
  , sq.bounty_close_date
  , sq.closing_id
*/
-- Summary view
SELECT
    DATEPART(weekday,sq.bounty_start_date) AS start_weekday
--    sq.question_bounty_amount
  , COUNT(sq.question_id) AS started_bounties
  , CONVERT(
        decimal(10,2)
      , CONVERT(
            decimal(10,5)
          , SUM(bounty_period_answers)
        ) / CONVERT(
            decimal(10,5)
          , COUNT(sq.question_id)
        )
    ) AS avg_bounty_answers
  , CONVERT(
        decimal(10,2)
      , CONVERT(
            decimal(10,5)
          , SUM(sq.question_bounty_amount)
        ) / CONVERT(
            decimal(10,5)
          , COUNT(sq.question_id)
        )
    ) AS avg_bounty_amount
  , COUNT(sq.answer_parent_id) AS awarded_bounties
  , CONVERT(
        decimal(10,2)
      , CONVERT(
            decimal(10,5)
          , SUM(sq.answer_bounty_amount)
        ) / CONVERT(
            decimal(10,5)
          , COUNT(sq.answer_parent_id)
        )
    ) AS avg_awarded_amount
  , CONVERT(
        decimal(10,2)
      , CONVERT(
            decimal(10,5)
          , COUNT(sq.answer_parent_id)
        ) / CONVERT(
            decimal(10,5)
          , COUNT(sq.question_id)
        ) * 100
    ) AS awarded_pct
FROM
    sq
GROUP BY
    DATEPART(weekday,sq.bounty_start_date)
--    sq.question_bounty_amount
ORDER BY
    DATEPART(weekday,sq.bounty_start_date)
--    sq.question_bounty_amount
;

Enter Parameters

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