WITH
  t_account_created AS (
    SELECT
      experiment.subject_id AS subject_id,
      timestamp AS account_created_timestamp -- when the account was created
    FROM
      wmf_experiments.experiment_event_v1
    WHERE
      timestamp BETWEEN from_iso8601_timestamp('{{ startDateISO }}') AND from_iso8601_timestamp('{{ endDateISO }}')
      AND experiment.enrolled LIKE '{{ experimentId }}'
      AND action = 'account_created'
      AND NOT performer.is_temp -- permanent accounts only
  ),
  t_edits_saved AS (
    -- Note: Article edits only!
    SELECT
      experiment.subject_id AS subject_id,
      timestamp AS edit_saved_timestamp, -- when the edit was saved
      mediawiki.database AS wiki_id,
      page.revision_id AS rev_id
    FROM
      wmf_experiments.experiment_event_v1
    WHERE
      timestamp BETWEEN from_iso8601_timestamp('{{ startDateISO }}') AND from_iso8601_timestamp('{{ endDateISO }}')
      AND experiment.enrolled LIKE '{{ experimentId }}'
      AND action = 'edit_saved'
      AND page.namespace_id = 0
  ),
  t_edits_filtered AS (
    SELECT
      tac.subject_id AS subject_id,
      tes.edit_saved_timestamp,
      tes.wiki_id,
      tes.rev_id,
      COALESCE(
        -- (article) edit was saved AFTER account was created
        (
          tes.edit_saved_timestamp > tac.account_created_timestamp
        )
        -- but DURING the first 24 hours since the account was created
        AND (
          tes.edit_saved_timestamp < tac.account_created_timestamp + INTERVAL '24' HOUR
        ),
        false -- if no (article) edit was saved at all
      ) AS edit_saved_in_first_24h
    FROM
      t_account_created tac
      LEFT JOIN t_edits_saved tes ON tac.subject_id = tes.subject_id
  ),
  t_edits_filtered_status AS (
    SELECT
      t_edits_filtered.subject_id,
      t_edits_filtered.edit_saved_timestamp,
      COALESCE(
        (NOT mwhi.revision_is_identity_reverted)
        OR (
          mwhi.revision_seconds_to_identity_revert > (48 * 3600)
        ),
        false
      ) AS is_constructive_24h
    FROM
      t_edits_filtered
      LEFT JOIN wmf_mediawiki.mediawiki_history_incremental_v1 mwhi ON (
        mwhi.event_timestamp BETWEEN from_iso8601_timestamp('{{ startDateISO }}') AND from_iso8601_timestamp('{{ endDateISO }}')
        AND mwhi.event_entity = 'revision'
        AND t_edits_filtered.wiki_id = mwhi.wiki_id
        AND t_edits_filtered.rev_id = mwhi.revision_id
      )
    WHERE
      t_edits_filtered.edit_saved_in_first_24h
  )
SELECT
  tac.subject_id,
  tac.account_created_timestamp AS timestamp,
  SUM(IF(is_constructive_24h, 1, 0)) > 0 AS is_constructively_activated_24h
FROM
  t_account_created tac
  LEFT JOIN t_edits_filtered_status tefs ON (tac.subject_id = tefs.subject_id)
GROUP BY
  1, -- subject_id
  2 -- timestamp (of account_created event)