Search code examples
sqldatabasepostgresqllagcoalesce

PostgreSQL adding specific value to missing cells after lag function

If I run the query:

SELECT status,
       created_at,
       lead(created_at) over(partition BY id
                             ORDER BY updated_at) ended_at
FROM salesdb.orders

I get the following result:

| status    | created_at          | ended_at            |
| Opened    | 2019-11-12 11:46:11 | 2019-11-15 12:04:13 |
| Pending   | 2019-11-15 12:04:13 | 2019-11-19 23:03:24 |
| Completed | 2019-11-19 23:03:24 |                     |
| Opened    | 2019-11-14 11:46:11 | 2019-11-17 12:04:13 |
| Pending   | 2019-11-17 12:04:13 | 2019-11-20 23:03:24 |
| Completed | 2019-11-20 23:03:24 |                     |

I would like to insert a specific end date into the missing cells as '2019-12-31'

Like so:

| status    | created_at          | ended_at            |
| Opened    | 2019-11-12 11:46:11 | 2019-11-15 12:04:13 |
| Pending   | 2019-11-15 12:04:13 | 2019-11-19 23:03:24 |
| Completed | 2019-11-19 23:03:24 | 2019-12-31 00:00:00 |
| Opened    | 2019-11-14 11:46:11 | 2019-11-17 12:04:13 |
| Pending   | 2019-11-17 12:04:13 | 2019-11-20 23:03:24 |
| Completed | 2019-11-20 23:03:24 | 2019-12-31 00:00:00 |
Solution

  • You can also use coalesce:

    select status,
       created_at,
       coalesce(
           lead(created_at) over(partition by id order by updated_at), 
           '2019-12-31') ended_at
from salesdb.orders