DATE_SUB

1
SELECT
2
  CURRENT_DATE() as today,
3
  DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) as week_ago,
4
  DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) as month_ago;

1
SELECT *
2
FROM `project.dataset.orders`
3
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

1
SELECT
2
  DATE_TRUNC(order_date, MONTH) as month,
3
  SUM(amount) as this_year,
4
  SUM(CASE
5
    WHEN order_date >= DATE_SUB(DATE_TRUNC(order_date, MONTH), INTERVAL 1 YEAR)
6
    THEN amount
7
  END) as last_year
8
FROM `project.dataset.orders`
9
GROUP BY month;