Aggregate Sales With Time Zones | Dataford Interview Questions

Write a query to aggregate sales data while handling time-zone complexities at Wayfair.

Use the provided sales and fulfillment-center data. Include completed sales with non-null amounts and convert each sale to the local calendar date of its fulfillment center.

Output

One row per fulfillment center and local sale date.
Columns: center_name, local_sale_date, order_count, and gross_sales.
Exclude sales without a matching fulfillment center, a valid time zone, or a qualifying amount and status.
Order by local_sale_date, then center_name.

Schema

sales_orders

Column	Type	Description
order_idPK	INT	Unique sales order identifier
center_id	INT	Fulfillment center associated with the sale
occurred_at	TIMESTAMPTZ	Sale timestamp stored with its absolute time-zone offset
status	VARCHAR(20)	Current order status
sale_amount	NUMERIC(10,2)	Gross amount for the sale

fulfillment_centers

Column	Type	Description
center_idPK	INT	Unique fulfillment center identifier
center_name	VARCHAR(100)	Fulfillment center name
time_zone	VARCHAR(64)	IANA time-zone name for the center

Your question is Aggregate Sales With Time Zones. Start with the requirements and the two 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