Database Fundamentals · 02

Visitors walked in.
Did they buy anything?

A real business problem — told through a shopping mall — that teaches you keys, relationships, joins, and aggregation, all at once.

🔗 LeetCode: Customer Who Visited but Did Not Make Any Transactions · Easy
scroll to begin
The Business

Imagine you own a shopping mall.

You want to track every customer visit, and every transaction that happens during that visit — so you build two systems.

🏬 Shopping Mall Owner
↓wants to track visits & transactions
System 1Visitors entry point
↓records every entry in a table named Visits
Visits
visit_idcustomer_id
123
29
430
554
696
754
854

Stores an auto-generated visit_id and the customer_id for every entry.

System 2Transaction tracking
↓records every purchase in a table named Transactions
Transactions
transaction_idvisit_idamount
25310
35300
95200
121910
132970

Stores a transaction_id, the visit_id it happened during, and the amount.

↓the two tables connect through visit_id
Every transaction can now be traced back to exactly one visit
The Business Question

Your manager asks a very specific question.

"Find customers who visited the mall but did not purchase anything, and tell me how many such visits each customer had."

✗Not this:

Customers who never visited the mall at all — that's a different question entirely, and it's not what's being asked.

✓Actually this:

Visits that happened, but ended with zero transactions — counted per customer.

What You Need to Know

Answering this needs real understanding — not just SQL syntax.

Seven concepts, in order, take us from "two random tables" to the final query.

1Relationship between tables 2Primary Key & Foreign Key 3One-to-Many relationship 4Best join: Left / Right / Inner 5Aggregate functions 6COUNT(*) vs COUNT(1) vs COUNT(col) 7SQL logical execution order
Concepts 1 – 3

How are these two tables related?

Every transaction points back to exactly one visit — through visit_id.

Primary Key

Visits.visit_id

Uniquely identifies one visit. No two rows in Visits share the same visit_id.

Foreign Key

Transactions.visit_id

Points back to a visit_id in Visits. Multiple transactions can share the same one.

Since one visit can have many transactions (or none), but every transaction belongs to exactly one visit — this is a one-to-many relationship, from Visits → Transactions.

1 Visit — visit_id = 5
↓can match many rows in Transactions
Transaction #2 · 310
Transaction #3 · 300
Transaction #9 · 200

...or it can match zero rows — like visit_id 4, 6, 7, 8. That's exactly the case this problem is hunting for.

visit_id 5→3 transactions (310, 300, 200)
visit_id 1→1 transaction (910)
visit_id 2→1 transaction (970)
visit_id 4→0 transactions — this is what we're hunting for
visit_id 6→0 transactions — this is what we're hunting for
visit_id 7→0 transactions — this is what we're hunting for
visit_id 8→0 transactions — this is what we're hunting for
Concept 4

Which join keeps the visits with no match?

We need every visit to survive the join — even the ones with zero matching transactions.

Visits — 7 rows
Transactions — 5 rows
LEFT JOIN Visits → Transactions ON visit_id
↓
7 rows out — NULL on the transaction side wherever nothing matched

INNER JOIN

Keeps only visits that do have a matching transaction. Visits with zero purchases vanish completely.

✗ loses exactly the rows we need

LEFT JOIN

Keeps every row from Visits, and fills in NULL on the Transactions side when there's no match.

✓ this is the one

RIGHT JOIN

Keeps every row from Transactions instead — but a transaction can't exist without a visit, so this doesn't help here.

✗ wrong side of the relationship
Concept 5

Now group the "no transaction" visits by customer.

After the LEFT JOIN, we filter down to rows where the transaction side is NULL, then group by customer_id and count.

4 unmatched visits — 4, 6, 7, 8
↓GROUP BY customer_id
3 customer groups — 54, 30, 96
↓COUNT(visit_id) per group
Final "no transaction" count, per customer
customer_id 543 visits (5, 7, 8) → 2 had no transaction → count = 2
customer_id 301 visit (4), no transaction → count = 1
customer_id 961 visit (6), no transaction → count = 1
Concept 6

COUNT(*) vs COUNT(1) vs COUNT(column)

They look similar. They are not always the same.

COUNT(*)

Counts every row in the group, regardless of NULLs in any column.

COUNT(1)

Counts every row too — 1 is just a constant, evaluated per row. Behaves the same as COUNT(*) in practice.

COUNT(column)

Counts only rows where that column is NOT NULL. This is exactly why we use COUNT(v.visit_id) here instead of COUNT(*).

Concept 7

SQL doesn't run top to bottom.

It runs in this logical order — which is exactly why WHERE can filter on the join result before grouping even happens.

FROM Visits v

Start with the base table.

LEFT JOIN Transactions t

Attach matching transactions — or NULL if none exist.

WHERE t.transaction_id IS NULL

Keep only the visits that had no match.

GROUP BY v.customer_id

Bucket the remaining visits by customer.

HAVING

Not needed here — we're not filtering the grouped result further.

SELECT customer_id, COUNT(visit_id)

Return the customer and their no-transaction visit count.

ORDER BY

Not required — the problem accepts any order.

Putting It Together

The final query

SELECT v.customer_id,
    COUNT(v.visit_id) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t
  ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id;
LEFT JOIN keeps every visit, matched or not.
WHERE t.transaction_id IS NULL isolates the unmatched visits.
COUNT(v.visit_id) — not COUNT(*) — counts visits, grouped per customer.
Result
customer_idcount_no_trans
542
301
961
The Takeaway

Understanding the relationship between tables solved the problem — not memorizing syntax.

Keys, joins, and execution order aren't trivia. They're how you translate a real manager's question into a correct query.

→ Series continues: more real business problems