SQL

1581. Customer Who Visited but Did Not Make Any Transactions

transaction_id is column with unique values for this table.
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.

The result format is in the following example.

Input:
Visits

Transactions

Output:

Explanation:
Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12.
Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13.
Customer with id = 30 visited the mall once and did not make any transactions.
Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions.
Customer with id = 96 visited the mall once and did not make any transactions.
As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also, user 54 visited the mall twice and did not make any transactions.

解答：

SELECT customer_id , count(customer_id) AS count_no_trans 
FROM visit_id left join Transactions on Visits.customer_id = Transactions.customer_id
WHERE Transactions.transaction_id IS NULL GROUP BY Visits.customer_id

GROUP BY 敘述句搭配聚合函數 (aggregation function) 使用，是用來將查詢結果中特定欄位值相同的資料分為若干個群組，而每一個群組都會傳回一個資料列。若沒有使用 GROUP BY，聚合函數針對一個 SELECT 查詢，只會返回一個彙總值。

聚合函數指的也就是 AVG()、COUNT()、MAX()、MIN()、SUM() 等這些內建函數。

遇到問題跟聚合函數有關時又要分組先想到GROUP BY。

這題left join在這條件下會null，所以WHERE Transactions.customer_id IS NULL要下。讓NULL的資料消失。

SELECT *
FROM Visits left join Transactions on Visits.visit_id = Transactions.visit_id