MySQL65 COUNT with IF clause

Description

Get all the email records from email table and filter the blacklist users by table user. Return the ratio of no_completed emails.

Thought

1. Use JOIN and WHERE clauses to filter the blacklist users.
2. Use GROUP BY and ORDER BY clauses to aggregate the data by date.
3. Use COUNT and IF clauses to get the number of no_completed emails, the syntax are below.
   count(filed > value or null), count(if(filed > value, 1, null)), count(case when filed > value then 1 end)
   4.control the decimal places by FORMAT

Code

SELECT date, FORMAT(COUNT(if(type="no_completed", 1,null))/COUNT(*), 3)
FROM email 
JOIN user AS u1 ON email.send_id = u1.id
JOIN user AS u2 ON email.receive_id = u2.id
WHERE u1.is_blacklist = 0 and u2.is_blacklist = 0
GROUP BY date
ORDER BY date