Message Routing with Multiple Providers

MediumSQL · PostgreSQL

Problem

Machine coding: build a communication service handling multiple request types (Email, SMS, Soundbox) with per-type mandatory fields; providers support one or more channels, multiple providers per channel, providers have multiple accounts (e.g. for critical OTP traffic); randomly select one active eligible provider per request. Asked in the Machine coding stage.

Output

Return one row per valid request with request_id, request_type, account_id, and provider_name.
Exclude requests missing their type-specific mandatory field or lacking an eligible account.
Select only active providers and accounts, require critical OTP support when applicable, and order by request_id.

Schema

communication_requests

Column	Type	Description
request_idPK	INT	Communication request identifier
request_type	VARCHAR(20)	Requested channel: EMAIL, SMS, or SOUNDBOX
email_address	VARCHAR(255)	Required for email requests
phone_number	VARCHAR(30)	Required for SMS requests
soundbox_device_id	VARCHAR(50)	Required for Soundbox requests
is_critical_otp	BOOLEAN	Whether the request requires a critical OTP-capable account

providers

Column	Type	Description
provider_idPK	INT	Provider identifier
provider_name	VARCHAR(100)	Provider display name
supported_channels	TEXT[]	Channels supported by the provider
is_active	BOOLEAN	Whether the provider can receive new requests

provider_accounts

Column	Type	Description
account_idPK	INT	Provider account identifier
provider_id	INT	Referenced provider
account_name	VARCHAR(100)	Provider account label
is_active	BOOLEAN	Whether the account is available
supports_critical_otp	BOOLEAN	Whether the account supports critical OTP traffic

Tablescommunication_requestsprovidersprovider_accounts