dbdbexercises
Start free
← Problems|

4. Closed-Won Revenue for Every Sales Rep

MediumJoinsPostgreSQL 18
Table: sales_reps
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| rep_id      | int         |
| rep_name    | varchar(80) |
| region      | varchar(30) |
+-------------+-------------+

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

Table: deals
+-------------+---------------+
| Column Name | Type          |
+-------------+---------------+
| deal_id     | int           |
| rep_id      | int           |
| stage       | varchar(20)   |
| amount      | numeric(12,2) |
| closed_at   | date          |
+-------------+---------------+

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

The VP of Sales is preparing Q1 2024 performance reviews and needs one line per rep. Reps who closed nothing must still appear on the report with zero, because those are exactly the conversations the VP needs to have.

Sales leadership wants Q1 2024 closed-won revenue for every rep in sales_reps, including reps who closed nothing. Count a deal from deals only if its stage is 'closed_won' and its closed_at date is between 2024-01-01 and 2024-03-31 (inclusive). Return: 1. rep_name 2. region 3. closed_won_revenue: total amount of the rep's qualifying deals, or 0 if there are none Order by closed_won_revenue from highest to lowest, then by rep_name 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 sales rep with rep_name, region, and Q1 2024 closed-won revenue, showing 0 for reps with no qualifying deals.

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

The VP of Sales is preparing Q1 2024 performance reviews and needs one line per rep. Reps who closed nothing must still appear on the report with zero, because those are exactly the conversations the VP needs to have.

Expected result: One row per sales rep with rep_name, region, and Q1 2024 closed-won revenue, showing 0 for reps with no qualifying deals.

What you will practice

LEFT JOINCOALESCE

Tables

CREATE TABLE sales_reps (
    rep_id INT PRIMARY KEY,
    rep_name VARCHAR(80) NOT NULL,
    region VARCHAR(30) NOT NULL
  );

  CREATE TABLE deals (
    deal_id INT PRIMARY KEY,
    rep_id INT NOT NULL REFERENCES sales_reps(rep_id),
    stage VARCHAR(20) NOT NULL,
    amount NUMERIC(12, 2) NOT NULL,
    closed_at DATE
  );

More Joins practice

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

View topic