SQL - Average number of records within a time period

2
votes

I'm trying to compile some lifetime value information for customers within one of our databases.

We have an MS SQL Server database which stores all of our customer/transactional information.

My issue is that I don't have much experience when it comes to MS SQL Server (or SQL in general) - I'd like to be able to run a query against the database that pulls AVG number of loans, and AVG revenue based on three criteria:

1.) Loans be counted if they are 'approved' 2.) Loans from a customer_id only be counted if the first loan (first identified by date_created field) be on or after a certain 'mm/yyyy' 3.) I'm able to specify for how many months after the first 'mm/yyyy' to tally the number of loans / revenue to be included within the AVG

Here is what the database would look like:

customer_id   | loan_status | date_created      | revenue
111       | 'approved'  | 2010-06-20 17:17:09   | 100.00
222       | 'approved'  | 2010-06-21 09:54:43   | 255.12
333       | 'denied'    | 2011-06-21 12:47:30   | NULL
333       | 'approved'  | 2011-06-21 12:47:20   | 56.87
222       | 'denied'    | 2011-06-21 09:54:48   | NULL
222       | 'approved'  | 2011-06-21 09:54:18   | 50.00
111       | 'approved'  | 2011-06-20 17:17:23   | 100.00
... loads' of records ...
555       | 'approved'  | 2012-01-02 09:08:42   | 24.70
111       | 'denied'    | 2012-01-05 02:10:36   | NULL
666       | 'denied'    | 2012-02-05 03:31:16   | NULL
555       | 'approved'  | 2012-02-17 09:32:26   | 197.10
777       | 'approved'  | 2012-04-03 18:28:45   | 300.50
777       | 'approved'  | 2012-06-28 02:42:01   | 201.80
555       | 'approved'  | 2012-06-21 22:16:59   | 10.00
666       | 'approved'  | 2012-09-30 01:17:20   | 50.00

If I wanted to find the avg transaction count (approved transactions), and average revenue per approved transaction for all customer's who's first loan was in/after 2012-01, and for a period of 4 months after then, how would I go about querying the database?

Any help is greatly appreciated.

2
sqlsql-server-2008
Hey Mitch! Unfortunately not the brightest with SQL - I'm typically given a data dump to analyze and go from there. Wanted to try doing some queries on my own, and realized I was in a bit over my head. – cometrico
Judging by your comment at @RichardTheKiwi's answer it seems you have more requirements than is apparent from your question. Could you post the expected result for your data set? – Sebastian Meine

2 Answers

0
votes

something like this (there maybe a few typos here and there)...

you could first calculate the minimum loan date:

select customer_id, min(date_created) from table t where loan_status = 'approved' group by customer_id

then you can join to it:

select customer_id,  count(date_created), avg(revenue) from table t 
join (
select customer_id,  min(date_created) as min_date from table t where loan_status = 'approved' group by customer_id ) s 
on t.customer_id = s.customer_id 
where t.date_created between s.min_date and DATEADD(month, 4, s.min_date) and t.loan_status = 'approved'
0
votes

Rename tbl to your table name.
Specify dates in the format YYYYMMDD.

select customer_id, AVG(revenue) average_revenue
from
(
    select customer_id
    from tbl
    group by customer_id
    having min(date_created) >= '20120101'
) fl
join tbl t on t.customer_id = fl.customer_id
where t.loan_status = 'approved'
  and date_created < '20120501' -- NOT including May the first, so Jan through Apr (4 months)

If you mean 4 months after each customer's first loan, leave me a comment, state whether it's 4 calendar months (e.g. 15-Jan to 15-May) or up to the last day of the 4th month (15-Jan to 30-Apr), and I'll update the answer.