Write SQL to Join Merchants and Triple Restaurant Points

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Bilt Rewards.

Q: What role is this question designed for?

This question is commonly asked for Software Engineer candidates at Bilt Rewards during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview