dbdbexercises
Practice Free
← Syllabus|
Medium

14. International Card Authorization Decline Concentrations

MediumAggregationsPostgreSQL 16PRO
Table: card_authorizations
+-----------------+-------------+
| Column Name     | Type        |
+-----------------+-------------+
| auth_id         | varchar(20) |
| issuing_country | varchar(4)  |
| amount_usd      | numeric(10  |
| auth_status     | varchar(20) |
+-----------------+-------------+

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

Payment authorization routers detect geo-specific interchange outages by tracking card decline volumes and total blocked dollar value per issuing country.

Group records in `card_authorizations` by `issuing_country` where `auth_status = 'declined'`. Calculate: 1. `issuing_country` 2. `declined_count`: Number of declined authorizations 3. `declined_volume_usd`: Sum of `amount_usd` for declined authorizations Filter groups with `HAVING COUNT(*) >= 2`. Order results by `declined_volume_usd` descending, then by `issuing_country` 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:

Card decline count and aggregate blocked dollar amount by country.

Constraints:
  • Query must utilize clause(s): GROUP BYSUMCOUNTORDER BY
  • 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 “International Card Authorization Decline Concentrations” SQL Problem

Business Scenario

Payment authorization routers detect geo-specific interchange outages by tracking card decline volumes and total blocked dollar value per issuing country.

Expected Output & Criteria

Card decline count and aggregate blocked dollar amount by country.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE card_authorizations (
        auth_id VARCHAR(20) PRIMARY KEY,
        issuing_country VARCHAR(4) NOT NULL,
        amount_usd NUMERIC(10, 2) NOT NULL,
        auth_status VARCHAR(20) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYSUMCOUNTORDER BY

Want more Aggregations practice?

Explore the full syllabus of 16 exercises and syntax guides.

View Aggregations Track