What should the customer payment summary show for this staging data?

Instruction: Write SQL to identify duplicate IDs and unmatched customers, then produce the requested summary. Give the exact output rows and explain NULL handling.

Context:

The PostgreSQL fixture below is an unvalidated staging table. Report every customer, including customers with no eligible paid orders. Quarantine all copies of duplicate order IDs, rows with a NULL amount, and rows whose customer does not exist, including NULL customer IDs. Count and sum only the remaining paid orders. Do not silently select one duplicate or replace an unknown order amount with zero. Customer IDs are unique and order IDs are non-NULL by definition.

CREATE TEMP TABLE qa_customers (id integer PRIMARY KEY);
CREATE TEMP TABLE qa_orders (
  order_id integer NOT NULL, customer_id integer,
  status text NOT NULL, amount_cents integer
);
INSERT INTO qa_customers VALUES (1), (2), (3);
INSERT INTO qa_orders VALUES
  (1, 1, 'paid', 100),
  (2, 1, 'paid', 200),
  (2, 1, 'paid', 200),
  (3, 2, 'pending', 90),
  (4, NULL, 'paid', 50),
  (5, 99, 'paid', 60),
  (6, 1, 'paid', NULL);

Updated

Example Answer

Order 2 occurs twice, so both copies are excluded until the source is corrected. Orders 4 and 5 have no matching customer, and order 6 has an unknown amount. Order 3 is pending. That leaves only paid order 1 for customer 1, worth 100 cents. Customers 2 and 3 must still appear with zero eligible paid orders and zero paid cents.

I would produce a separate quality report so the summary is explicitly provisional. I would prefilter eligible orders, then left join them to the complete customer set. COUNT of the non-null order ID counts actual matches; COUNT(*) would incorrectly count the placeholder row for a customer with no matches. COALESCE belongs on the final empty aggregate, not on each unknown source amount.

Worked exhibit

Run the prompt DDL once in an isolated PostgreSQL session, then these queries.

SELECT order_id, COUNT(*) AS row_count
FROM qa_orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

Expected: (2, 2).

SELECT o.order_id, o.customer_id
FROM qa_orders AS o
LEFT JOIN qa_customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL
ORDER BY o.order_id;

Expected: (4, NULL) and (5, 99). The separate NULL-amount check SELECT order_id FROM qa_orders WHERE amount_cents IS NULL ORDER BY order_id; returns (6).

WITH duplicate_ids AS (
  SELECT order_id
  FROM qa_orders
  GROUP BY order_id
  HAVING COUNT(*) > 1
), eligible AS (
  SELECT o.order_id, o.customer_id, o.amount_cents
  FROM qa_orders AS o
  JOIN qa_customers AS c ON c.id = o.customer_id
  LEFT JOIN duplicate_ids AS d ON d.order_id = o.order_id
  WHERE o.status = 'paid'
    AND o.amount_cents IS NOT NULL
    AND d.order_id IS NULL
)
SELECT c.id, COUNT(e.order_id) AS paid_orders,
       COALESCE(SUM(e.amount_cents), 0) AS paid_cents
FROM qa_customers AS c
LEFT JOIN eligible AS e ON e.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;
Customer IDEligible paid ordersPaid cents
11100
200
300

What the interviewer is assessing

Core checks (1 point each):

  • Finds duplicate 2, unmatched 4/5 and NULL amount 6.
  • Returns exactly (1,1,100), (2,0,0), (3,0,0).
  • Preserves empty customers without hiding duplicates or inventing amounts.

Stronger (+1 after all core checks): Explains COUNT(*) versus COUNT(non-null right-side key) and avoids changing the LEFT JOIN into an inner join with a final paid-status WHERE condition. A NOT EXISTS implementation with the same quarantine semantics is equally valid.

Acceptable alternatives: NOT EXISTS or other queries work with the same quarantine semantics.

Red flags: Uses DISTINCT to hide duplicate defects, treats NULL as zero at source, drops zero-order customers or counts their placeholder row as an order.

Tempting answer that misses the point

Weak answer: Use COUNT(*) after the LEFT JOIN and replace every NULL amount with zero.

Why it fails: Placeholder rows inflate empty counts; unknown source amounts become invented facts.

Follow-up

Can this summary be labeled total customer revenue?

Example response: Not without qualification. It is the total of eligible paid staging records under the stated exclusions. Duplicate and missing-value rows may represent real revenue that has not been reconciled. I would publish excluded row counts and reasons, assign data owners, and reconcile source totals before calling this a complete revenue figure.

Related Questions