CASE WHEN Conditional Filtering | Dataford Interview Questions

Your question is CASE WHEN Conditional Filtering. Start with the requirements and the one table 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

Capgemini Invent uses a project intake dashboard to prioritize client requests. Write a PostgreSQL query that derives a decision with CASE WHEN and returns only requests requiring escalation.

Requirements

Classify each request as Exclude, Escalate, Review, or Standard using the stated business rules.
Return only requests classified as Escalate, ordered by request_id.

Use these rules in order:

Cancelled requests are Exclude.
Active or pending requests with a budget of at least 100000 EUR and a risk score of at least 7 are Escalate.
Requests with a budget of at least 50000 EUR or a risk score of at least 5 are Review.
All other requests are Standard.

Schema

client_requests

Column	Type	Description
request_idPK	INTEGER	Unique client request identifier
client_name	VARCHAR(100)	Client organization name
budget_eur	NUMERIC(12,2)	Proposed project budget in euros
risk_score	INTEGER	Risk score from 0 to 10
status	VARCHAR(20)	Current request status