SQL proof of work

DealFlow SQL

A small PostgreSQL database for a commercial property deal pipeline, built to show working SQL rather than describe it. Ten queries, from a plain SELECT to window functions. Every table on this page is the real output of the query printed directly above it, captured from a live run.

• PostgreSQL 16 in Docker
• 6 brokers
• 10 properties
• 15 deals
• 3 tables, 2 foreign keys, 3 indexes
• Generated 2026-07-29

The data is invented. Brokers, agencies, properties and values are synthetic seed data written for this project. No real company, person, client or transaction appears anywhere in it. Reproduce the whole thing with one command: ./run.sh

SELECT subject, status, deal_value_zar
FROM deals
WHERE status IN ('New', 'In review', 'Qualified')
  AND deal_value_zar > 0
ORDER BY deal_value_zar DESC;

SELECT subject, review_reason, deal_value_zar, received_at
FROM deals
WHERE needs_review = true
ORDER BY received_at DESC;

SELECT d.subject, p.sector, p.province, d.deal_value_zar
FROM deals d
JOIN properties p ON p.property_id = d.property_id
ORDER BY d.received_at;

SELECT d.message_id, b.full_name AS broker, b.agency,
       p.common_name AS property, d.deal_value_zar, d.status
FROM deals d
JOIN brokers b ON b.broker_id = d.broker_id
JOIN properties p ON p.property_id = d.property_id
ORDER BY d.message_id;

SELECT b.full_name, b.agency, b.is_active,
       count(d.deal_id) AS deal_count,
       coalesce(sum(d.deal_value_zar), 0) AS total_value_zar
FROM brokers b
LEFT JOIN deals d ON d.broker_id = b.broker_id
GROUP BY b.broker_id, b.full_name, b.agency, b.is_active
ORDER BY deal_count DESC, b.full_name;

SELECT p.sector,
       count(*) AS open_deals,
       sum(d.deal_value_zar) AS pipeline_zar,
       round(avg(d.deal_value_zar), 0) AS avg_deal_zar
FROM deals d
JOIN properties p ON p.property_id = d.property_id
WHERE d.status IN ('New', 'In review', 'Qualified')
GROUP BY p.sector
ORDER BY pipeline_zar DESC;

SELECT b.agency,
       count(*) AS open_deals,
       sum(d.deal_value_zar) AS pipeline_zar
FROM deals d
JOIN brokers b ON b.broker_id = d.broker_id
WHERE d.status IN ('New', 'In review', 'Qualified')
GROUP BY b.agency
HAVING sum(d.deal_value_zar) > 30000000
ORDER BY pipeline_zar DESC;

SELECT date_trunc('week', received_at)::date AS week_starting,
       count(*) AS deals_received,
       sum(deal_value_zar) AS value_received_zar
FROM deals
GROUP BY week_starting
ORDER BY week_starting;

SELECT p.common_name, p.sector, p.province
FROM properties p
WHERE NOT EXISTS (
  SELECT 1
  FROM deals d
  WHERE d.property_id = p.property_id
    AND d.status IN ('New', 'In review', 'Qualified')
)
ORDER BY p.sector, p.common_name;

SELECT rank() OVER (ORDER BY sum(d.deal_value_zar) DESC) AS rank,
       b.full_name,
       b.agency,
       sum(d.deal_value_zar) AS pipeline_zar,
       round(100.0 * sum(d.deal_value_zar)
             / sum(sum(d.deal_value_zar)) OVER (), 1) AS pct_of_book
FROM deals d
JOIN brokers b ON b.broker_id = d.broker_id
WHERE d.status IN ('New', 'In review', 'Qualified')
GROUP BY b.broker_id, b.full_name, b.agency
ORDER BY rank;