Users by the amount of bounties won on their first week of activities

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

WITH Raw AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    Votes.CreationDate AS VoteDay,
    Votes.Id AS VoteId,
    Votes.PostId,
    Votes.VoteTypeId,
    Votes.BountyAmount
  FROM Posts, Votes
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.OwnerUserId > 0
),
Raw2 AS (
  SELECT UserId, MIN(VoteDay) AS FirstVoteDay
  FROM Raw
  GROUP BY UserId
),
Raw3 AS (
  SELECT
    Raw.UserId,
    Raw.VoteDay,
    Raw.PostId,
    Raw.VoteId,
    Raw.BountyAmount,
    SUM(BountyAmount) OVER (PARTITION BY Raw.UserId) AS StartBounty
  FROM Raw, Raw2
  WHERE
    Raw.UserId = Raw2.UserId
    AND
    DATEDIFF(day, Raw2.FirstVoteDay, Raw.VoteDay) < ##FirstDays:int?7##
    AND
    Raw.VoteTypeId = 9
)
SELECT
  UserId AS [User Link],
  VoteDay,
  PostId AS [Post Link],
  BountyAmount,
  StartBounty
FROM Raw3
ORDER BY StartBounty DESC, UserId, VoteId

Enter Parameters

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