Back to questions
ApplePay Volume Visa SQL Interview Question
ApplePay Volume
Visa SQL Interview Question
Visa is analysing its partnership with ApplyPay. Calculate the total transaction volume for each merchant where the transaction was performed via ApplePay.
Output the merchant ID and the total transactions. For merchants with no ApplePay transactions, output their total transaction volume as 0. Display the result in descending order of the transaction volume.
Tip: You might want to check the spelling of the payment methods... just sayin' 👀
Table:
| Column Name | Type |
|---|---|
| merchant_id | integer |
| transaction_amount | integer |
| payment_method | varchar |
Example Input:
| merchant_id | transaction_amount | payment_method |
|---|---|---|
| 1 | 600 | Contactless Chip |
| 1 | 850 | apple pay |
| 1 | 500 | Apple Pay |
| 2 | 560 | Magstripe |
| 2 | 400 | Samsung Pay |
| 4 | 1200 | apple pay |
Example Output:
| merchant_id | total_transaction |
|---|---|
| 1 | 1350 |
| 4 | 1200 |
| 2 | 0 |
Explanation
Merchant 1 has made two ApplePay purchases totalling $1,350, Merchant 4 has completed one ApplePay transaction costing $1,200, and Merchant 2 has not completed any ApplePay transactions.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Objective: Output the merchant with their total transaction volume where payment is made via ApplePay.
"For merchants with no ApplePay transactions, output their total transaction volume as 0."
Based on the above condition, we break it down into 2 scenarios.
- Scenario #1: Where the payment method is ApplePay, then calculate the total transaction volume.
- Scenario #2: Where the payment method is not ApplePay, then the total transaction volume is $0.
Additionally, the tip asks to "check the spelling of the payment methods". To be safe, let's apply the function on the field. It converts the original value of the string (say, "apPle PaY" or "Apple pay") into the lower-case value of the string ("apple pay").
Finally, find the total transaction volume for each merchant by using the function on the .
Remember to sort the final output by the highest transaction volume to the least.