I have the following table with these constraints –
- Units can only be released or bought on a given day (one of the columns will always be 0)
- Units released on one day can be bought from the next day onwards
- Units released on one day can be bought in parts across multiple days (need not be fully bought on one day)
| Event Date | Released Units | Bought Units |
|---|---|---|
| 1/1/2024 | 1 | 0 |
| 1/2/2024 | 0 | 0.5 |
| 1/3/2024 | 0.5 | 0 |
| 1/4/2024 | 0 | 1 |
| 1/5/2024 | 0.5 | 0 |
| 1/6/2024 | 0.5 | 0 |
| 1/7/2024 | 0 | 1 |
I would now like to understand when a given “released unit” was fully bought. I am working on SQL in Snowflake.
For the above example, I’d like the following output to be as shown:
| Event Date | Released Units | Units fully bought by |
|---|---|---|
| 1/1/2024 | 1 | 1/4/2024 |
| 1/2/2024 | 0 | NULL |
| 1/3/2024 | 0.5 | 1/4/2024 |
| 1/4/2024 | 0 | NULL |
| 1/5/2024 | 0.5 | 1/7/2024 |
| 1/6/2024 | 0.5 | 1/7/2024 |
| 1/7/2024 | 0 | NULL |
Appreciate the help!
The first_value function wouldn’t work here as it can attribute one Bought to many Released, Im not sure how to knock one Bought out as soon as it is attributed to a Release
New contributor
Shweta Gopinath is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.