Add-to-Cart to Checkout Conversion | Dataford Interview Questions

Your question is Add-to-Cart to Checkout Conversion. 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

Amazon.com wants to compare shopping-funnel performance across marketplaces. Write a PostgreSQL query that calculates the conversion rate from add-to-cart to checkout for sessions recorded during January 1 through January 2, 2025.

A session counts in the denominator when it contains an add_to_cart event. It counts as converted only when the same session contains a checkout event after the add-to-cart event and within 24 hours.

Requirements

Aggregate events at the user and session level before calculating conversion.
Report add-to-cart sessions, converted sessions, and conversion rate percentage by marketplace.
Include marketplaces with customers but no qualifying add-to-cart sessions, returning a NULL rate when the denominator is zero.
Exclude events for customer IDs that are not present in customers and avoid division by zero.

Schema

customers

Column	Type	Description
customer_idPK	INT	Amazon customer identifier
marketplace	VARCHAR(20)	Amazon marketplace code

funnel_events

Column	Type	Description
event_idPK	INT	Unique funnel event identifier
customer_id	INT	Customer associated with the event
session_id	VARCHAR(20)	Shopping session identifier
event_type	VARCHAR(20)	Funnel event name
event_ts	TIMESTAMP	Timestamp when the event occurred