Proportion

Proportion widgets show parts of a whole: each row a slice, a step or a rectangle. Funnel, Treemap and Sunburst refuse a negative value when they run, naming the row.

Pie chart and Donut chart

Each row a slice of the whole; a donut is a pie with a hole.

Open orders by status:

SELECT status, count(*) AS orders
FROM work_orders
WHERE status NOT IN ('COMPLETED', 'CANCELLED')
  AND ($VAR.branch IS NULL OR branch = $VAR.branch::uuid)
GROUP BY status
ORDER BY orders DESC

Each slice is named by the label of its value in the column's presentation (In progress for IN_PROGRESS) and colored by that value's color, unless the query returns a _color. Without either, slices rotate through the palette.

Pie chart

PIE

Each row a slice of the whole.

Size: 4 × 4 of the 12-column grid, at least 3 × 3 · Also: events, export

SELECT * FROM (VALUES ('Online', 540, 'blue'), ('Stores', 320, 'emerald'), ('Phone', 90, 'amber')) AS t(channel, orders, _color)

Donut chart

DONUT

A pie with a hole - each row a slice of the whole.

Size: 4 × 4 of the 12-column grid, at least 3 × 3 · Also: events, export

SELECT * FROM (VALUES ('Open', 12), ('In progress', 7), ('Resolved', 43)) AS t(status, tickets)

Funnel

Steps in the query's order, each with its share of the first or the previous step.

Acme's quote conversion. The _step column only orders the rows; columns starting with _ are never shown:

SELECT * FROM (
  SELECT 1 AS _step, 'All quotes' AS stage, count(*) AS quotes FROM quotes
  UNION ALL
  SELECT 2, 'Sent', count(*) FILTER (WHERE status IN ('SENT', 'ACCEPTED', 'REJECTED')) FROM quotes
  UNION ALL
  SELECT 3, 'Accepted', count(*) FILTER (WHERE status = 'ACCEPTED') FROM quotes
) steps
ORDER BY _step

FUNNEL

Steps in the query's order, each with its share of the first or the previous step.

Size: 4 × 4 of the 12-column grid, at least 3 × 3 · Also: events, export

SELECT * FROM (VALUES (1, 'Visits', 1200), (2, 'Sign-ups', 480), (3, 'Orders', 210), (4, 'Paid', 160)) AS t(_n, step, people) ORDER BY _n

Treemap

Nested rectangles by levels, each sized by its value. A parent is the sum of its children, so values must add up: a sum or a count, never an average.

Stock value by branch, warehouse and part category:

SELECT b.name AS branch, w.name AS warehouse, p.category,
       sum(s.quantity * p.unit_cost) AS value
FROM stock s
JOIN parts p ON p.id = s.part
JOIN warehouses w ON w.id = s.warehouse
JOIN branches b ON b.id = w.branch
GROUP BY 1, 2, 3

TREEMAP

Nested rectangles by levels (branch → category → product), each sized by its value; a parent is the sum of its children.

Size: 6 × 4 of the 12-column grid, at least 3 × 3 · Also: events, export

SELECT * FROM (VALUES ('North', 'Drinks', 'Water', 120), ('North', 'Drinks', 'Soda', 90), ('North', 'Food', 'Bread', 60), ('South', 'Drinks', 'Water', 70), ('South', 'Food', 'Cheese', 110)) AS t(branch, category, product, sold)

Sunburst

The same as a treemap, as rings: the innermost ring is the first level. Installed equipment by category and status:

SELECT em.category, e.status, count(*) AS units
FROM equipment e
JOIN equipment_models em ON em.id = e.model
WHERE e.status <> 'RETIRED'
GROUP BY 1, 2

Its settings: Show labels, Format, Compact number, Color column and On click.

SUNBURST

Rings by levels, the innermost the first; a parent is the sum of its children.

Size: 4 × 4 of the 12-column grid, at least 3 × 3 · Also: events, export

SELECT * FROM (VALUES ('North', 'Drinks', 'Water', 120), ('North', 'Drinks', 'Soda', 90), ('North', 'Food', 'Bread', 60), ('South', 'Drinks', 'Water', 70), ('South', 'Food', 'Cheese', 110)) AS t(branch, category, product, sold)

Next steps

• Distribution and Relation: heatmaps and scatter.
• SQL Conventions: coloring slices from the query.