Rolling 7-Day Conversion SQL | Dataford Interview Questions

Your question is Rolling 7-Day Conversion SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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

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

Schema

shopify_marketing_sessions

Column	Type	Description
session_idPK	INT	Unique marketing session identifier
session_date	DATE	Date on which the session occurred
channel	VARCHAR(100)	Shopify Marketing attribution channel
campaign_name	VARCHAR(150)	Shopify Email campaign name

shopify_orders

Column	Type	Description
order_idPK	INT	Unique order identifier
session_id	INT	Marketing session attributed to the order
order_date	DATE	Date on which the order was placed
status	VARCHAR(30)	Order lifecycle status