I have this query that I use to calculate returning customers (with more than one order)

SELECT COUNT(*)
  FROM (SELECT customer_id, COUNT(*) as order_count
        FROM orders
        WHERE shop_id = #{shop_id}
          AND orders.active = true
          AND orders.created_at >= '#{from}'
          AND orders.created_at < '#{to}'
        GROUP BY customer_id
        HAVING COUNT(orders) > 1
        ORDER BY order_count) src;

And if I want new customers (that have only one order) I simply change this line:

HAVING COUNT(orders) = 1

Now, how can I generate a series between 2 given dates that will give me the number of new and returning customers for each day between the dates?

Expected result:

I have tried this but doesn't work at all (error syntax near from is the error I'm getting) and I'm not sure how to fix. Ideas?

    select *, return_customers
      from (select created_at, count(*) as order_count
      from orders
      where shop_id = 43
        and created_at >= '2022-07-01'
        and created_at < '2022-07-10'
      group by customer_id
      having count(orders) > 1
      order by order_count) as return_customers from generate_series(timestamp '2007-01', timestamp '2022-07-11', interval '1 day')
    as g(created_at)
    left join (
        select created_at::date,
                count(*) as order_count
                from orders
                where shop_id 43
                and created_at >= '2022-07-01'
                and created_at < '2022-07-10'
                group by customer_id
                having count(orders) > 1
                order by order_count
                group by 1) o using (created_at)) sub
order by created_at desc;

CodePudding user response：

This is based on your initial query w/o the having clause and conditional counts with filter. order by in src is redundant too.

SELECT src.order_date as "date", 
       COUNT(*) filter (where order_count > 1) as "returning",
       COUNT(*) filter (where order_count = 1) as "new"
FROM 
(
  SELECT date_trunc('day', o.created_at)::date as order_date, 
         COUNT(*) as order_count
  FROM orders o
  WHERE o.shop_id = #{shop_id}
    AND o.active
    AND o.created_at >= '#{from}'
    AND o.created_at < '#{to}'
  GROUP BY o.customer_id, order_date
) as src
group by order_date;