Create column with timeframe relative to other column in SQL

Question

Suppose I have the following table t_1 where every row represents a day:

+------+------------+-------+
| week |    date    | val   |
+------+------------+-------+
|    1 | 2022-02-07 |     1 | <- Monday
|    1 | 2022-02-08 |     2 |
|    1 | 2022-02-09 |     3 |
|    1 | 2022-02-10 |     4 | <- Thursday
|    1 | 2022-02-11 |     5 |
|    1 | 2022-02-12 |     6 |
|    1 | 2022-02-13 |     7 |
|    2 | 2022-02-14 |     8 | <- Monday
|    2 | 2022-02-15 |     9 |
|    2 | 2022-02-16 |    10 |
|    2 | 2022-02-17 |    11 | <- Thursday
|    2 | 2022-02-18 |    12 | 
|    2 | 2022-02-19 |    13 |
|    2 | 2022-02-20 |    14 |
+------+------------+-------+

How can I create the following table t2 from t1?

+------------+------------+-----------+------------+
| date_start |  date_end  | val_cur.  | val_prev   |
+------------+------------+-----------+------------+
| 2022-01-14 | 2022-01-17 |        38 |         10 |
+------------+------------+-----------+------------+

Here val_cur is defined as the sum of values of the current timeframe (i.e. the sum of values between date_start and date_end) and val_prev is defined as the sum of values of the previous timeframe (i.e. the current timeframe minus one week).

-- Bigquery Standard SQL
WITH t_1 AS
  (SELECT 1 AS week, '2022-02-07' AS date, 1 AS val UNION ALL
    SELECT 1, '2022-02-08', 2 UNION ALL
    SELECT 1, '2022-02-09', 3 UNION ALL
    SELECT 1, '2022-02-10', 4 UNION ALL
    SELECT 1, '2022-02-11', 5 UNION ALL
    SELECT 1, '2022-02-12', 6 UNION ALL
    SELECT 1, '2022-02-13', 7 UNION ALL
    SELECT 2, '2022-02-14', 8 UNION ALL
    SELECT 2, '2022-02-15', 9 UNION ALL
    SELECT 2, '2022-02-16', 10 UNION ALL
    SELECT 2, '2022-02-17', 11 UNION ALL
    SELECT 2, '2022-02-18', 12 UNION ALL
    SELECT 2, '2022-02-19', 13 UNION ALL
    SELECT 2, '2022-02-20', 14)
SELECT '2022-02-14' AS date_start, '2022-02-17' AS date_stop, sum(val) AS val_cur
FROM t_1
WHERE date >= '2022-02-14' AND date <= '2022-02-17'

Output:

+-----+------------+------------+---------+
| Row | date_start | date_stop  | val_cur |
+-----+------------+------------+---------+
|   1 | 2022-02-14 | 2022-02-17 |      38 |
+-----+------------+------------+---------+

But how do I get the last column?

Answer 1

Consider below approach

with your_table as (
  select 1 as week, date '2022-02-07' as date, 1 as val union all
  select 1, '2022-02-08', 2 union all
  select 1, '2022-02-09', 3 union all
  select 1, '2022-02-10', 4 union all
  select 1, '2022-02-11', 5 union all
  select 1, '2022-02-12', 6 union all
  select 1, '2022-02-13', 7 union all
  select 2, '2022-02-14', 8 union all
  select 2, '2022-02-15', 9 union all
  select 2, '2022-02-16', 10 union all
  select 2, '2022-02-17', 11 union all
  select 2, '2022-02-18', 12 union all
  select 2, '2022-02-19', 13 union all
  select 2, '2022-02-20', 14
), timeframe as (
  select date '2022-02-14' as date_start, date '2022-02-17' as date_stop     
)
select date_start, date_stop, 
  sum(if(date between date_start and date_stop,val, 0)) as val_cur,
  sum(if(date between date_start - 7 and date_stop - 7,val, 0)) as val_prev
from your_table, timeframe
group by date_start, date_stop       

with output