How to lag return only previous month grouping from a set of date values?

user_19769194 0
Viewed 41

Hi I have a a table as :

date_key month customer_id
2022-01-01 1 1
2022-01-23 1 1
2022-02-02 2 1
2022-02-15 2 1
2022-02-16 2 1
2022-02-18 2 1
2022-02-16 2 1
2022-05-18 5 1
2022-06-11 6 1
2022-06-12 6 1
2022-06-13 6 1
2022-06-15 6 1

and want to lag on last previous month above

date_key month customer_id lastMonth
2022-01-01 1 1
2022-01-23 1 1
2022-02-02 2 1 1
2022-02-15 2 1 1
2022-02-16 2 1 1
2022-02-18 2 1 1
2022-02-16 2 1 1
2022-05-18 5 1 2
2022-06-11 6 1 5
2022-06-12 6 1 5
2022-06-13 6 1 5
2022-06-15 6 1 5

I tried using

select *
lag(month,1) over(partition by customer_id order by month) lastMonth
from table

However this does not seem to get the result as needed. Please do help.

google-bigquery
1 Answers

Try this one:

SELECT *,
       LAST_VALUE(month) OVER (
         PARTITION BY customer_id 
             ORDER BY EXTRACT(YEAR FROM date_key) * 12 + month 
             RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS lastMonth
  FROM sample_table
 ORDER BY date_key;

Query results:

+-----+------------+-------+-------------+-----------+
| Row |  date_key  | month | customer_id | lastMonth |
+-----+------------+-------+-------------+-----------+
|   1 | 2022-01-01 |     1 |           1 | null      |
|   2 | 2022-01-23 |     1 |           1 | null      |
|   3 | 2022-02-02 |     2 |           1 | 1         |
|   4 | 2022-02-15 |     2 |           1 | 1         |
|   5 | 2022-02-16 |     2 |           1 | 1         |
|   6 | 2022-02-16 |     2 |           1 | 1         |
|   7 | 2022-02-18 |     2 |           1 | 1         |
|   8 | 2022-05-18 |     5 |           1 | 2         |
|   9 | 2022-06-11 |     6 |           1 | 5         |
|  10 | 2022-06-12 |     6 |           1 | 5         |
|  11 | 2022-06-13 |     6 |           1 | 5         |
|  12 | 2022-06-15 |     6 |           1 | 5         |
+-----+------------+-------+-------------+-----------+
user_190399200
Related