0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @PeriodStart DATE = '2022-12-01';
DECLARE @PeriodEnd DATE = '2022-12-31';

-- Query for active users with simplified expertise levels and sampling
SELECT
  DATEFROMPARTS(YEAR(p.CreationDate), MONTH(p.CreationDate), 1) AS ActivityMonth,
  CASE
    WHEN u.Reputation < 1000 THEN 'Beginner'
    WHEN u.Reputation BETWEEN 1000 AND 9999 THEN 'Intermediate'
    ELSE 'Expert'
  END AS ExpertiseLevel,
  COUNT(DISTINCT p.OwnerUserId) * 10 AS EstimatedActiveUsers  -- Multiply by 10 to estimate total
FROM Posts p
JOIN Users u ON p.OwnerUserId = u.Id
WHERE p.CreationDate BETWEEN @PeriodStart AND @PeriodEnd
  AND p.OwnerUserId IS NOT NULL
  AND p.Id % 10 = 0  -- Sample approximately 10% of posts
GROUP BY
  DATEFROMPARTS(YEAR(p.CreationDate), MONTH(p.CreationDate), 1),
  CASE
    WHEN u.Reputation < 1000 THEN 'Beginner'
    WHEN u.Reputation BETWEEN 1000 AND 9999 THEN 'Intermediate'
    ELSE 'Expert'
  END
ORDER BY ActivityMonth, ExpertiseLevel;

fork query

created oct 17 jon.doe32071