dbdbexercises
Practice Free
← Syllabus|
Medium

13. Marketplace Merchant 48-Hour Fulfillment SLA Rate

MediumAggregationsPostgreSQL 16PRO
Table: merchant_orders
+-------------------+-------------+
| Column Name       | Type        |
+-------------------+-------------+
| order_id          | varchar(20) |
| merchant_id       | int         |
| hours_to_dispatch | int         |
| order_amount      | numeric(10  |
+-------------------+-------------+

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

Operations leadership benchmarks third-party seller logistics by measuring the percentage of merchant orders dispatched within their contractual 48-hour service level agreement (SLA).

Group records in `merchant_orders` by `merchant_id`. Calculate: 1. `merchant_id` 2. `total_orders`: Count of all orders 3. `on_time_orders`: Count of orders where `hours_to_dispatch <= 48` 4. `sla_compliance_pct`: Percentage of orders dispatched within 48 hours, rounded to 1 decimal place Filter groups with `HAVING COUNT(*) >= 3` (minimum volume). Order results by `sla_compliance_pct` DESC, then by `merchant_id` ASC.

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 fulfillment SLA compliance rates for active sellers.

Constraints:
  • Query must utilize clause(s): GROUP BYCASEHAVINGROUND
  • 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 “Marketplace Merchant 48-Hour Fulfillment SLA Rate” SQL Problem

Business Scenario

Operations leadership benchmarks third-party seller logistics by measuring the percentage of merchant orders dispatched within their contractual 48-hour service level agreement (SLA).

Expected Output & Criteria

Merchant fulfillment SLA compliance rates for active sellers.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE merchant_orders (
        order_id VARCHAR(20) PRIMARY KEY,
        merchant_id INT NOT NULL,
        hours_to_dispatch INT NOT NULL,
        order_amount NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYCASEHAVINGROUND

Want more Aggregations practice?

Explore the full syllabus of 16 exercises and syntax guides.

View Aggregations Track