Rolling 7-Day Conversion SQL

Business context

Shopify Marketing analysts monitor the performance of the Shopify Email channel across consecutive days. The team needs a rolling conversion rate that remains accurate when a day has traffic but no conversions, or no traffic at all.

Task

Write a PostgreSQL query for the period from March 1 through March 10, 2025, using only the Shopify Email channel.

Requirements

1. Generate one output row for every calendar date, including dates with no sessions.
2. Calculate daily distinct sessions and daily distinct sessions with at least one completed order.
3. Calculate seven-day rolling totals using the current date and preceding six calendar days.
4. Return the rolling conversion rate as a percentage rounded to two decimals, with safe division when there are no sessions.
5. Prevent multiple orders from inflating session counts, and sort chronologically.

Output details

• Return columns in this order: session_date, daily_sessions, daily_conversions, rolling_sessions, rolling_conversions, rolling_conversion_rate_pct.
• Sort session_date in ascending order.
• Represent missing daily counts as 0, and return NULL for rolling_conversion_rate_pct when rolling_sessions is 0.