Coding questions · Bilt Rewards
Write SQL to Join Merchants and Triple Restaurant Points
This question evaluates SQL querying and data engineering competencies—specifically JOINs, conditional logic (CASE), ETL staging, data cleaning, indexing, and approximate string matching—within the data manipulation and database domain and emphasizes practical application of SQL for transforming transactional datasets.
You are given: (1) a CSV file merchants.csv with columns: merchant_name, merchant_code, category; (2) a database table transactions(user_id INT, merchant_name TEXT, amount DECIMAL(10, 2), transacted_at TIMESTAMP). Tasks:
- Load the CSV into a staging table merchants(merchant_name TEXT, merchant_code TEXT, category TEXT).
- Write a single SQL query that returns, for each transaction, user_id, merchant_code, category, base_points = FLOOR(amount), and final_points where Restaurant transactions earn 3x base_points and all others 1x. If merchant_name does not match any row in merchants, set merchant_code = 'UNKNOWN' and category = 'UNKNOWN'.
- Explain why pushing this transformation into SQL (using JOIN and CASE) may be preferable to post-processing in application code.
- Describe indexes and data-cleaning steps you would apply, and how you would handle approximate name matching if merchant_name strings are inconsistent.
Join Merchants and Transactions to Compute Restaurant Reward Points
You have two tables:
- merchants(merchant_name, merchant_code, category) – already loaded from a CSV.
- transactions(user_id, merchant_name, amount, transacted_at).
Write a single SQL query that returns, for each row in transactions:
- user_id,
- merchant_code,
- category,
- base_points = FLOOR(amount),
- final_points, where transactions with category = 'Restaurant' earn 3x base_points and all other categories earn 1x base_points.
If a transaction.merchant_name does not match any row in merchants, set merchant_code = 'UNKNOWN' and category = 'UNKNOWN' for that transaction, and treat it as a non-Restaurant transaction.
Table merchants
| column | type | constraints |
|---|---|---|
| merchant_name | VARCHAR(100) | NOT NULL |
| merchant_code | VARCHAR(20) | PRIMARY KEY, NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| category | merchant_code | merchant_name |
|---|---|---|
| Restaurant | STB001 | Starbucks |
| Transport | UBR001 | Uber |
| Grocery | WFD001 | Whole Foods |
| Restaurant | DIN001 | Local Diner |
Table transactions
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| merchant_name | VARCHAR(100) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| transacted_at | TIMESTAMP | NOT NULL |
Sample data:
| amount | merchant_name | transacted_at | user_id |
|---|---|---|---|
| 5.75 | Starbucks | 2025-05-20 08:15:00 | 1 |
| 12.4 | Uber | 2025-05-21 09:30:00 | 1 |
| 30.1 | Whole Foods | 2025-05-22 13:00:00 | 2 |
| 20 | Unknown Shop | 2025-05-23 18:45:00 | 3 |
| 15.5 | Local Diner | 2025-05-24 19:20:00 | 2 |
Expected output
One row per transaction, with merchant details (or 'UNKNOWN' when there is no match), the base_points as FLOOR(amount), and final_points where Restaurant category earns 3x base_points and others earn 1x.
| base_points | category | final_points | merchant_code | user_id |
|---|---|---|---|---|
| 5 | Restaurant | 15 | STB001 | 1 |
| 12 | Transport | 12 | UBR001 | 1 |
| 30 | Grocery | 30 | WFD001 | 2 |
| 20 | UNKNOWN | 20 | UNKNOWN | 3 |
| 15 | Restaurant | 45 | DIN001 | 2 |
Hints
- Use a LEFT JOIN from transactions to merchants so that transactions without a matching merchant still appear.
- Use COALESCE to default merchant_code and category to 'UNKNOWN', and a CASE expression to apply 3x points only when the category is 'Restaurant'.