Getting new vs. returning customers in Postgres

Question

I have this work task, to write a SQL query that would show how many of the active customers are new (= do not have prior transactions) as opposed to returning per a given period.

There is nothing particular in the DB to get this, so my solution takes a transaction and compares customer_id to the pool of customer_ids that belong to all transactions that took place prior. Here's how it looks:

SELECT allthem.period, allthem.c "all", newonly.c "new", (allthem.c - newonly.c) "returning"
FROM (
    SELECT date_trunc('week', t.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
    FROM transactions t
    WHERE t.status = 'paid' AND (t.price->>'payment_total')::real > 35
    GROUP BY date_trunc('week', t.paid::timestamptz)
    ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS allthem
JOIN (
    SELECT date_trunc('week', b.paid::timestamptz) AS period, COUNT(DISTINCT(t.customer_id)) AS c
    FROM transactions t
    WHERE t.status = 'paid' AND (t.price->>'payment_total')::real > 35
    AND t.customer_id NOT IN (SELECT customer_id FROM transactions WHERE status='paid' AND (price->>'payment_total')::real > 35 AND paid::timestamptz < t.paid::timestamptz)
    GROUP BY date_trunc('week', t.paid::timestamptz)
    ORDER BY date_trunc('week', t.paid::timestamptz) DESC) AS newonly ON allthem.period=newonly.period
WHERE allthem.period > date_trunc('week', now()::timestamptz at time zone 'pst') - interval '12 months'

It works, but the problem is that it is quite slow.

Is there any way to compute the required data with less server load?

Answer 1

I had some troubles reading your query, but I think I got it, please tell me if not. Basically you want a report per week of total customers vs new customers, right?

I did a query that seems a bit more performant, please have a look and let me know if your requirements are accomplished:

WITH firstTransactions AS (
    SELECT customer_id, min(date_trunc('week', paid::timestamptz)) as firstTransactionWeek
    FROM transactions 
    WHERE status='paid' 
      AND (price->>'payment_total')::real > 35  
    GROUP BY customer_id
)

SELECT date_trunc('week', t.paid::timestamptz) AS period, 
       COUNT(DISTINCT(t.customer_id)) AS total_customers, 
       COUNT(DISTINCT(ft.customer_id)) AS new_customers,
       COUNT(DISTINCT(t.customer_id)) - COUNT(DISTINCT(ft.customer_id)) AS returning
FROM transactions t
LEFT JOIN firstTransactions ft
ON ft.firstTransactionWeek = date_trunc('week', t.paid::timestamptz)
WHERE t.status = 'paid' 
  AND (t.price->>'payment_total')::real > 35
  AND date_trunc('week', t.paid::timestamptz) > date_trunc('week', now()::timestamptz at time zone 'pst') - interval '12 months'
GROUP BY date_trunc('week', t.paid::timestamptz)
ORDER BY date_trunc('week', t.paid::timestamptz) DESC

You have too much inner selects what can reduce your performance, it becomes easier when we refactor it and sometimes in order to don't repeat code in inner queries, CTEs are useful, have a look at that.