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

Net Realized Revenue & Promotional Discount Impact

Scenario

Finance and growth marketing are evaluating coupon campaign ROI. They need a breakdown of sales grouped by discount promotion codes to verify which codes generate substantial net sales after deducting promotional price cuts.

Task
Group records in `checkout_orders` by `promo_code`. Calculate: 1. `promo_code` 2. `order_count`: Total orders using that code 3. `gross_sales`: Sum of `gross_amount` 4. `total_discount`: Sum of `discount_amount` 5. `net_revenue`: Difference between gross sales and discount (`SUM(gross_amount - discount_amount)`) Filter using a `HAVING` clause to only keep promotions that generated at least **$500.00** in `net_revenue`. Order results by `net_revenue` descending.
Expected Output Format

Summary of promo code sales metrics with net revenue >= $500, sorted by highest net revenue.

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 “Net Realized Revenue & Promotional Discount Impact” SQL Problem

Business Scenario

Finance and growth marketing are evaluating coupon campaign ROI. They need a breakdown of sales grouped by discount promotion codes to verify which codes generate substantial net sales after deducting promotional price cuts.

Expected Output & Criteria

Summary of promo code sales metrics with net revenue >= $500, sorted by highest net revenue.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE checkout_orders (
        order_id INT PRIMARY KEY,
        promo_code VARCHAR(30) NOT NULL,
        gross_amount NUMERIC(10, 2) NOT NULL,
        discount_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00
      );

Target SQL Clauses & Operators

GROUP BYSUMHAVING

Want more Aggregations practice?

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

View Aggregations Track→