SQL: Partner Order Volume Decline | Dataford Interview Questions

Your question is SQL: Partner Order Volume Decline. 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

Asana's Operations team wants to identify channel partners whose order activity is weakening. Write a PostgreSQL query that compares each partner's average monthly completed-order volume in the latest represented quarter with the preceding quarter.

Requirements

Use the latest completed order date in the data to determine the most recent represented quarter and the preceding quarter.
Count completed orders by partner and calendar month, including months with zero orders.
Return partners whose average monthly order volume declined by more than 20% between the two quarters.
Exclude partners whose earlier-quarter average is zero, and order results by percentage decline descending.

Schema

channel_partners

Column	Type	Description
partner_idPK	INT	Unique channel partner identifier
partner_name	VARCHAR(100)	Channel partner name
region	VARCHAR(50)	Partner operating region

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
partner_id	INT	References channel_partners.partner_id
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status