리트코드 : 1581. Customer Who Visited but Did Not Make Any Transactions

리트코드 : 1581. Customer Who Visited but Did Not Make Any Transactions

• [link]
  

---

문제

Table: 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.


Table: 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.



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              |
+-------------+----------------+
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.

• 방문 후, 거래가 없는 고객들의 id와 그 수

문제 풀이

MySQL

WITH no_trans as (
    SELECT v.customer_id, t.transaction_id 
    FROM visits v
    LEFT JOIN transactions t ON v.visit_id = t.visit_id
)

SELECT customer_id, count(*) as count_no_trans
FROM no_trans
WHERE transaction_id is null
GROUP BY customer_id

• LEFT JOIN을 통해서, 방문은 했지만 거래 데이터가 없는 사람들까지 표현해준다.
• 이후 GROUP BY COUNT를 통해서 개수 카운팅해주기.

Pandas

import pandas as pd

def find_customers(visits: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    joined = pd.merge(visits, transactions, on='visit_id', how='left')

    answer = joined[joined['transaction_id'].isnull()].groupby('customer_id').agg(
        count_no_trans = ('transaction_id','size')
    ).reset_index()
    return answer

• 파이썬에서는 count, size를 구분하고 있으니 NULL을 카운팅해주는 size를 사용한다.

코멘트

• 쉽다