You are working with a B2B sales company that tracks deals closed by its sales team. The sales manager wants a quick summary of how much revenue each salesperson has generated in total, so they can evaluate individual performance and plan commission payouts accordingly. The database contains two tables: salespeople — which stores basic information about each sales representative, including salesperson_id , name , and region ; and deals — which records every closed deal with columns deal_id , salesperson_id , deal_value , and closed_at . Each row in deals represents one successfully closed sale attributed to a specific salesperson. Your task is to write a PostgreSQL query that calculates the total revenue generated by each salesperson across all their closed deals. The result should include the salesperson's salesperson_id , their name , and the sum of all their deal values aliased as total_revenue . Results should be ordered by total_revenue in descending order so the top performers appear first. Join the salespeople and deals tables on salesperson_id . Group results by salesperson_id and name . Use SUM() to calculate each salesperson's total revenue. Alias the aggregated column as total_revenue . Order by total_revenue DESC. Only include salespeople who have at least one closed deal. Hint: Since you are using an INNER JOIN, salespeople with no deals will automatically be excluded from the results — this is the desired behaviour here. A common pitfall is forgetting to include name in the GROUP BY clause alongside salesperson_id ; because name appears in the SELECT list but is not aggregated, PostgreSQL requires it to be explicitly listed in GROUP BY .

Table Setup (SQL Schema)

CREATE TABLE salespeople (
  salesperson_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  region VARCHAR(50) NOT NULL
);

CREATE TABLE deals (
  deal_id SERIAL PRIMARY KEY,
  salesperson_id INT NOT NULL REFERENCES salespeople(salesperson_id),
  deal_value NUMERIC(10, 2) NOT NULL,
  closed_at DATE NOT NULL
);

INSERT INTO salespeople (name, region) VALUES
  ('Sarah Connor', 'North'),
  ('James Novak', 'South'),
  ('Priya Patel', 'East'),
  ('Tom Nguyen', 'West'),
  ('Laura Kim', 'North'),
  ('Marcus Reid', 'South');

INSERT INTO deals (salesperson_id, deal_value, closed_at) VALUES
  (1, 12000.00, '2024-01-15'),
  (1, 8500.00,  '2024-02-10'),
  (1, 22000.00, '2024-03-05'),
  (2, 15000.00, '2024-01-20'),
  (2, 9800.00,  '2024-02-28'),
  (3, 31000.00, '2024-01-11'),
  (3, 4200.00,  '2024-02-14'),
  (3, 17500.00, '2024-03-22'),
  (4, 6000.00,  '2024-02-01'),
  (4, 11000.00, '2024-03-18'),
  (5, 28000.00, '2024-01-30'),
  (5, 5500.00,  '2024-03-10');