【Leetcode】1581. Customer Who Visited but Did Not Make Any Transactions

Leetcode題目網址：1581. Customer Who Visited but Did Not Make Any Transactions

難度：Easy

Visits表格屬性
Column Name	Type
visit_id	int
customer_id	int

visit_id is the column with unique values for this table.

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

Transactions表格屬性
Column Name	Type
transaction_id	int
visit_id	int
amount	int

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.

個人翻譯（非逐字翻譯）：

編寫解決方案尋找未進行交易的用戶ID及他們進行訪問的次數，以不排序的方式回傳結果。

輸入：

Visits
visit_id	customer_id
1	23
2	9
4	30
5	54
6	96
7	54
8	54

Transactions
transaction_id	visit_id	amount
2	5	310
3	5	300
9	5	200
12	1	910
13	2	970

輸出：

customer_id	count_no_trans
54	2
30	1
96	1

註釋：

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.

個人理解

先用Left join語法將語法資料表合併，透過COUNT()計算出訂單總數，最後再進行分組

程式碼

select customer_id,count(visit_id) as count_no_trans
from Visits
left join Transactions
using(visit_id)
where transaction_id is null
group by customer_id;

參考資料

LINE 分享

小屋創作

日誌2024-10-31 12:46
【Leetcode】1581. Customer Who Visited but Did Not Make Any Transactions

相關創作

【Leetcode】175. Combine Two Tables

【Leetcode】1683. Invalid Tweets

【SQL】一些常用的SQL語法

留言

日誌2024-10-31 12:46【Leetcode】1581. Customer Who Visited but Did Not Make Any Transactions

相關創作

【Leetcode】175. Combine Two Tables

【Leetcode】1683. Invalid Tweets

【SQL】一些常用的SQL語法

留言

日誌2024-10-31 12:46
【Leetcode】1581. Customer Who Visited but Did Not Make Any Transactions