Resolved: How to create a column that shows if a date appears in the last 7 days in BQ?

SELECT
  user_id,
  MIN(active_date) as first_action_date,
  MAX(active_date) as last_action_date,


FROM activity_dates
GROUP BY 1

WITH activity_dates AS (<br />
  SELECT "1" as user_id, DATE("2022-06-16") as active_date<br />
  UNION ALL<br />
  SELECT "2" as user_id, "2022-06-02" as active_date<br />
  UNION ALL<br />
  SELECT "1" as user_id, "2022-06-14" as active_date<br />
  UNION ALL<br />
  SELECT "1" as user_id, "2022-05-01" as active_date<br />
)<br />
SELECT<br />
  user_id,<br />
  MIN(active_date) as first_action_date,<br />
  MAX(active_date) as last_action_date,<br />
  MAX(IF(DATE_DIFF(CURRENT_DATE(), active_date, DAY) <= 7, active_date, NULL)) IS NOT NULL as active_last_7_days,
  MAX(IF(DATE_DIFF(CURRENT_DATE(), active_date, DAY) BETWEEN 8 AND 14, active_date, NULL)) IS NOT NULL as active_8_14_ago,
  MAX(IF(DATE_DIFF(CURRENT_DATE(), active_date, DAY) BETWEEN 15 AND 21, active_date, NULL)) IS NOT NULL as active_15_21_ago,
FROM activity_dates
GROUP BY user_id
[/code]

</p>



Output:<br> 


<p>[code]user_id first_action_date   last_action_date    active_last_7_days  active_8_14_ago active_15_21_ago<br />
1       2022-05-01          2022-06-16          true                false           false<br />
2       2022-06-02          2022-06-02          false               true            false