Skip to content
Advertisement

Selecting first element in Group by object Postgres

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement