SQL Expected vs Actual Comparison

The 6sense QA team validates platform behavior by storing expected API or UI results separately from results observed during automated test runs. Write a PostgreSQL query that compares the expected and actual result rows for every 6sense test case.

Requirements

1. Return every test case, including cases with no recorded result rows.
2. Report expected row count, actual row count, mismatched row count, missing expected rows, and unexpected actual rows.
3. Mark each case as PASS, FAIL, or NOT_RUN. Treat two NULL result values as equal.
4. Order the output by test_case_id ascending.

Output details

• Return columns in this order: test_case_id, test_name, expected_count, actual_count, mismatched_count, missing_expected_count, unexpected_actual_count, status.
• Match expected and actual rows by equal test_case_id and result_key values.
• Count only result rows whose result_key is non-NULL; exclude NULL-key rows from all reported counts.
• Retain duplicate result rows as separate rows when calculating counts.
• Set status to NOT_RUN only when neither result table has any row for the test case; otherwise use PASS only when every discrepancy count is zero, and FAIL otherwise.