WITH Raw AS ( SELECT DATEFROMPARTS( DATEPART(yyyy, Creati...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Patents Meta

Q&A about the site for people interested in improving and participating in the patent system

WITH Raw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
      1) AS Month,
    Id,
    CreationDate
  FROM Users
), Raw2 AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY Month ORDER BY Id) AS MonthIdRank
  FROM Raw
), Raw3 AS (
  SELECT Month, COUNT(*) AS Count
  FROM Raw2
  GROUP BY Month
)
SELECT
  Raw3.*, Raw2.Id AS MedianId
FROM Raw2, Raw3
WHERE
  Raw2.Month = Raw3.Month
  AND
  Raw2.MonthIdRank = Raw3.Count / 2

Enter Parameters

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