Notes from SQL 201

sql_201.sql

-- let's check what our table looks like
-- without bringing in everything
select * from email.sends limit 10;
select * from email.opens limit 10;

-- how many records are in the table?
select count(*) 
from email.sends 
limit 10;

-- what templates do we even have?
-- 1. issue_followup
-- 2. order_confirmation
-- 3. marketing
select distinct template from email.sends limit 100;

-- how do those break down?
select
    template,
    count(*)
from
    email.sends
group by
    template
;

-- what does our data broadly look like?
select
    min(sent_on) as first_send_date,
    max(sent_on) as last_send_date,
    count(*) as total_records,
    count(distinct template) as unique_templates
from
    email.sends
;


-- let's join em
select * 
from
    email.sends
    join
        email.opens
        on sends.id = opens.send_id
limit 100;


-- let's join em
select
    sends.*,
    opens.opened_on
from
    email.sends
    left join
        email.opens
        on sends.id = opens.send_id
limit 100;


-- let's make a was_opened column
select
    s.*,
    o.opened_on,
    case
        when o.opened_on is null then false
        else true
        end as was_opened
from
    email.sends s
    left join
        email.opens o
        on s.id = o.send_id
limit 100;


-- how long did it take to open?
select
    s.*,
    o.opened_on,
    case
        when o.opened_on is null then false
        else true
        end as was_opened,
    o.opened_on - s.sent_on as days_to_open
from
    email.sends s
    left join
        email.opens o
        on s.id = o.send_id
limit 100;






-- let's query that query

select
    template,
    avg(days_to_open)
from 
(
    select
        s.*,
        o.opened_on,
        case
            when o.opened_on is null then false
            else true
            end as was_opened,
        o.opened_on - s.sent_on as days_to_open
    from
        email.sends s
        left join
            email.opens o
            on s.id = o.send_id
) x
group by template
limit 100;




-- let's query that query in a different way
with
email_data as (
    select
        s.*,
        o.opened_on,
        case
            when o.opened_on is null then false
            else true
            end as was_opened,
        o.opened_on - s.sent_on as days_to_open
    from
        email.sends s
        left join
            email.opens o
            on s.id = o.send_id
)


select
    template,
    avg(days_to_open)
from email_data
group by 
    template
limit 100;




-- let's calculate number of opens and sends!
with
email_data as (
    select
        s.*,
        o.opened_on,
        case
            when o.opened_on is null then false
            else true
            end as was_opened,
        o.opened_on - s.sent_on as days_to_open
    from
        email.sends s
        left join
            email.opens o
            on s.id = o.send_id
)


select
    template,
    sum(
        case
            when was_opened then 1
            else 0
            end
        ) as num_opens,
    count(*) as num_sends
from email_data
group by
    template
limit 100;



-- let's calculate number of opens and sends!
with
email_data as (
    select
        s.*,
        o.opened_on,
        case
            when o.opened_on is null then false
            else true
            end as was_opened,
        o.opened_on - s.sent_on as days_to_open
    from
        email.sends s
        left join
            email.opens o
            on s.id = o.send_id
),

agg as (
    select
        template,
        sum(
            case
                when was_opened then 1
                else 0
                end
            ) as num_opens,
        count(*) as num_sends
    from email_data
    group by
        template
)

select
    *,
    -- integer division
    num_opens / num_sends as open_rate,
    -- another option
    num_opens * 1.0 / num_sends as open_rate2,
    -- another option
    cast(num_opens as numeric) / num_sends as open_rate3,
    -- a final option!
    num_opens::numeric / num_sends as open_rate4
    
from agg
limit 100;



-- let's calculate open rate
with
email_data as (
    select
        s.*,
        o.opened_on,
        case
            when o.opened_on is null then false
            else true
            end as was_opened,
        o.opened_on - s.sent_on as days_to_open
    from
        email.sends s
        left join
            email.opens o
            on s.id = o.send_id
),

agg as (
    select
        template,
        sum(
            case
                when was_opened then 1
                else 0
                end
            ) as num_opens,
        count(*) as num_sends
    from email_data
    group by
        template
)

select
    *,
    round(num_opens * 1.0 / num_sends, 2) as open_rate,
    -- make it pretty (but don't normally do this)
    (round(num_opens * 1.0 / num_sends, 2)*100)::integer || ' %' as pretty_open_rate
from 
    agg
order by
    open_rate desc
limit 100;