-1

Is there a way to bring up the following with the table below:

customer_id | loan_date  | loan_amount | loan_paid | status
------------+------------+-------------+-----------+--------
customer1     04/02/2010   5000          3850        active
customer2     04/02/2010   3000          3000        completed
customer3     04/02/2010   6500          4300        defaulted
...

Avg loan, the standard deviation of all the loans, the number of loans, the total amount of defaulted, and the total amount of collected loans per month. (I have data for about 5 years).

I have no idea of where to start.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Camue
  • 469
  • 7
  • 17
  • 1
    Start from reading about aggregate function. In this case - for statistic, like stddev, count, sum. https://www.postgresql.org/docs/14/functions-aggregate.html – Kadet May 14 '22 at 18:34
  • The database questions usually follow these steps: 1. data 2. expected output. Also it is a good habit to show of what have you tryied so far. – user14063792468 May 14 '22 at 21:26

1 Answers1

0

Start like this:

SELECT date_trunc('month', loan_date)
     , avg(loan_amount) AS avg_loan
     , stddev_samp(loan_amount)  AS stddev_samp
     , count(*) AS ct_loans
     , count(*) FILTER (WHERE status = 'defaulted') AS ct_defaulted
     , sum(loan_paid) AS sum_paid
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Then refine. Details are unclear. Not sure what loan_paid signifies exactly, and what you want to sum exactly. And there are multiple measures under the name of "standard deviation" ...

About aggregate functions.

About date_trunc().

About GROUP BY 1:

About the aggregate FILTER clause:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228