Katkat
Back

Write SQL to Join Merchants and Triple Restaurant Points

Bilt RewardsSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

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:
  3. Load the CSV into a staging table merchants(merchant_name TEXT, merchant_code TEXT, category TEXT).
  4. 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'.
  5. Explain why pushing this transformation into SQL (using JOIN and CASE) may be preferable to post-processing in application code.
  6. Describe indexes and data-cleaning steps you would apply, and how you would handle approximate name matching if merchant_name strings are inconsistent.