LeetCode MySQL-1581. Customer Who Visited but Did Not Make Any Transactions

    AllenLi
    LeetCode MySQL

    Write an SQL query 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.

    寫一個 SQL Query來尋找有登入紀錄，但是沒有交易次數的使用者 IDs。
任意排序結果。

    Table: Visits

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id is the primary key for this table.
This table contains information about the customers who visited the mall.

    Table: Visits

    +----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+
transaction_id is the primary key for this table.
This table contains information about the transactions made during the visit_id.

    Example 1:

    Input: 
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    |
+----------------+----------+--------+
Output: 
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54          | 2              |
| 30          | 1              |
| 96          | 1              |
+-------------+----------------+

    Solution:
    1. 選擇標題 customer_id
    2. 計算 customer_id 的次數
    3. 來自 Visits 的表中，且 visits_id 沒有在 Transactions(排除重複) 中

    Code:

    SELECT 
    customer_id,
    COUNT(*) AS count_no_trans  
FROM Visits
WHERE visit_id NOT IN (SELECT DISTINCT visit_id FROM Transactions)
GROUP BY customer_id
ORDER BY count_no_trans DESC;

    checkOrder

    Related Posts