How would you DRY out this Postgres query to calculate task completion rate?-CodePudding

I have a table of tasks. They have a property called completed. I would like to figure out what proportion of tasks in a certain date range have been completed.

Here's a window of tasks I'd like to analyse:

SELECT * from tasks 
WHERE created_at > CURRENT_DATE - INTERVAL '15 day' 
AND created_at < CURRENT_DATE - INTERVAL '5 day'

The rather clumsy approach I have so far

It's embarassing that I can't think of a smarter approach than this but for now this is all I have:

-- Calculate completion rate: completed / total

-- completed tasks
SELECT (
  SELECT COUNT(*) from tasks 
  WHERE created_at > CURRENT_DATE - INTERVAL '15 day' 
  AND created_at < CURRENT_DATE - INTERVAL '5 day' 
  AND completed = true
)

-- / total tasks
/ SELECT (
  SELECT COUNT(*) from tasks 
  WHERE created_at > CURRENT_DATE - INTERVAL '15 day' 
  AND created_at < CURRENT_DATE - INTERVAL '5 day' 
)

How would I DRY this query out? I read up on the principle of creating a temporary view but that seems to be overkill for what's needed.

CodePudding user response：

You can use SUM with a conditional operator to sum either 0 or 1 for the Completed=true and then just a count on the total records as the 2nd column. `

SELECT SUM(CASE WHEN completed THEN 1 ELSE 0 END) Completed, COUNT(*) Total
from tasks 
WHERE created_at > CURRENT_DATE - INTERVAL '15 day' 
AND created_at < CURRENT_DATE - INTERVAL '5 day'

CodePudding user response：

Here is basically your query (DRY-ed, half of it) that uses aggregation with FILTER.

select
    (count(*) FILTER (where completed))::numeric / count(*) as completion_rate
from tasks 
where created_at > current_date - interval '15 day' 
  and created_at < current_date - interval '5 day';