Compare Segment Performance Across Channels | Dataford Interview Questions

You are given customer, order, and channel activity data from DISH digital touchpoints. Write a PostgreSQL query that compares customer behavior across customer segments and acquisition channels for Q1 2024. Return one row per segment and channel_name, including the number of customers in that segment-channel combination, how many placed at least one completed order in Q1, total completed order revenue, average completed order value, and the conversion rate as the share of customers with at least one completed order. Only include segment-channel groups with at least 2 customers, and sort the results by conversion rate descending, then total revenue descending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
segment	VARCHAR(50)	Customer segment classification
signup_date	DATE	Date the customer signed up

channel_events

Column	Type	Description
event_idPK	INT	Unique channel event identifier
customer_id	INT	Customer associated with the event
channel_name	VARCHAR(50)	Channel where the customer interacted
event_date	DATE	Date of the channel event

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_amount	DECIMAL(10,2)	Order amount

Your question is Compare Segment Performance Across Channels. Start with the requirements and the three tables in the Question tab.

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

Problem