Leetcode SQL 50 -- 1581. Customer Who Visited but Did Not Make Any Transactions

LeetCode > SQL_50 SQL

Created At : 2024-11-04 11:16

Count:161 Views :

Question
1. Table name: Visits
2. Table name: Transactions
Explaination
Solution

Question

Table name: `Visits`

Column Name	Type
visit_id	int
customer_id	int

This table contains information about the customers who visited the mall.

Table name: `Transactions`

Column Name	Type
transaction_id	int
visit_id	int
amount	int

This table contains information about the transactions made during the visit_id.

Write a solution to find the IDs of the users who visited without making any transactions, and
the number of times they made these types of visits.
Return the result table sorted in any order.

Explaination

This is another typical JOIN query question.
The tricky part is the column we join on doesn’t necessarily be selected.

Moreover, we can control which rows to be joined using WHERE, as simple SELECT.

Lasting to remember is to use GROUP BY for your COUNT() function,
to make sure they counted separately based on customer_id.

Solution

SELECT customer_id, count(*) AS count_no_trans
FROM Visits
LEFT JOIN Transactions ON Visits.visit_id=Transactions.visit_id
WHERE transaction_id IS NULL
GROUP BY customer_id