Number of unique users closing questions

1

Please login or register to vote for this query.

(click on this box to dismiss)

Interpersonal Skills

Q&A for people looking to improve their interpersonal communication skills

-- at: Action Type "10 - Close, 11 - Reopen, 12 - Delete, 13 - Undelete"
-- sd: Start date "yyyy-mm-dd format"
-- ed: End date "yyyy-mm-dd format"

DECLARE @atype INT = ##at:int##
DECLARE @sdate NVARCHAR(10) = ##sd:string##
DECLARE @edate NVARCHAR(10) = ##ed:string##
DECLARE @startdate DATE = CAST(@sdate as date)
DECLARE @enddate DATE = CAST(@edate as date)

SELECT
  ph.PostId, ph.CreationDate, voter_id INTO #voter_ids
FROM PostHistory ph
CROSS APPLY OPENJSON (ph.Text)
  WITH (
    voters NVARCHAR(MAX) '$.Voters' AS JSON
  )
  OUTER APPLY OPENJSON(voters)
  WITH (voter_id NVARCHAR(20) '$.Id')
WHERE ph.PostHistoryTypeId = @atype -- Close 
  AND ph.CreationDate >= @startdate AND ph.CreationDate < @enddate

SELECT 
  COUNT(DISTINCT voter_id) [Num Voters] 
FROM
  #voter_ids
WHERE
  voter_id != -1 -- Don't count the deleted user

Enter Parameters

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