SQL查询按月对新客户和老客户进行分组

Question

我正在寻找可以每月统计新客户和回头客的查询。

基本上，我有超过 2 年的客户申请贷款的数据，一些客户在 1、2、3... x 个月后退还了第一笔贷款。我想要的是一个以以下格式返回结果的查询:

Month YY  |  New Customers  | Returning Customers
Jan '16   |       6         |        0
Feb '16   |       3         |        0
Mar '16   |       2         |        3
Apr '16   |       4         |        2

我认为在我的表(表名:Loans)中的一些有用列包括“customer_id”、“date_disbursed” .

新客户的定义是给定月份内第一次出现在表上的 customer_id。Returning Customer 是一个 customer_id，它在随后几个月的表格中的第一个日期之后出现不止一次。

例如:如果 customer_id:“2231”于 16 年 1 月首次出现在表格中，则应在 16 年 1 月将其计为新客户，如果它2 个月后返回，也就是 2016 年 4 月，他应该被算作回头客。

Answer 1

这样的事情似乎可以做你想做的事:

测试设置:

create table loans
(
  customer_id integer, 
  date_disbursed date
);

insert into loans
values
( 1, date '2016-01-01'),
( 2, date '2016-01-02'),
( 3, date '2016-01-04'),
( 4, date '2016-01-08'),
( 5, date '2016-01-12'),
( 6, date '2016-01-18'),
( 7, date '2016-02-08'),
( 8, date '2016-02-12'),
( 9, date '2016-03-18'),
(10, date '2016-03-12'),
(11, date '2016-03-18'),
( 3, date '2016-03-04'),
( 4, date '2016-03-08'),
( 5, date '2016-03-12'),
( 5, date '2016-04-12'),
(12, date '2016-04-12'),
( 5, date '2016-05-12'),
( 6, date '2016-05-18');

查询:

select to_char(date_disbursed, 'yyyy-mm') as month,
       count(*) filter (where new_customer) as new_customers,
       count(*) filter (where recurring_customer) as returning_customers
from (       
  select customer_id,
         date_disbursed, 
         date_disbursed = min(date_disbursed) over w as new_customer,
         date_disbursed > min(date_disbursed) over w as recurring_customer
  from loans
  window w as (partition by customer_id)
) t
group by to_char(date_disbursed, 'yyyy-mm')
order by to_char(date_disbursed, 'yyyy-mm');

返回

month   | new_customers | returning_customers
--------+---------------+--------------------
2016-01 |             6 |                   0
2016-02 |             2 |                   0
2016-03 |             3 |                   3
2016-04 |             1 |                   1
2016-05 |             0 |                   2