Drill 3 of 12 in Marketing questions
0

Interview Question Bank · Derived denominator · date range · decimal division

3. Email open, click and unsubscribe rates

EasyMarketingQuestion #171

Concept: Derived denominator · date range · decimal divisionshow

Email rates are measured against delivered messages, not everything you tried to send: delivered = recipients - bounces. Compute the denominator once in the expression and divide each count by it with decimal math. For a quarter on a DATE column, >= the first day and < the first day of the next quarter catches every day, including the last one.

Example (different data):

SELECT title,
  seats - no_shows AS attended,
  ROUND(100.0 * rated / (seats - no_shows), 1) AS rating_rate_pct
FROM webinars
WHERE held_on >= '2025-07-01' AND held_on < '2025-10-01'
ORDER BY rating_rate_pct DESC, title;

Your task

Larkspur Roasters' lifecycle team is writing its Q1 2025 email review and wants the core engagement rates for every send that quarter.

For emails sent in Q1 2025 (2025-01-01 through 2025-03-31), return name, delivered (recipients - bounces), open_rate_pct and click_rate_pct (unique_opens and unique_clicks as a % of delivered, rounded to 1 decimal) and unsubscribe_rate_pct (unsubscribes as a % of delivered, rounded to 2 decimals). Sort by open_rate_pct descending, then name.

Starting Postgres in your browser…

Checking your access…