0

I'm having trouble figuring out how to write this query:

We have a sales table but the sales table only represents customers that have purchased.

We have a customers table that represents all of the customers that we have.

I have tried:

select 
    c.customer_id
from 
    customers c
left join 
    orders o 
    on c.customer_id=o.customer_id
    and c.created_at >= now()-interval '1 year'
group by
    c.customer_id
having 
    count(distinct o.order_id)=0

But for a table with many millions of rows, it is very slow even with index.

I'm assuming it's because of the left join.

What can I do?

0
SELECT c.customer_id
    FROM customers AS c
    WHERE NOT EXISTS ( SELECT 1 FROM orders
                      WHERE customer_id = c.customer_id
                        AND sale_date > NOW() - INTERVAL 1 YEAR
                     )
0
SELECT c.customer_id
    FROM customers AS c
    WHERE  c.customer_id not in ( SELECT customer_id FROM orders
                      sale_date > NOW() - INTERVAL 1 year)

You can make faster if you index sale date and customer_id in orders table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.