Reporting Dashboard

Tutorial 2 of 5

This tutorial builds a sales pipeline and slices it with aggregation, window functions, ROLLUP/CUBE for subtotals, and reusable views. You will rank reps, compute running totals, and generate summary rows—all in pure SQL.

1. Schema and seed data

CREATE TABLE reps (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    region TEXT NOT NULL
);

CREATE TABLE deals (
    id       SERIAL PRIMARY KEY,
    rep_id   INT NOT NULL REFERENCES reps(id),
    customer TEXT NOT NULL,
    amount   INT NOT NULL,
    stage    TEXT NOT NULL DEFAULT 'prospect',
    closed   DATE
);

INSERT INTO reps (name, region) VALUES
    ('Alice',  'West'),
    ('Bob',    'East'),
    ('Carol',  'West'),
    ('Dave',   'East'),
    ('Eve',    'Central');

INSERT INTO deals (rep_id, customer, amount, stage, closed) VALUES
    (1, 'Acme Corp',       45000,  'won',      '2025-01-10'),
    (1, 'Globex',          30000,  'won',      '2025-01-18'),
    (1, 'Initech',         20000,  'prospect',  NULL),
    (2, 'Umbrella',        55000,  'won',      '2025-01-05'),
    (2, 'Stark Ind',       40000,  'lost',      NULL),
    (3, 'Wayne Ent',       60000,  'won',      '2025-01-22'),
    (3, 'Oscorp',          35000,  'prospect',  NULL),
    (4, 'LexCorp',         25000,  'won',      '2025-01-15'),
    (4, 'Cyberdyne',       70000,  'won',      '2025-01-28'),
    (5, 'Aperture',        50000,  'won',      '2025-01-12'),
    (5, 'Weyland-Yutani',  15000,  'lost',      NULL);

2. Revenue by rep

Total closed revenue per rep, filtering to won deals only.

SELECT r.name,
       COUNT(*) AS deals_won,
       SUM(d.amount) AS revenue
FROM deals d
JOIN reps r ON d.rep_id = r.id
WHERE d.stage = 'won'
GROUP BY r.name
ORDER BY revenue DESC;

3. Filter with HAVING

Only show reps with total pipeline (all stages) above 60k.

SELECT r.name,
       SUM(d.amount) AS pipeline
FROM deals d
JOIN reps r ON d.rep_id = r.id
GROUP BY r.name
HAVING SUM(d.amount) > 60000
ORDER BY pipeline DESC;

4. Rank reps by revenue

Use RANK() to rank reps within each region by closed revenue.

SELECT r.name, r.region,
       SUM(d.amount) AS revenue,
       RANK() OVER (
         PARTITION BY r.region
         ORDER BY SUM(d.amount) DESC
       ) AS rnk
FROM deals d
JOIN reps r ON d.rep_id = r.id
WHERE d.stage = 'won'
GROUP BY r.name, r.region;

5. Running revenue total

Compute a running total of closed deals ordered by close date, using a window frame.

SELECT d.closed,
       r.name,
       d.amount,
       SUM(d.amount) OVER (
         ORDER BY d.closed
         ROWS BETWEEN UNBOUNDED
           PRECEDING AND CURRENT ROW
       ) AS running_total
FROM deals d
JOIN reps r ON d.rep_id = r.id
WHERE d.stage = 'won'
ORDER BY d.closed;

6. Subtotals with ROLLUP

GROUP BY ROLLUP generates subtotal rows automatically—one per region, plus a grand total.

SELECT COALESCE(r.region, 'TOTAL')
         AS region,
       COALESCE(r.name, 'Subtotal')
         AS rep,
       SUM(d.amount) AS revenue
FROM deals d
JOIN reps r ON d.rep_id = r.id
WHERE d.stage = 'won'
GROUP BY ROLLUP(r.region, r.name)
ORDER BY r.region, r.name;

7. Wrap it in a view

Create a reusable view for the pipeline summary so any dashboard query can reference it directly.

CREATE VIEW pipeline_summary AS
SELECT r.name AS rep,
       r.region,
       COUNT(*) FILTER (
         WHERE d.stage = 'won'
       ) AS won,
       SUM(d.amount) AS pipeline,
       SUM(CASE WHEN d.stage = 'won'
           THEN d.amount ELSE 0
       END) AS revenue
FROM deals d
JOIN reps r ON d.rep_id = r.id
GROUP BY r.name, r.region;

SELECT * FROM pipeline_summary
ORDER BY revenue DESC;

What you learned

• GROUP BY + HAVING for filtered aggregation
• RANK() OVER (PARTITION BY ...) for per-group rankings
• Window frames (ROWS BETWEEN) for running totals
• ROLLUP for automatic subtotal and grand-total rows
• CASE WHEN inside aggregates for conditional sums
• CREATE VIEW to encapsulate complex queries