0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Define a one-month period
DECLARE @PeriodStart DATE = '2022-11-01';
DECLARE @PeriodEnd DATE = '2022-11-30';

-- Query for active users with simplified expertise levels, using 100% of data
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) AS ActiveUsers
FROM Posts p
JOIN Users u ON p.OwnerUserId = u.Id
WHERE p.CreationDate BETWEEN @PeriodStart AND @PeriodEnd
  AND p.OwnerUserId IS NOT NULL
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

edited oct 18

created oct 18 jon.doe32071