'Presto lag dates, group/partitioned by id
Say that I want to find every time that a client updated their budget.
Here's what my data looks like
datetime, client_id, new_budget
__________,__________,___________
2022-01-01,1, ,100
2022-01-01,2, ,300
2022-01-02,1, ,80
2022-01-02,2, ,80
And the code I've run.
SELECT datetime AS dt_1,
LAG(datetime) OVER (ORDER BY client_id, datetime) AS dt_2,
client_id,
new_budget
FROM budget_table
What I'm expecting returned will be
dt_1, dt_2, client_id, new_budget
__________,__________,__________,___________
2022-01-01,NULL, 1 , 100
2022-01-02,2022-01-01,1 , 80
2022-01-01,NULL, 2 , 300
2022-01-02,2022-01-01,2 , 80
Hence there are NULL values for dt_2 in the first entry of each client_id. I'm not sure what code will accomplish this effect; is a GROUP BY clause will be necessary (or a partition over clause.)
But here's the output to the SQL that I ran
dt_1, dt_2, client_id, new_budget
__________,__________,__________,___________
2022-01-01,NULL, 1 , 100
2022-01-02,2022-01-01,1 , 80
2022-01-01,2022-01-02,2 , 300
2022-01-02,2022-01-01,2 , 80
So the huge issue here is that it's not recognizing that the dt_2 should be NULL if the previous row is from a different client_id.
Which syntax is recommended to accomplish this effect?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
