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.
NULL rate when the denominator is zero.customers and avoid division by zero.marketplace, add_to_cart_sessions, checkout_sessions, conversion_rate_pct.marketplace ascending, placing NULL values first.2025-01-01 00:00:00 and exclusive at 2025-01-03 00:00:00.add_to_cart timestamp and earliest checkout timestamp.add_to_cart and no later than 24 hours afterward.conversion_rate_pct to 2 decimal places.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Amazon customer identifier |
| marketplace | VARCHAR(20) | Amazon marketplace code |
| 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 |