Some SQL 201 tricks for parsing email data

sql_201.sql

with

    email_data as (
        select 
            s.id as send_id,
            s.template,
            s.sent_on,
            o.opened_on,
            case
                when opened_on is null then false
                else true
                end as was_opened,
            opened_on - sent_on as num_days
        from 
            email.sends s
            left join
                email.opens o
                on o.send_id = s.id
    ),
    
    email_aggregate as (
           select
                template,
                sum(
                    case
                        when was_opened is true then 1
                        else 0
                        end
                    ) as num_opens,
                count(*) as num_sends
            from 
                email_data
            group by template

    )


select
    *,
    num_opens / num_sends::numeric as open_rate,
    (round(num_opens / num_sends::numeric, 2) * 100)::text || ' %' as pretty_open_rate
from email_aggregate
limit 100
;