SQL Practice Pack

Get all documents (newest first)

SELECT *
FROM "Document"
ORDER BY "createdAt" DESC;

QA use: quickly inspect what artifacts exist and how your system is behaving.

Get documents by type

SELECT *
FROM "Document"
WHERE "type" = 'TEST_CASE_SET'
ORDER BY "createdAt" DESC;

QA use: find only test-case documents when debugging generation or export behavior.

Get accepted generated items

SELECT *
FROM "GeneratedItem"
WHERE "reviewStatus" = 'ACCEPTED'
ORDER BY "createdAt" DESC;

QA use: validate what will be exported and saved coverage for human-accepted artifacts.

Count pending items

SELECT COUNT(*)::int AS pendingItems
FROM "GeneratedItem"
WHERE "reviewStatus" = 'PENDING';

QA use: measure review backlog and generation throughput.

Find documents created by a specific user

SELECT *
FROM "Document"
WHERE "userId" = 'USER_ID_HERE'
ORDER BY "createdAt" DESC;

QA use: reproduce issues for a specific account and verify ownership rules.

Find documents without any accepted items

SELECT d.*
FROM "Document" d
WHERE NOT EXISTS (
  SELECT 1
  FROM "GeneratedItem" gi
  WHERE gi."documentId" = d."id"
    AND gi."reviewStatus" = 'ACCEPTED'
)
ORDER BY d."createdAt" DESC;

QA use: verify export behavior defaults to ACCEPTED and detect documents stuck in PENDING/REJECTED.

Get quality analysis scores (by document)

SELECT
  d."id" AS "documentId",
  d."title",
  d."type" AS "documentType",
  qa."overallScore",
  qa."createdAt" AS "analysisCreatedAt"
FROM "QualityAnalysis" qa
JOIN "Document" d ON d."id" = qa."documentId"
ORDER BY qa."createdAt" DESC;

QA use: audit model quality, compare scores across runs, and debug quality analysis.

Order documents by newest (explicit query)

SELECT "id", "title", "type", "createdAt"
FROM "Document"
ORDER BY "createdAt" DESC
LIMIT 20;

QA use: quickly locate the latest artifacts created during a test session.

Find guest usage history (limit / investigate)

SELECT
  ul.*,
  gs."sessionKey",
  gs."usageCount",
  gs."allowedFeatureUsed"
FROM "UsageLog" ul
LEFT JOIN "GuestSession" gs ON gs."id" = ul."guestSessionKey"
ORDER BY ul."createdAt" DESC
LIMIT 50;

QA use: verify guest lock-in and usage limiting behavior over time.

Exercise 1: find all bug report documents

SELECT *
FROM "Document"
WHERE "type" = 'BUG_REPORT'
ORDER BY "createdAt" DESC;

Try: add a WHERE for userId, then compare counts.

Exercise 2: count rejected items

SELECT COUNT(*)::int AS rejectedItems
FROM "GeneratedItem"
WHERE "reviewStatus" = 'REJECTED';

Try: group by documentId to see which docs are most rejected.

Exercise 3: show all accepted test cases

SELECT gi.*
FROM "GeneratedItem" gi
JOIN "Document" d ON d."id" = gi."documentId"
WHERE d."type" = 'TEST_CASE_SET'
  AND gi."reviewStatus" = 'ACCEPTED'
ORDER BY gi."createdAt" DESC;

Try: add gi."priority" filter and see what changes.

Exercise 4: list latest 5 documents

SELECT "id", "title", "type", "createdAt"
FROM "Document"
ORDER BY "createdAt" DESC
LIMIT 5;

Try: add title search with LIKE.

Exercise 5: find users with most documents

SELECT
  u."id" AS "userId",
  u."email",
  COUNT(d."id")::int AS "documentCount"
FROM "User" u
LEFT JOIN "Document" d ON d."userId" = u."id"
WHERE d."id" IS NOT NULL
GROUP BY u."id", u."email"
ORDER BY "documentCount" DESC
LIMIT 10;

Try: include guest docs by switching logic around guestSessionId.