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.
CodePudding user response：
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: