Visits Without Transactions SQL Query | SQL Interview Question

Problem statement

Prompt

Given the visits and transactions tables below, write a query that reports, for each customer, how many of their visits ended with no transaction — that is, visits with no matching row in transactions.

Output columns: customer_id and count_no_trans.
Only customers with at least one transaction-less visit appear.
A visit is matched to a transaction on visit_id.

Example

visit_id	customer_id
1	23
2	9
3	23

transaction_id	visit_id	amount
12	2	310
13	1	70

Expected result:

customer_id	count_no_trans
23	1

Constraints

visits.visit_id and transactions.transaction_id are primary keys.
A visit may have zero, one, or several transactions.

Tips

LEFT JOIN visits to transactions and keep rows where the transaction id is NULL, then group by customer.
NOT EXISTS also works and avoids the fan-out entirely.

Schema

CREATE TABLE visits (
    visit_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL
);

CREATE TABLE transactions (
    transaction_id INTEGER PRIMARY KEY,
    visit_id       INTEGER NOT NULL REFERENCES visits(visit_id),
    amount         INTEGER NOT NULL
);

Sample data

INSERT INTO visits (visit_id, customer_id) VALUES
  (1, 23),
  (2, 9),
  (3, 23),
  (4, 9),
  (5, 31);

INSERT INTO transactions (transaction_id, visit_id, amount) VALUES
  (12, 2, 310),
  (13, 1, 70),
  (14, 4, 50);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: per customer, the number of visits with no transaction.
SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM visits v
LEFT JOIN transactions t ON t.visit_id = v.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id
ORDER BY v.customer_id;

Key concepts

LEFT JOIN
Anti-join
GROUP BY
COUNT

Visits Without Transactions