I have the following table and I want to get the specidic Amount per loan_ID that corresponds to the earliest observation with greater than or equal to 10 dpd per month.
Loan_ID date dpd Amount 1 1/1/2017 1 55 1 1/2/2017 2 100 1 1/3/2017 3 5000 1 1/4/2017 5 6000 1 1/5/2017 10 50000 1 1/6/2017 15 50001 1 1/9/2017 31 50004 1 1/10/2017 55 50005 1 1/11/2017 59 50006 1 1/12/2017 65 50007 1 1/13/2017 70 80000 1 1/20/2017 85 900000 1 1/29/2017 92 100000 1 1/30/2017 93 10000 2 1/1/2017 0 522 2 1/2/2017 8 5444 2 1/3/2017 12 8784 2 1/6/2017 15 6221 2 1/12/2017 18 2220 2 1/13/2017 20 177 2 1/29/2017 35 5151 2 1/30/2017 60 40000 2 1/31/2017 61 5500
The expected output:
Loan_ID Month Amount 1 1 50000 2 1 8784
Advertisement
Answer
SELECT DISTINCT ON ("Loan_ID", date_trunc('month', "date")) "Loan_ID", date_trunc('month', "date")::date as month, "Amount" FROM loans WHERE dpd >= 10 ORDER BY "Loan_ID", date_trunc('month', "date"), "date" ;
Returns:
Loan_ID | month | Amount |
---|---|---|
1 | 2017-01-01 | 50000 |
2 | 2017-01-01 | 8784 |
You can find test case in db<>fiddle