0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Define the two periods
DECLARE @Period1Start DATE = '2020-11-29';
DECLARE @Period1End DATE = '2022-11-29';
DECLARE @Period2Start DATE = '2022-11-30';
DECLARE @Period2End DATE = '2024-10-14';

-- Use a CTE to get monthly active users for both periods
WITH MonthlyActiveUsers AS (
  SELECT
    CASE
      WHEN CreationDate BETWEEN @Period1Start AND @Period1End THEN 1
      WHEN CreationDate BETWEEN @Period2Start AND @Period2End THEN 2
    END AS PeriodNumber,
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1) AS MonthStart,
    COUNT(DISTINCT OwnerUserId) AS ActiveUsers
  FROM Posts
  WHERE CreationDate BETWEEN @Period1Start AND @Period2End
    AND OwnerUserId IS NOT NULL
  GROUP BY
    CASE
      WHEN CreationDate BETWEEN @Period1Start AND @Period1End THEN 1
      WHEN CreationDate BETWEEN @Period2Start AND @Period2End THEN 2
    END,
    DATEFROMPARTS(YEAR(CreationDate), MONTH(CreationDate), 1)
)

-- Final select with row numbers for side-by-side comparison
SELECT
  PeriodNumber,
  MonthStart,
  ActiveUsers,
  ROW_NUMBER() OVER (PARTITION BY PeriodNumber ORDER BY MonthStart) AS MonthNumber
FROM MonthlyActiveUsers
ORDER BY PeriodNumber, MonthStart

fork query

created oct 15 jon.doe32071