Here is my base table. I'm trying to add a column where I would see the approved date.
| date | customer_id | approved |
|---|---|---|
| 01/01/23 | a | false |
| 01/01/23 | b | false |
| 02/01/23 | a | true |
| 02/01/23 | b | false |
| 03/01/23 | a | true |
| 03/01/23 | b | true |
Here is what I want to get where I can see the approved date for each customer_id. In this example a would have an approved date of 02/01/23 and b would have an approved date of 03/01/23.
| date | customer_id | approved | approved_date |
|---|---|---|---|
| 01/01/23 | a | false | 02/01/23 |
| 01/01/23 | b | false | 03/01/23 |
| 02/01/23 | a | true | 02/01/23 |
| 02/01/23 | b | false | 03/01/23 |
| 03/01/23 | a | true | 02/01/23 |
| 03/01/23 | b | true | 03/01/23 |
I am using SQL presto if that helps.
Many thanks!
CodePudding user response：
One option is to use the MIN window function, which attempts to select only dates where approved = true (per conditional aggregation), then retrieve the minimum date.
SELECT date_,
customer_id,
approved,
MIN(CASE WHEN approved = true THEN date_ END) OVER(PARTITION BY customer_id)
FROM tab
ORDER BY date_
Output:
| date_ | customer_id | approved | min |
|---|---|---|---|
| 2023-01-01 | a | false | 2023-02-01 |
| 2023-01-01 | b | false | 2023-03-01 |
| 2023-02-01 | a | true | 2023-02-01 |
| 2023-02-01 | b | false | 2023-03-01 |
| 2023-03-01 | a | true | 2023-02-01 |
| 2023-03-01 | b | true | 2023-03-01 |
Check the demo here.
CodePudding user response：
You can use a Window function for MIN() aggregation along with a conditional such as
SELECT t.*,
MIN(CASE WHEN approved = 'true' THEN date END)
OVER (PARTITION BY customer_id) AS approved_date
FROM t