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