
########################
#Default Stats
########################
SELECT Loans.CreditGrade,
count( * ) AS LOANS,
min( Loans.AmountBorrowed ) AS MINAMT,
max( Loans.AmountBorrowed ) AS MAXAMT,
avg( Loans.AmountBorrowed ) AS AVGAMT,
variance( Loans.AmountBorrowed ) AS VARAMT,
min( Loans.BorrowerRate ) AS MININT,
max( Loans.BorrowerRate ) AS MAXINT,
avg( Loans.BorrowerRate ) AS AVGINT,
variance( Loans.BorrowerRate ) AS VARINT,
min(Listings.DebtToIncomeRatio) as MINDTI,
max(Listings.DebtToIncomeRatio) as MAXDTI,
avg(Listings.DebtToIncomeRatio) as AVGDTI,
variance(Listings.DebtToIncomeRatio) as VARDTI,
min(Listings.Category) as MINCAT,
max(Listings.Category) as MAXCAT,
avg(Listings.Category) as AVGCAT,
variance(Listings.Category) as VARCAT,
min(CreditProfiles.BankcardUtilization) AS MINBU,
max(CreditProfiles.BankcardUtilization) AS MAXBU,
avg(CreditProfiles.BankcardUtilization) AS AVGBU,
variance(CreditProfiles.BankcardUtilization) AS VARBU,
min(CreditProfiles.RevolvingCreditBalance) AS MINRCB,
max(CreditProfiles.RevolvingCreditBalance) AS MAXRCB,
avg(CreditProfiles.RevolvingCreditBalance) AS AVGRCB,
variance(CreditProfiles.RevolvingCreditBalance) AS VARRCB,
min(CreditProfiles.DelinquenciesLast7Years) AS MINDEL,
max(CreditProfiles.DelinquenciesLast7Years) AS MAXDEL,
avg(CreditProfiles.DelinquenciesLast7Years) AS AVGDEL,
variance(CreditProfiles.DelinquenciesLast7Years) AS VARDEL,
min(CreditProfiles.Income) AS MININC,
max(CreditProfiles.Income) AS MAXINC,
avg(CreditProfiles.Income) AS AVGINC,
variance(CreditProfiles.Income) AS VARINC
#min(Loans.AgeInMonths) as MINAGE,
#max(Loans.AgeInMonths) as MAXAGE,
#avg(Loans.AgeInMonths) as AVGAGE,
#variance(Loans.AgeInMonths) as VARAGE
FROM Loans, Listings, CreditProfiles
WHERE Loans.ListingKey = Listings.ProsperKey
AND Listings.Prosperkey = CreditProfiles.Listingkey
AND( Loans.STATUS LIKE '%Default%' OR Loans.STATUS = 'Charge-Off' OR Loans.STATUS = 'Repurchased')
GROUP BY Loans.CreditGrade

########################
#Late Stats
########################
SELECT Loans.CreditGrade,
count( * ) AS LOANS,
min( Loans.AmountBorrowed ) AS MINAMT,
max( Loans.AmountBorrowed ) AS MAXAMT,
avg( Loans.AmountBorrowed ) AS AVGAMT,
variance( Loans.AmountBorrowed ) AS VARAMT,
min( Loans.BorrowerRate ) AS MININT,
max( Loans.BorrowerRate ) AS MAXINT,
avg( Loans.BorrowerRate ) AS AVGINT,
variance( Loans.BorrowerRate ) AS VARINT,
min(Listings.DebtToIncomeRatio) as MINDTI,
max(Listings.DebtToIncomeRatio) as MAXDTI,
avg(Listings.DebtToIncomeRatio) as AVGDTI,
variance(Listings.DebtToIncomeRatio) as VARDTI,
min(Listings.Category) as MINCAT,
max(Listings.Category) as MAXCAT,
avg(Listings.Category) as AVGCAT,
variance(Listings.Category) as VARCAT,
min(CreditProfiles.BankcardUtilization) AS MINBU,
max(CreditProfiles.BankcardUtilization) AS MAXBU,
avg(CreditProfiles.BankcardUtilization) AS AVGBU,
variance(CreditProfiles.BankcardUtilization) AS VARBU,
min(CreditProfiles.RevolvingCreditBalance) AS MINRCB,
max(CreditProfiles.RevolvingCreditBalance) AS MAXRCB,
avg(CreditProfiles.RevolvingCreditBalance) AS AVGRCB,
variance(CreditProfiles.RevolvingCreditBalance) AS VARRCB,
min(CreditProfiles.DelinquenciesLast7Years) AS MINDEL,
max(CreditProfiles.DelinquenciesLast7Years) AS MAXDEL,
avg(CreditProfiles.DelinquenciesLast7Years) AS AVGDEL,
variance(CreditProfiles.DelinquenciesLast7Years) AS VARDEL,
min(CreditProfiles.Income) AS MININC,
max(CreditProfiles.Income) AS MAXINC,
avg(CreditProfiles.Income) AS AVGINC,
variance(CreditProfiles.Income) AS VARINC
#min(Loans.AgeInMonths) as MINAGE,
#max(Loans.AgeInMonths) as MAXAGE,
#avg(Loans.AgeInMonths) as AVGAGE,
#variance(Loans.AgeInMonths) as VARAGE
FROM Loans, Listings, CreditProfiles
WHERE Loans.ListingKey = Listings.ProsperKey
AND Listings.Prosperkey = CreditProfiles.Listingkey
AND(Loans.STATUS LIKE '%Late%')
GROUP BY Loans.CreditGrade

########################
#Current Stats
########################
SELECT Loans.CreditGrade,
count( * ) AS LOANS,
min( Loans.AmountBorrowed ) AS MINAMT,
max( Loans.AmountBorrowed ) AS MAXAMT,
avg( Loans.AmountBorrowed ) AS AVGAMT,
variance( Loans.AmountBorrowed ) AS VARAMT,
min( Loans.BorrowerRate ) AS MININT,
max( Loans.BorrowerRate ) AS MAXINT,
avg( Loans.BorrowerRate ) AS AVGINT,
variance( Loans.BorrowerRate ) AS VARINT,
min(Listings.DebtToIncomeRatio) as MINDTI,
max(Listings.DebtToIncomeRatio) as MAXDTI,
avg(Listings.DebtToIncomeRatio) as AVGDTI,
variance(Listings.DebtToIncomeRatio) as VARDTI,
min(Listings.Category) as MINCAT,
max(Listings.Category) as MAXCAT,
avg(Listings.Category) as AVGCAT,
variance(Listings.Category) as VARCAT,
min(CreditProfiles.BankcardUtilization) AS MINBU,
max(CreditProfiles.BankcardUtilization) AS MAXBU,
avg(CreditProfiles.BankcardUtilization) AS AVGBU,
variance(CreditProfiles.BankcardUtilization) AS VARBU,
min(CreditProfiles.RevolvingCreditBalance) AS MINRCB,
max(CreditProfiles.RevolvingCreditBalance) AS MAXRCB,
avg(CreditProfiles.RevolvingCreditBalance) AS AVGRCB,
variance(CreditProfiles.RevolvingCreditBalance) AS VARRCB,
min(CreditProfiles.DelinquenciesLast7Years) AS MINDEL,
max(CreditProfiles.DelinquenciesLast7Years) AS MAXDEL,
avg(CreditProfiles.DelinquenciesLast7Years) AS AVGDEL,
variance(CreditProfiles.DelinquenciesLast7Years) AS VARDEL,
min(CreditProfiles.Income) AS MININC,
max(CreditProfiles.Income) AS MAXINC,
avg(CreditProfiles.Income) AS AVGINC,
variance(CreditProfiles.Income) AS VARINC
#min(Loans.AgeInMonths) as MINAGE,
#max(Loans.AgeInMonths) as MAXAGE,
#avg(Loans.AgeInMonths) as AVGAGE,
#variance(Loans.AgeInMonths) as VARAGE
FROM Loans, Listings, CreditProfiles
WHERE Loans.ListingKey = Listings.ProsperKey
AND Listings.Prosperkey = CreditProfiles.Listingkey
AND(Loans.STATUS = 'Current'
OR Loans.STATUS ='Payoff in Progress')
GROUP BY Loans.CreditGrade

########################
#Paid Stats
########################
SELECT Loans.CreditGrade,
count( * ) AS LOANS,
min( Loans.AmountBorrowed ) AS MINAMT,
max( Loans.AmountBorrowed ) AS MAXAMT,
avg( Loans.AmountBorrowed ) AS AVGAMT,
variance( Loans.AmountBorrowed ) AS VARAMT,
min( Loans.BorrowerRate ) AS MININT,
max( Loans.BorrowerRate ) AS MAXINT,
avg( Loans.BorrowerRate ) AS AVGINT,
variance( Loans.BorrowerRate ) AS VARINT,
min(Listings.DebtToIncomeRatio) as MINDTI,
max(Listings.DebtToIncomeRatio) as MAXDTI,
avg(Listings.DebtToIncomeRatio) as AVGDTI,
variance(Listings.DebtToIncomeRatio) as VARDTI,
min(Listings.Category) as MINCAT,
max(Listings.Category) as MAXCAT,
avg(Listings.Category) as AVGCAT,
variance(Listings.Category) as VARCAT,
min(CreditProfiles.BankcardUtilization) AS MINBU,
max(CreditProfiles.BankcardUtilization) AS MAXBU,
avg(CreditProfiles.BankcardUtilization) AS AVGBU,
variance(CreditProfiles.BankcardUtilization) AS VARBU,
min(CreditProfiles.RevolvingCreditBalance) AS MINRCB,
max(CreditProfiles.RevolvingCreditBalance) AS MAXRCB,
avg(CreditProfiles.RevolvingCreditBalance) AS AVGRCB,
variance(CreditProfiles.RevolvingCreditBalance) AS VARRCB,
min(CreditProfiles.DelinquenciesLast7Years) AS MINDEL,
max(CreditProfiles.DelinquenciesLast7Years) AS MAXDEL,
avg(CreditProfiles.DelinquenciesLast7Years) AS AVGDEL,
variance(CreditProfiles.DelinquenciesLast7Years) AS VARDEL,
min(CreditProfiles.Income) AS MININC,
max(CreditProfiles.Income) AS MAXINC,
avg(CreditProfiles.Income) AS AVGINC,
variance(CreditProfiles.Income) AS VARINC
#min(Loans.AgeInMonths) as MINAGE,
#max(Loans.AgeInMonths) as MAXAGE,
#avg(Loans.AgeInMonths) as AVGAGE,
#variance(Loans.AgeInMonths) as VARAGE
FROM Loans, Listings, CreditProfiles
WHERE Loans.ListingKey = Listings.ProsperKey
AND Listings.Prosperkey = CreditProfiles.Listingkey
AND(Loans.STATUS = 'Paid')
GROUP BY Loans.CreditGrade


########################
#Rejected Listing Stats
########################
SELECT Listings.CreditGrade,
count( * ) AS LOANS,
min( Listings.AmountRequested ) AS MINAMT,
max( Listings.AmountRequested ) AS MAXAMT,
avg( Listings.AmountRequested ) AS AVGAMT,
variance( Listings.AmountRequested ) AS VARAMT,
min( Listings.BorrowerMaximumRate ) AS MININT,
max( Listings.BorrowerMaximumRate ) AS MAXINT,
avg( Listings.BorrowerMaximumRate ) AS AVGINT,
variance(Listings.BorrowerMaximumRate) AS VARINT,
min(Listings.DebtToIncomeRatio) as MINDTI,
max(Listings.DebtToIncomeRatio) as MAXDTI,
avg(Listings.DebtToIncomeRatio) as AVGDTI,
variance(Listings.DebtToIncomeRatio) as VARDTI,
min(Listings.Category) as MINCAT,
max(Listings.Category) as MAXCAT,
avg(Listings.Category) as AVGCAT,
variance(Listings.Category) as VARCAT,
min(CreditProfiles.BankcardUtilization) AS MINBU,
max(CreditProfiles.BankcardUtilization) AS MAXBU,
avg(CreditProfiles.BankcardUtilization) AS AVGBU,
variance(CreditProfiles.BankcardUtilization) AS VARBU,
min(CreditProfiles.RevolvingCreditBalance) AS MINRCB,
max(CreditProfiles.RevolvingCreditBalance) AS MAXRCB,
avg(CreditProfiles.RevolvingCreditBalance) AS AVGRCB,
variance(CreditProfiles.RevolvingCreditBalance) AS VARRCB,
min(CreditProfiles.DelinquenciesLast7Years) AS MINDEL,
max(CreditProfiles.DelinquenciesLast7Years) AS MAXDEL,
avg(CreditProfiles.DelinquenciesLast7Years) AS AVGDEL,
variance(CreditProfiles.DelinquenciesLast7Years) AS VARDEL,
min(CreditProfiles.Income) AS MININC,
max(CreditProfiles.Income) AS MAXINC,
avg(CreditProfiles.Income) AS AVGINC,
variance(CreditProfiles.Income) AS VARINC
#min(Loans.AgeInMonths) as MINAGE,
#max(Loans.AgeInMonths) as MAXAGE,
#avg(Loans.AgeInMonths) as AVGAGE,
#variance(Loans.AgeInMonths) as VARAGE,
FROM Listings, CreditProfiles
WHERE Listings.ProsperKey = CreditProfiles.Listingkey
AND (Listings.STATUS = 'expired')
GROUP BY Listings.CreditGrade