dbdbexercises
Start free
← Problems|

5. Customer Revenue Without Double Counting

MediumJoinsPostgreSQL 18
Table: customers
+---------------+-------------+
| Column Name   | Type        |
+---------------+-------------+
| customer_id   | int         |
| customer_name | varchar(80) |
+---------------+-------------+

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

Table: orders
+-------------+---------------+
| Column Name | Type          |
+-------------+---------------+
| order_id    | int           |
| customer_id | int           |
| order_date  | date          |
| order_total | numeric(10,2) |
+-------------+---------------+

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

Table: order_items
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| item_id     | int         |
| order_id    | int         |
| sku         | varchar(20) |
| quantity    | int         |
+-------------+-------------+

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

Finance noticed that the revenue on a customer dashboard was higher than what was actually invoiced. The dashboard joins orders to their line items and then sums the order totals. They need a corrected report.

Each row in orders has an order_total. Each order has one or more rows in order_items, one per product, with a quantity. Return one row per customer who has placed at least one order: 1. customer_name 2. order_count: number of orders 3. revenue: sum of order_total across the customer's orders 4. units: total quantity across all items in the customer's orders Order by revenue from highest to lowest.

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:

One row per customer with order_count, revenue (each order total counted once), and total units across their order items.

Constraints:
  • Query must utilize clause(s): JOINGROUP BYSUM
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Database: PostgreSQL 18, running in your browser.

About this problem

The business question

Finance noticed that the revenue on a customer dashboard was higher than what was actually invoiced. The dashboard joins orders to their line items and then sums the order totals. They need a corrected report.

Expected result: One row per customer with order_count, revenue (each order total counted once), and total units across their order items.

What you will practice

JOINGROUP BYSUM

Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(80) NOT NULL
  );

  CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    order_total NUMERIC(10, 2) NOT NULL
  );

  CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id),
    sku VARCHAR(20) NOT NULL,
    quantity INT NOT NULL
  );

More Joins practice

20 problems in this topic, with key concepts and common mistakes.

View topic