SQL: count 1 in a given month if a count > 1 exists previously

Self join usually performs poorly.

You can use a window function FIRST_VALUE to get the first date when the customer appeared in the data and scan the table only once. After that simply compare the current date with the first date to see if this is a repeat customer or not.

Preliminary query:

WITH
CTE
AS
(
  SELECT
    *
    ,FIRST_VALUE(month_year) OVER (PARTITION BY customer_id ORDER BY month_year) AS first_date
  FROM
    orders
)
SELECT
  *
FROM
  CTE
ORDER BY
  customer_id
  ,month_year
  ,order_id
;

This produces the following results:

| month_year | order_id | customer_id | first_date |
|------------|----------|-------------|------------|
|    2016-08 |     0010 |       11372 |    2016-08 |
|    2016-08 |     0011 |       11372 |    2016-08 |
|    2016-10 |     0014 |       11372 |    2016-08 |
|    2016-11 |     0016 |       11372 |    2016-08 |
|    2016-12 |     0017 |       11372 |    2016-08 |
|    2017-01 |     0018 |       11372 |    2016-08 |
|    2017-01 |     0019 |       11372 |    2016-08 |
|    2016-04 |     0001 |       24662 |    2016-04 |
|    2016-05 |     0002 |       24662 |    2016-04 |
|    2016-05 |     0002 |       24662 |    2016-04 |
|    2016-07 |     0003 |       24662 |    2016-04 |
|    2016-07 |     0003 |       24662 |    2016-04 |
|    2016-07 |     0004 |       24662 |    2016-04 |
|    2016-07 |     0004 |       24662 |    2016-04 |
|    2016-08 |     0005 |       24662 |    2016-04 |
|    2016-08 |     0006 |       24662 |    2016-04 |
|    2016-08 |     0007 |       24662 |    2016-04 |
|    2016-08 |     0008 |       24662 |    2016-04 |
|    2016-08 |     0009 |       24662 |    2016-04 |
|    2016-09 |     0012 |       24662 |    2016-04 |
|    2016-10 |     0013 |       24662 |    2016-04 |
|    2016-11 |     0015 |       24662 |    2016-04 |

Final query

WITH
CTE
AS
(
  SELECT
    *
    ,FIRST_VALUE(month_year) OVER (PARTITION BY customer_id ORDER BY month_year) AS first_date
  FROM
    orders
)
SELECT
  month_year
  ,COUNT(DISTINCT CASE WHEN month_year > first_date THEN customer_id END) AS repeat_orders
  ,COUNT(DISTINCT CASE WHEN month_year = first_date THEN customer_id END) AS first_orders
FROM
  CTE
GROUP BY
  month_year
ORDER BY
  month_year
;

This query produces the following result:

| month_year | repeat_orders | first_orders |
|------------|---------------|--------------|
|    2016-04 |             0 |            1 |
|    2016-05 |             1 |            0 |
|    2016-07 |             1 |            0 |
|    2016-08 |             1 |            1 |
|    2016-09 |             1 |            0 |
|    2016-10 |             2 |            0 |
|    2016-11 |             2 |            0 |
|    2016-12 |             1 |            0 |
|    2017-01 |             1 |            0 |

Performance

If you create an index on (customer_id, month_year) you should eliminate a sort in the query plan (i.e. it will run faster).

I think the query will look something like:

SELECT 
 o1.month_year, 
 count(distinct o1.customer_id) 
from orders o1
left join 
 orders o2 on o1.customer_id = o2.customer_id
  and o1.month_year > o2.month_year
   and o1.order_id != o2.order_id
where o2.order_id is not null
group by month_year

It's starts similar to what you've done. We're just adding a self join to the orders table to find if the customer being evaluated placed an order in any month before. We're also making sure that the order_id doesn't match because i saw some duplicates in the data. This produces the expected results

Hope it makes sense. Let me know if you have any follow up would be happy to help

Edit

Based on the comment that "first time customer" info may be required too I think we can use a CTE here

with first_customers as (
  select 
    customer_id,
    min(month_year) month_year
  from orders
  group by customer_id
), repeat_customers as (
  select distinct on (o.order_id)
    o.customer_id,
    o.month_year
  from orders o
  join first_customers fc on o.customer_id = fc.customer_id and o.month_year > fc.month_year
) select 
    o.month_year, 
    count(distinct fc.customer_id) first_customers, 
    count(distinct rc.customer_id) repeat_customers 
  from orders o
    left join first_customers fc on o.month_year = fc.month_year
    left join repeat_customers rc on o.month_year = rc.month_year
  group by o.month_year
  order by o.month_year

Essentially we find the first time customers by grouping orders data by customer_id and fetch the row with the min month_year

The logic for finding repeat_customers goes in its respective cte as well. It's a bit simplified now because we can use the information from first_customers to see if the customer ever ordered before

In the final select, we query orders table and join on both the ctes and count their distinct customer_ids by month