Ensure process integrity and surface issues early using these Trino queries
This set helps QA professionals track trends, validate user inputs, detect out-of-range data, and highlight risk areas in the field data lifecycle.
1️⃣ Average Field Value by User
What it does:
Shows average numeric input (e.g., torque) per user for each QA field.
SELECT
f."title" AS "field_name",
s."userid",
u."displayname" AS "user_displayname",
AVG(s."valuenumber") AS "avg_value",
MIN(s."valuenumber") AS "min_value",
MAX(s."valuenumber") AS "max_value"
FROM
"sessioncustomfieldview" s
JOIN
"field" f
ON
s."fieldid" = f."_id"
LEFT JOIN
"usermodel" u
ON
s."userid" = u."_id"
WHERE
s."valuenumber" IS NOT NULL
GROUP BY
f."title", s."userid", u."displayname"
ORDER BY
"avg_value" DESC
2️⃣ Field Values with Min/Max Limits
What it does:
Displays all fields that have both a defined min and max value.
SELECT
f."_id" AS "field_id",
f."title",
f."min",
f."max"
FROM
"field" f
WHERE
f."min" IS NOT NULL
AND f."max" IS NOT NULL
ORDER BY
f."title"
3️⃣ Most Used QA Fields
What it does:
Ranks custom fields by how often they are used.
SELECT
f."title",
COUNT(*) AS "entry_count"
FROM
"sessioncustomfieldview" s
JOIN
"field" f
ON
s."fieldid" = f."_id"
GROUP BY
f."title"
ORDER BY
"entry_count" DESC
4️⃣ Last Input per Field
What it does:
When was each QA field last used?
SELECT
f."title",
MAX(s."date") AS "last_entry"
FROM
"sessioncustomfieldview" s
JOIN
"field" f
ON
s."fieldid" = f."_id"
GROUP BY
f."title"
ORDER BY
"last_entry" DESC
5️⃣ Weekly Trends for a QA Field
What it does:
Tracks average value over time (filterable per field).
SELECT
date_trunc ('week', s."date") AS "week",
f."title",
AVG(s."valuenumber") AS "weekly_avg"
FROM
"sessioncustomfieldview" s
JOIN "field" f ON s."fieldid" = f."_id"
WHERE
s."valuenumber" IS NOT NULL
GROUP BY
date_trunc ('week', s."date"),
f."title"
ORDER BY
"week" DESC
6️⃣ Form Submissions by User
What it does:
Count how many QA forms each user submitted.
SELECT
fd."createdby",
u."displayname" AS "user_displayname",
COUNT(*) AS "qa_forms"
FROM
"formdata" fd
LEFT JOIN
"usermodel" u
ON
fd."createdby" = u."_id"
GROUP BY
fd."createdby", u."displayname"
ORDER BY
"qa_forms" DESC
7️⃣ QA Input Distribution by Workflow
What it does:
How QA data varies across workflows (e.g., torque in Assembly vs. Repair).
SELECT
f."title" AS "field",
w."workflowname",
AVG(s."valuenumber") AS "avg_value"
FROM
"sessioncustomfieldview" s
JOIN
"field" f
ON
s."fieldid" = f."_id"
JOIN
"workflowsessionitem" w
ON
s."workflowsessionitemid" = w."_id"
WHERE
s."valuenumber" IS NOT NULL
GROUP BY
f."title", w."workflowname"
ORDER BY
"avg_value" DESC
8️⃣ Rework-Related Input Events
What it does:
Find QA entries tied to sessions with rework or resubmission (UNSIGN present).
SELECT DISTINCT
f."title",
s."userid",
u."displayname" AS "user_displayname",
s."valuenumber",
s."valuestring",
s."valuedate",
s."valueboolean",
s."date",
s."workflowsessionitemid" AS "session_id",
w."workorderid",
wo."workordernumber"
FROM
"sessioncustomfieldview" s
JOIN
"field" f
ON
s."fieldid" = f."_id"
JOIN
"workflowsessionitemrecord" r
ON
s."workflowsessionitemid" = r."workflowsessionitemid"
JOIN
"workflowsessionitem" w
ON
s."workflowsessionitemid" = w."_id"
LEFT JOIN
"workorder" wo
ON
w."workorderid" = wo."_id"
LEFT JOIN
"usermodel" u
ON
s."userid" = u."_id"
WHERE
r."reason" = 'UNSIGN'
ORDER BY
s."date" DESC
9️⃣ Missing QA Field Detection
What it does:
Find sessions where a required field was never entered.
SELECT
w."_id" AS "session_id",
f."title" AS "missing_field"
FROM
"field" f
JOIN
"workflowsessionitem" w
ON
f."required" = TRUE
WHERE NOT EXISTS (
SELECT 1
FROM "sessioncustomfieldview" s
WHERE s."fieldid" = f."_id"
AND s."workflowsessionitemid" = w."_id"
)