Monthly Accounting Query across years.

MonthlyAccounting.sql

SELECT
  integer(mnthid/100) YEAR,
  integer(mnthid%100) MONTH,
  NewUsers,
  Recurring,
  Resurrected,
  NewUsers+Recurring+Resurrected TOTAL FROM (
  SELECT
    mnthid,
    NewUsers,
    Recurring,
    Resurrected,
    NewUsers+Recurring+Resurrected TOTAL
  FROM (
    SELECT
      mnthid,
      SUM(IF(DIFF IS NULL, uc, 0)) NewUsers,
      SUM(IF(DIFF = 1 or DIFF = 89, uc, 0)) Recurring,
      SUM(IF(DIFF > 1 and DIFF != 89, uc, 0)) Resurrected
    FROM (
      SELECT
        mnthid,
        DIFF,
        EXACT_COUNT_DISTINCT(amplitude_id) uc
      FROM (
        SELECT
          amplitude_id,
          mnthid,
          mnthid-prevWeek AS DIFF
        FROM (
          SELECT
            amplitude_id,
            mnthid,
            LAG(mnthid,1) OVER (PARTITION BY amplitude_id ORDER BY mnthid ) AS prevWeek
          FROM (
            SELECT
              amplitude_id,
              YR*100+weeks mnthid from(
              SELECT
                amplitude_id,
                YEAR(server_upload_time) YR,
                MONTH(server_upload_time) AS WEEKS
              FROM (
                SELECT
                  amplitude_id,
                  server_upload_time
                FROM
                  [project_name:dataset_name.table_name] )
              GROUP BY
                1,
                2,
                3 ))))
      GROUP BY
        mnthid,
        DIFF )
    GROUP BY
      1 ))
ORDER BY
  1, 2