CODEWITHSUNDEEP
mysqlsql
jemminger
jemminger

Reputation: 5173

sql query: no payments in last 90 days

Suppose I have a Client with many Payments. How do I query to get all clients that have no payment records in the last 90 days?

clients
=======
id          integer
name        string

payments
========
id          integer
client_id   integer
created_at  datetime

Essentially the inverse of:

select *
from clients
inner join payments on payments.client_id = clients.id
where payments.created_at > utc_timestamp() - interval 90 day

Hopefully more efficient than:

select *
from clients 
where id not in (
  select *
  from clients
  inner join payments on payments.client_id = clients.id
  where payments.created_at > utc_timestamp() - interval 90 day
)

Upvotes: 1

Views: 1456

Answers (2)

Andomar
Andomar

Reputation: 238076

Ensure there's an index on payments(client_id), or even better, payments(client_id, created_at).

For alternative way to write your query, you could try a not exists, like:

select  *
from    clients c
where   not exists
        (
        select  *
        from    payments p
        where   p.payments.client_id = clients.id
                and payments.created_at > utc_timestamp() - interval 90 day
        )

Or an exclusive left join:

select  *
from    clients c
left join
        payments p
on      p.payments.client_id = clients.id
        and payments.created_at > utc_timestamp() - interval 90 day
where   p.client_id is null

If both are slow, add the explain extended output to your question, so we can see why.

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

select *
from clients
left join payments on 
    payments.client_id = clients.id and 
    payments.created_at > utc_timestamp() - interval 90 day
where payments.client_id is null

Upvotes: 0

Related Questions