DB Exercises
0
← Exercises/
Aggregations•2 of 12
PrevNext
AggregationsIntermediate

Gross Merchandise Value & Refund Rate

Scenario

E-commerce merchants frequently suffer from high return rates in certain product categories. The VP of Operations wants to audit total gross sales (GMV) and calculate the percentage of total sales refunded per category.

Task
Analyze order transactions in `orders` grouped by `category`. Calculate: 1. `category` 2. `total_orders`: Count of all orders in that category 3. `total_gmv`: Sum of `order_amount` 4. `refund_rate_pct`: Percentage of total order amount that was refunded, calculated as: `ROUND(100.0 * SUM(refunded_amount) / SUM(order_amount), 2)` Order results by `refund_rate_pct` in **descending** order.
Expected Output Format

Summary table of category, total_orders, total_gmv, and refund_rate_pct sorted by highest refund rate first.

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 “Gross Merchandise Value & Refund Rate” SQL Problem

Business Scenario

E-commerce merchants frequently suffer from high return rates in certain product categories. The VP of Operations wants to audit total gross sales (GMV) and calculate the percentage of total sales refunded per category.

Expected Output & Criteria

Summary table of category, total_orders, total_gmv, and refund_rate_pct sorted by highest refund rate first.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        category VARCHAR(50) NOT NULL,
        order_amount NUMERIC(10, 2) NOT NULL,
        refunded_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00
      );

Target SQL Clauses & Operators

GROUP BYSUMROUND

Want more Aggregations practice?

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

View Aggregations Track→