How to Find Users with Multiple Daily Payments from Different ZIP Codes Using SQL?

SQL Query to Detect Users with Multiple Same-Day Payments from Varying ZIP Codes

This SQL query analyzes a PAYMENTS table (containing user IDs, account numbers, ZIP codes, and payment dates) to pinpoint users who made multiple payments on the same day, each using the same account number but originating from different ZIP codes.

SELECT 
    user_id,
    COUNT(*) AS payment_count
FROM
    (SELECT DISTINCT
            user_id,
            account_no, 
            zip, 
            payment_date
         FROM
            PAYMENTS
    ) AS daily_payments
GROUP BY
    user_id,
    account_no,
    payment_date
HAVING COUNT(*) > 1;

Explanation:

The query employs a subquery to initially select unique payment records based on user_id, account_no, zip, and payment_date. This eliminates redundant entries. The outer query then groups these unique records by user_id, account_no, and payment_date to count payments for each unique combination. Finally, the HAVING clause filters the results, retaining only those users with more than one payment on a given day using the same account number. The output shows the user_id and the number of qualifying payments.

The above is the detailed content of How to Find Users with Multiple Daily Payments from Different ZIP Codes Using SQL?. For more information, please follow other related articles on the PHP Chinese website!