Write SQL to Join Merchants and Triple Restaurant Points
Company: Bilt Rewards
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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:
1) Load the CSV into a staging table merchants(merchant_name TEXT, merchant_code TEXT, category TEXT).
2) 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'.
3) Explain why pushing this transformation into SQL (using JOIN and CASE) may be preferable to post-processing in application code.
4) Describe indexes and data-cleaning steps you would apply, and how you would handle approximate name matching if merchant_name strings are inconsistent.
Overview: 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.
Read the full Bilt Rewards Software Engineer interview experience this question came from
You have two tables:
1) merchants(merchant_name, merchant_code, category) – already loaded from a CSV.
2) 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.
Tables
merchants(merchant_name VARCHAR(100), merchant_code VARCHAR(20), category VARCHAR(50))
transactions(user_id INT, merchant_name VARCHAR(100), amount DECIMAL(10,2), transacted_at TIMESTAMP)
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'.