DB Exercises
0
← Exercises/
Aggregations•4 of 12
PrevNext
AggregationsIntermediatePRO

Merchant Processing Volume & Chargeback Ratio

Scenario

Card network regulations (Visa/Mastercard) place merchants with elevated dispute rates on monitoring programs. The risk engineering team needs to aggregate transaction volumes and identify merchants with a dispute rate exceeding 1.0%.

Task
Group transactions in `merchant_transactions` by `merchant_id` and `merchant_name`. Calculate: 1. `merchant_id` 2. `merchant_name` 3. `total_transactions`: Count of all transactions 4. `total_settled_amount`: Sum of `amount_usd` 5. `dispute_count`: Sum of `is_disputed` (1 for disputed, 0 otherwise) 6. `dispute_rate_pct`: Percentage of transactions disputed: `ROUND(100.0 * SUM(is_disputed) / COUNT(*), 2)` Filter with a `HAVING` clause to only retain merchants where `dispute_rate_pct >= 1.00`. Order results by `dispute_rate_pct` descending.
Expected Output Format

High-risk merchants with dispute rate >= 1.0%, sorted by highest dispute rate.

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 “Merchant Processing Volume & Chargeback Ratio” SQL Problem

Business Scenario

Card network regulations (Visa/Mastercard) place merchants with elevated dispute rates on monitoring programs. The risk engineering team needs to aggregate transaction volumes and identify merchants with a dispute rate exceeding 1.0%.

Expected Output & Criteria

High-risk merchants with dispute rate >= 1.0%, sorted by highest dispute rate.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE merchant_transactions (
        txn_id VARCHAR(16) PRIMARY KEY,
        merchant_id INT NOT NULL,
        merchant_name VARCHAR(100) NOT NULL,
        amount_usd NUMERIC(10, 2) NOT NULL,
        is_disputed INT NOT NULL DEFAULT 0
      );

Target SQL Clauses & Operators

GROUP BYSUMCOUNTHAVING

Want more Aggregations practice?

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

View Aggregations Track→