dbdbexercises
Start free
← Problems|

6. Monthly Booked vs Collected Revenue

MediumJoinsPostgreSQL 18
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: payments
+-------------+---------------+
| Column Name | Type          |
+-------------+---------------+
| payment_id  | int           |
| order_id    | int           |
| paid_at     | date          |
| amount      | numeric(10,2) |
| method      | varchar(20)   |
+-------------+---------------+

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

At month-end close, finance compares what was sold with what was actually paid. Some customers pay in instalments, so one order can have several payments, and some orders have not been paid at all. Last quarter's report showed more revenue than was ever invoiced, and nobody could say why.

Each row in orders is a sale with an order_total. Each row in payments is money received against an order. An order can have one, several, or no payments. Return one row per month, based on order_date: 1. order_month: the first day of the month, as a date (for example 2024-01-01) 2. orders: number of orders placed that month 3. booked_revenue: total order_total of those orders 4. collected: total payments received against those orders, whenever they were paid (0 if none) 5. outstanding: booked_revenue minus collected Order by order_month 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:

One row per order month with orders, booked_revenue, collected and outstanding, counting every order once whether it has one, several, or no payments.

Constraints:
  • Query must utilize clause(s): GROUP 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

At month-end close, finance compares what was sold with what was actually paid. Some customers pay in instalments, so one order can have several payments, and some orders have not been paid at all. Last quarter's report showed more revenue than was ever invoiced, and nobody could say why.

Expected result: One row per order month with orders, booked_revenue, collected and outstanding, counting every order once whether it has one, several, or no payments.

What you will practice

GROUP BYSUM

Tables

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

  CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id),
    paid_at DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    method VARCHAR(20) NOT NULL
  );

More Joins practice

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

View topic