Customer Who Visited but Did Not Make Any Transcations

https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/

[문제]

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.

 

[풀이]

 

Pandas

import pandas as pd

def find_customers(visits: pd.DataFrame, transactions: pd.DataFrame) -> pd.DataFrame:
    df = pd.merge(visits, transactions, on='visit_id', how='left')
    condition = df['transaction_id'].isna()
    
    return df[condition].groupby('customer_id').size().reset_index(name='count_no_trans')

 

SQL

SELECT v.customer_id,
       COUNT(*) AS count_no_trans
FROM visits v
LEFT JOIN transactions t
    ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id

 

SQL도 JOIN없이 WHERE 절에서 필터링 해도되고

판다스는 방법이 참 여러가지다. agg 함수에 대해 좀 더 알아봐야겠다.

1. df[condition].groupby('customer_id', as_index=False).agg(count_no_trans=('visit_id', 'size'))

2. visits[~visits['visit_id'].isin(transactions['visit_id'])].groupby('customer_id').agg(count_no_trans=('visit_id', 'count')).reset_index()

.

.

등등 다 되더라..