'Create column with timeframe relative to other column in SQL
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
