DB Exercises
0
← Exercises/
Fundamentals•12 of 12
PrevNext
FundamentalsBeginnerPRO

Banking Dispute Escalation Routing

Scenario

A digital payments gateway handles transaction disputes. Compliance mandates assigning a routing priority: disputes over $1,000 or marked with 'unauthorized_charge' are 'CRITICAL'; disputes between $200 and $1,000 are 'HIGH'; all other disputes are 'STANDARD'.

Task
Assign each dispute in `dispute_tickets` a `priority_level` based on these rules: - If `disputed_amount >= 1000.00` OR `reason_code = 'unauthorized_charge'` -> `'CRITICAL'` - Else if `disputed_amount >= 200.00` -> `'HIGH'` - Else -> `'STANDARD'` Return: 1. `dispute_id` 2. `account_id` 3. `disputed_amount` 4. `reason_code` 5. `priority_level` Order by `disputed_amount` descending, then by `dispute_id` ascending.
Expected Output Format

Dispute tickets classified by escalation priority and sorted by disputed amount.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Banking Dispute Escalation Routing” SQL Problem

Business Scenario

A digital payments gateway handles transaction disputes. Compliance mandates assigning a routing priority: disputes over $1,000 or marked with 'unauthorized_charge' are 'CRITICAL'; disputes between $200 and $1,000 are 'HIGH'; all other disputes are 'STANDARD'.

Expected Output & Criteria

Dispute tickets classified by escalation priority and sorted by disputed amount.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE dispute_tickets (
        dispute_id VARCHAR(16) PRIMARY KEY,
        account_id INT NOT NULL,
        disputed_amount NUMERIC(10, 2) NOT NULL,
        reason_code VARCHAR(40) NOT NULL,
        filed_date DATE NOT NULL
      );

Target SQL Clauses & Operators

CASEWHENORDER BY

Want more Fundamentals practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Fundamentals Track→