SQL Conversion by Channel and Device | Dataford Interview Questions

Your question is SQL Conversion by Channel and Device. 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

PayPal Product Growth wants to compare how effectively acquisition channels convert users into completed PayPal Checkout sessions across device types. Write a PostgreSQL query for sessions acquired during January 2025.

Requirements

Group results by acquisition channel and device type, replacing NULL values with Unknown.
Count total acquisition sessions and sessions with at least one completed PayPal Checkout event during January 2025.
Calculate conversion rate as converted sessions divided by total sessions, expressed as a percentage rounded to two decimals.
Include groups with zero conversions and order results by channel, then device type.

Schema

acquisition_sessions

Column	Type	Description
session_idPK	INT	Unique acquisition session identifier
user_id	INT	PayPal user identifier
channel	VARCHAR(40)	Acquisition channel
device_type	VARCHAR(20)	Device used to start the session
started_at	TIMESTAMP	Session start timestamp

payment_events

Column	Type	Description
event_idPK	INT	Unique payment event identifier
session_id	INT	Acquisition session associated with the event
event_type	VARCHAR(30)	Payment funnel event type
event_at	TIMESTAMP	Payment event timestamp