Cross-border share by card tier
hardCross-border share by card tier
Visa SQL Interview Question
Visa earns extra fees on cross-border spending, where a card is used in a different country from the one that issued it. The product team wants to know which card tiers are used abroad the most.
Find the percentage of approved transactions that were cross-border for each card_tier, as cross_border_pct, rounded to 1 decimal place.
Asked of
- Data Analyst
- Business Analyst
- Data Scientist
- Analytics Engineer
card_transactionsTable18 rows
| Column Name | Type |
|---|---|
| transaction_id | BIGINT |
| card_id | BIGINT |
| merchant_country | VARCHAR |
| merchant_category | VARCHAR |
| amount_usd | DOUBLE |
| transaction_date | DATE |
| status | VARCHAR |
cardsTable6 rows
| Column Name | Type |
|---|---|
| card_id | BIGINT |
| card_tier | VARCHAR |
| issuing_country | VARCHAR |
card_transactionsExample Input
| transaction_id | card_id | merchant_country | merchant_category | amount_usd | transaction_date | status |
|---|---|---|---|---|---|---|
| 3001 | 1 | United States | Groceries | 84.2 | 2024-01-04 | approved |
| 3002 | 2 | France | Travel | 640 | 2024-01-09 | approved |
| 3003 | 3 | Canada | Restaurants | 52.75 | 2024-01-15 | approved |
| 3004 | 4 | Spain | Travel | 410.3 | 2024-01-21 | declined |
| 3005 | 6 | Japan | Electronics | 1299 | 2024-01-28 | approved |
| 3006 | 5 | Canada | Groceries | 96.4 | 2024-02-02 | approved |
| 3007 | 1 | Mexico | Travel | 275 | 2024-02-08 | approved |
| 3008 | 3 | United States | Electronics | 899.99 | 2024-02-13 | approved |
| 3009 | 2 | United States | Restaurants | 68.1 | 2024-02-17 | approved |
| 3010 | 4 | United Kingdom | Groceries | 58.6 | 2024-02-22 | approved |
| 3011 | 6 | United States | Travel | 520 | 2024-02-27 | declined |
| 3012 | 5 | United States | Restaurants | 44.8 | 2024-03-03 | approved |
cardsExample Input
| card_id | card_tier | issuing_country |
|---|---|---|
| 1 | Classic | United States |
| 2 | Gold | United States |
| 3 | Signature | Canada |
| 4 | Classic | United Kingdom |
| 5 | Gold | Canada |
| 6 | Signature | United States |
Example Output
| card_tier | cross_border_pct |
|---|---|
| Classic | 33.3 |
| Gold | 50 |
| Signature | 66.7 |
Explanation
Gold cards have 4 approved transactions in the example. Two of them were cross-border: a card issued in the United States was used in France, and a card issued in Canada was used in the United States. That gives 50.0%. Declined transactions are not counted.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Visa
Difficulty
hard
Topic
conditional logic
Your query
Starting the SQL engine...