Question: Payment Method Preferences
"Find the most commonly used payment method (credit card, PayPal, etc.) for each customer based on their transaction history."

Step-by-Step Explanation:

You have a table called transactions with:

• user_id
• payment_method (like 'Credit Card', 'PayPal', 'Debit Card', etc.)

You need to:

1. Find, for each user, the payment method they used the most.
2. Based on their transaction history.

Sample Data (transactions):

Step 1: Count How Many Times Each Payment Method Was Used by Each User

Use GROUP BY user_id, payment_method and COUNT(*).

Example:

For user 1:

• Credit Card: 2 times
• PayPal: 1 time

For user 2:

• PayPal: 2 times

For user 3:

• Debit Card: 1 time
• Credit Card: 1 time

Step 2: Find the Most Frequently Used Payment Method Per User

After counting, pick the payment method with the highest count for each user.

If there’s a tie (equal counts), it’s okay to pick either one (unless otherwise specified).

SQL Query:

WITH payment_counts AS (
  SELECT
    user_id,
    payment_method,
    COUNT(*) AS method_count
  FROM
    transactions
  GROUP BY
    user_id, payment_method
),

ranked_methods AS (
  SELECT
    user_id,
    payment_method,
    method_count,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY method_count DESC) AS rn
  FROM
    payment_counts
)

SELECT
  user_id,
  payment_method,
  method_count
FROM
  ranked_methods
WHERE
  rn = 1;

Explanation of SQL:

• payment_counts CTE:
  • Counts how many times each payment method was used per user.
• ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY method_count DESC):
  • Assigns a rank to each payment method for a user.
  • The method used most gets rn = 1.
• Final SELECT:
  • Only picks the top-ranked method for each user (WHERE rn = 1).

Expected Output:

(Note: for user 3, either "Debit Card" or "Credit Card" could appear if tied.)

Important Points:

• Always partition by user_id when using ROW_NUMBER().
• Sorting ORDER BY method_count DESC ensures the most used method comes first.
• If there are ties and the interviewer wants both methods listed, use RANK() instead of ROW_NUMBER().