select date, round(sum(cnt) / count(*), 3) as p from(
    select send_id, tb1.is_blacklist as send_id_check, receive_id, tb2.is_blacklist as 
    receive_id_check, type, date, case when type = 'completed' then 0 else 1 end as cnt
    from email
    join user as tb1 on email.send_id = tb1.id
    join user as tb2 on email.receive_id = tb2.id
    where tb1.is_blacklist = 0 and tb2.is_blacklist = 0
) as tb3
group by date
order by date