SQL for Weekly Operational Metrics

HardSQL · PostgreSQL

Problem

eBay Operations needs a weekly view of order fulfillment performance across its marketplace operations. Write a PostgreSQL query that combines orders, shipment records, and eBay customer support tickets without double-counting orders that have multiple tickets.

Requirements

Exclude cancelled orders and orders outside January 6 through January 26, 2025.
Group results by the Monday week containing each order date.
Return total orders, gross order value, shipped orders, support ticket count, and the percentage of delivered orders delivered on or before the promised date.
Preserve orders without shipment or support records, and return weeks in chronological order.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique eBay order identifier
buyer_id	VARCHAR(20)	eBay buyer identifier
order_date	DATE	Date the order was placed
promised_date	DATE	Promised delivery date
order_amount	NUMERIC(10,2)	Order value in dollars
order_status	VARCHAR(20)	Order lifecycle status

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
order_id	INT	Order associated with the shipment
shipped_date	DATE	Date the shipment left the fulfillment facility
delivered_date	DATE	Date delivered to the buyer
shipment_status	VARCHAR(20)	Shipment status
carrier	VARCHAR(30)	Shipping carrier

support_tickets

Column	Type	Description
ticket_idPK	INT	Unique support ticket identifier
order_id	INT	Order referenced by the ticket
created_at	TIMESTAMP	Ticket creation timestamp
resolution_status	VARCHAR(20)	Ticket resolution status
issue_type	VARCHAR(30)	Customer-reported issue category

Tablesordersshipmentssupport_tickets