Skip to main content

🏭 Production Manager SQL Toolkit

Alex Merkin avatar
Written by Alex Merkin
Updated over 2 weeks ago

Ready-to-use Trino queries for Next Plus (Metabase-ready)

Get deep visibility into production performance, identify bottlenecks, detect rework, and track outputs — all from your own data.

✅ Works in Trino v450
✅ Designed for Next Plus MongoDB schema
✅ No semicolons (Trino best practice)


1️⃣ Sessions per Workflow

What it does:
Counts how many workflow sessions were started per workflow process.

SELECT
"workflowname",
COUNT(*) AS "session_count"
FROM
"workflowsessionitem"
GROUP BY
"workflowname"
ORDER BY
"session_count" DESC

2️⃣ Valid vs. Invalid Quantity per Workflow

What it does:
Compares the amount of valid and invalid output per process.

SELECT
"workflowname",
SUM("validquantity") AS "valid",
SUM("invalidquantity") AS "invalid"
FROM
"workflowsessionitem"
GROUP BY
"workflowname"
ORDER BY
"invalid" DESC

3️⃣ Sessions Without Outputs

What it does:
Finds production sessions where no valid items were produced.

SELECT
wsi."_id",
wsi."workflowname",
wsi."userid",
u."displayname" AS "user_displayname",
wsi."start"
FROM
"workflowsessionitem" wsi
LEFT JOIN
"usermodel" u
ON
wsi."userid" = u."_id"
WHERE
wsi."validquantity" IS NULL OR wsi."validquantity" = 0
ORDER BY
wsi."start" DESC

4️⃣ Average Session Duration by Workflow

What it does:
Calculates how long sessions typically take.

SELECT
"workflowname",
ROUND(AVG("nettimespent"), 2) AS "avg_minutes",
ROUND(AVG("nettimespent") / 60, 2) AS "avg_hours",
ROUND(AVG("nettimespent") / 1440, 2) AS "avg_days"
FROM
"workflowsessionitem"
WHERE
"nettimespent" IS NOT NULL
GROUP BY
"workflowname"
ORDER BY
"avg_minutes" DESC

5️⃣ Sessions per Location

What it does:
Where are production sessions happening most?

SELECT
l."_id" AS "location_id",
l."name" AS "location_name",
COUNT(*) AS "session_count"
FROM
"workflowsessionitem" wsi
CROSS JOIN UNNEST(wsi."location") AS t(location_id)
LEFT JOIN "location" l
ON t.location_id = l."_id"
GROUP BY
l."_id", l."name"
ORDER BY
"session_count" DESC

6️⃣ Long Sessions Warning

What it does:
Identifies sessions taking longer than 60 minutes.

SELECT
wsi."_id",
wsi."workflowname",
wsi."userid",
u."displayname" AS "user_displayname",
wsi."nettimespent"
FROM
"workflowsessionitem" wsi
LEFT JOIN
"usermodel" u
ON
wsi."userid" = u."_id"
WHERE
wsi."nettimespent" > 60
ORDER BY
wsi."nettimespent" DESC

7️⃣ First Time Pass Rate

What it does:
Percent of sessions with 100% valid items.

SELECT
"workflowname",
ROUND(COUNT(*) FILTER (WHERE "invalidquantity" = 0) * 100.0 / COUNT(*), 2) AS "first_pass_percent"
FROM
"workflowsessionitem"
GROUP BY
"workflowname"
ORDER BY
"first_pass_percent" DESC

8️⃣ Daily Throughput

What it does:
Units produced per day across all workflows.

SELECT
DATE("created") AS "day",
"sku",
SUM("validquantity") AS "units_produced"
FROM
"stock"
WHERE
"validquantity" IS NOT NULL
GROUP BY
DATE("created"),
"sku"
ORDER BY
"day" DESC,
"units_produced" DESC

9️⃣ Production Output per Work Order

What it does:
How many valid units were produced for each WO.

SELECT
wsi."workorderid",
wo."workordernumber",
SUM(wsi."validquantity") AS "total_valid"
FROM
"workflowsessionitem" wsi
LEFT JOIN
"workorder" wo
ON
wsi."workorderid" = wo."_id"
GROUP BY
wsi."workorderid", wo."workordernumber"
ORDER BY
"total_valid" DESC

🔟 Work Orders with No Output

What it does:
Finds work orders with no valid stock associated.

SELECT
wo."_id",
wo."sku",
wo."statusname",
wo."createdat"
FROM
"workorder" wo
WHERE
wo."closed" = false
AND wo."_id" NOT IN (
SELECT DISTINCT "workorderid"
FROM "stock"
WHERE "validquantity" > 0
)
ORDER BY
wo."createdat" DESC

1️⃣1️⃣ Time per Step in Workflow

What it does:
Avg time spent per workflow node (step).

SELECT
wsir."nodeid",
wn."name" AS "node_name",
w."name" AS "workflow_name",
ROUND(AVG(wsir."sessionslength"), 2) AS "avg_step_minutes"
FROM
"workflowsessionitemrecord" wsir
LEFT JOIN
"workflownodesview" wn
ON
wsir."nodeid" = wn."id"
LEFT JOIN
"workflow" w
ON
wsir."workflowid" = w."_id"
WHERE
wsir."sessionslength" IS NOT NULL
GROUP BY
wsir."nodeid", wn."name", w."name"
ORDER BY
"avg_step_minutes" DESC

1️⃣2️⃣ High Invalid Quantity Sessions

What it does:
Flags sessions where invalid output is significant.

SELECT
wsi."_id",
wsi."workflowname",
wsi."userid",
u."displayname" AS "user_displayname",
wsi."validquantity",
wsi."invalidquantity"
FROM
"workflowsessionitem" wsi
LEFT JOIN
"usermodel" u
ON
wsi."userid" = u."_id"
WHERE
wsi."invalidquantity" > 0
ORDER BY
wsi."invalidquantity" DESC

1️⃣3️⃣ Median Session Duration per Workflow

What it does:
Gives more balanced view than average.

SELECT
"workflowname",
approx_percentile("nettimespent", 0.5) AS "median_duration",
MIN("nettimespent") AS "min_duration",
MAX("nettimespent") AS "max_duration"
FROM
"workflowsessionitem"
WHERE
"nettimespent" IS NOT NULL
GROUP BY
"workflowname"
ORDER BY
"median_duration" DESC

1️⃣4️⃣ Sessions with Missing Stock Link

What it does:
Detects where a session might be missing a stock trail.

SELECT
w."_id" AS "session_id",
w."workflowname",
w."userid",
u."displayname" AS "user_displayname",
w."workorderid",
wo."workordernumber"
FROM
"workflowsessionitem" w
LEFT JOIN
"sessionstockview" s
ON
w."_id" = s."workflowsessionitemid"
LEFT JOIN
"usermodel" u
ON
w."userid" = u."_id"
LEFT JOIN
"workorder" wo
ON
w."workorderid" = wo."_id"
WHERE
s."stockid" IS NULL

1️⃣5️⃣ Rework Rate (UNSIGN Sessions)

What it does:
How often steps were unsigned per workflow.

SELECT
wsir."workflowid",
w."name" AS "workflow_name",
wsir."workflowsessionitemid" AS "session_id",
COUNT(*) FILTER (WHERE wsir."reason" = 'UNSIGN') AS "unsign_events",
COUNT(*) AS "total_steps",
ROUND(100.0 * COUNT(*) FILTER (WHERE wsir."reason" = 'UNSIGN') / COUNT(*), 2) AS "rework_percent"
FROM
"workflowsessionitemrecord" wsir
LEFT JOIN
"workflow" w
ON
wsir."workflowid" = w."_id"
GROUP BY
wsir."workflowid", w."name", wsir."workflowsessionitemid"
ORDER BY
"rework_percent" DESC
Did this answer your question?