dbdbexercises
Practice Free
← Syllabus|
Hard

18. Collateral Risk Reserve Holdback for High-Chargeback Merchants

HardBusiness AnalyticsPostgreSQL 16PRO
Table: merchant_monthly_stats
+----------------------+-------------+
| Column Name          | Type        |
+----------------------+-------------+
| merchant_id          | varchar(16) |
| gross_volume_usd     | numeric(12  |
| chargeback_ratio_pct | numeric(5   |
+----------------------+-------------+

merchant_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Payment gateways mitigate credit loss by automatically withholding rolling reserve holdbacks (between 5% and 15%) from merchant payouts when dispute ratios breach risk corridors.

For each merchant in `merchant_monthly_stats`, determine their `reserve_rate`: - If `chargeback_ratio_pct >= 2.0`, rate is `0.15` (15% holdback) - If `chargeback_ratio_pct >= 1.0`, rate is `0.08` (8% holdback) - Otherwise, rate is `0.00` (No reserve) Calculate `reserve_holdback_usd` as `ROUND(gross_volume_usd * reserve_rate, 2)`. Return: 1. `merchant_id` 2. `gross_volume_usd` 3. `chargeback_ratio_pct` 4. `reserve_rate` 5. `reserve_holdback_usd` Order results by `reserve_holdback_usd` descending, then by `merchant_id` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Merchant collateral reserve holdback calculation based on chargeback ratios.

Constraints:
  • Query must utilize clause(s): CASESUMROUND
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Collateral Risk Reserve Holdback for High-Chargeback Merchants” SQL Problem

Business Scenario

Payment gateways mitigate credit loss by automatically withholding rolling reserve holdbacks (between 5% and 15%) from merchant payouts when dispute ratios breach risk corridors.

Expected Output & Criteria

Merchant collateral reserve holdback calculation based on chargeback ratios.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE merchant_monthly_stats (
        merchant_id VARCHAR(16) PRIMARY KEY,
        gross_volume_usd NUMERIC(12, 2) NOT NULL,
        chargeback_ratio_pct NUMERIC(5, 2) NOT NULL
      );

Target SQL Clauses & Operators

CASESUMROUND

Want more Business Analytics practice?

Explore the full syllabus of 18 exercises and syntax guides.

View Business Analytics Track