sql-201.md

Get some high-level data on our sends table.

select
    min(sent_on) as first_date,
    max(sent_on) as latest_date,
    count(*) as total_records,
    count(distinct template) as unique_templates,
    count(distinct sent_on) as unique_dates
from
    email.sends
limit 10
;

What dates did we send emails on? How many emails per day?

select
  sent_on as date_sent,
  count(*) as num_emails_sent
from
  email.sends
group by
    1
order by
    2 desc
;

Multiple different ways to cast a timestamp to a date.

select
  created,
  date(created),
  created::date,
  cast(created as date)
from some_table

limit 100

Group by a column that we've casted to a date.

select
  date(created),
  count(*)
from some_table
where created > '2016-01-01'
group by 1
order by 2 desc

limit 100
;

Query the results of a query (this is called using a subquery).

select
    *
from
(
    select
      sends.*,
      case
        when send_id is null then false
        else true
        end as was_opened,
      opened_on - sent_on as days_til_open
    from
      email.sends
      left join
        email.opens
        on sends.id = opens.send_id
) as email_information
where
    days_til_open > 6
limit 100;

Or, use with to create a temporary table for cleaner querying later:

with

email_information as (
    select
      sends.*,
      case
        when send_id is null then false
        else true
        end as was_opened,
      opened_on - sent_on as days_til_open
    from
      email.sends
      left join
        email.opens
        on sends.id = opens.send_id
)


select * from email_information
where days_til_open > 6
limit 100;

Group our results by template and calculate open rate in a few different ways:

with

email_information as (
    select
      sends.*,
      case
        when send_id is null then false
        else true
        end as was_opened,
      opened_on - sent_on as days_til_open
    from
      email.sends
      left join
        email.opens
        on sends.id = opens.send_id
),

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

select
    *,
    num_opens * 1.0 / num_sends as open_rate,
    num_opens::numeric / num_sends as open_rate_2,
    cast(num_opens as numeric) / num_sends as open_rate_3
from template_data
limit 100;

Round our open rate results to 2 decimal places:

with

email_information as (
    select
      sends.*,
      case
        when send_id is null then false
        else true
        end as was_opened,
      opened_on - sent_on as days_til_open
    from
      email.sends
      left join
        email.opens
        on sends.id = opens.send_id
),

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

select
    *,
    round(num_opens::numeric / num_sends, 2) as open_rate
from template_data
order by open_rate desc
limit 100;