Year-Over-Year Carrier Growth | Dataford Interview Questions

Your question is Year-Over-Year Carrier Growth. 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

Convoy's Carrier Dashboard tracks how quickly new carriers join the platform. Write a PostgreSQL query that calculates annual carrier sign-ups and their year-over-year growth rate.

Requirements

Count distinct eligible carriers by the calendar year of their first signup event.
Include only carriers whose account status is active or pending.
Return the signup year, current signup count, prior-year signup count, and growth rate as a percentage rounded to two decimal places. Return NULL for the first year or when the prior-year count is zero.
Order the results chronologically.

Schema

carrier_accounts

Column	Type	Description
carrier_idPK	INT	Unique carrier identifier
account_status	VARCHAR(20)	Current carrier account status
created_at	DATE	Date the carrier account was created

carrier_signups

Column	Type	Description
signup_idPK	INT	Unique signup event identifier
carrier_id	INT	Carrier associated with the signup
signed_up_at	TIMESTAMP	Timestamp of the signup event
acquisition_channel	VARCHAR(30)	Signup acquisition source