With PostgreSQL how to query for records contained within a date range of the created_at field?

In my PostgreSQL database I have an invitations table with the following fields:

Invitations: id, created_at, completed_at (timestamp)

I am working to write a PostgreSQL query that returns the number of records completed between 2-7 days of the creation date.

Here is what I have so far:

SELECT round(count(i.completed_at <= i.created_at + interval '7 day' and i.completed_at > i.created_at + interval '1 day')::decimal / count(DISTINCT i.id), 2) * 100 AS "CR in D2-D7"
FROM invitations i

The select statement is not returning the correct value. What am I doing wrong?

Answer

The expression you are feeding to the first COUNT yields a boolean and never a NULL (unless its inputs are NULL). But COUNT counts all of its non-NULL input, so whether the expression returns true or false, the count still increments. There are many ways to fix this, a simple (but probably not the best–just the least typing difference from what you already have) one would be to use nullif to convert false to NULL inside the first COUNT.

But even then, is this correct? It seems odd that one COUNT has a DISTINCT and the other does not.

So a more complete solution may be something like:

SELECT 
  round(
    count(distinct i.id) filter (where i.completed_at <= i.created_at + interval '7 day' and i.completed_at > i.created_at + interval '1 day')::decimal 
    / 
    count(DISTINCT i.id)
  ,2) * 100 AS "CR in D2-D7"
FROM invitations i