SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.
Join usage_logs to contracts so each usage row matches the contract where: usage_date BETWEEN start_date AND end_date for the same customer. Sum amount as total_usage per customer_id, plan_type. Only include usage rows that match a contract. Order by customer_id, plan_type.
Input: contracts customer_id | start_date | end_date | plan_type C01 | 2024-01-01 | 2024-06-30 | basic C01 | 2024-07-01 | 2024-12-31 | pro C02 | 2024-01-01 | 2024-12-31 | pro usage_logs customer_id | usage_date | amount C01 | 2024-02-15 | 10 C01 | 2024-08-10 | 40 C02 | 2024-04-01 | 50 C02 | 2024-09-01 | 60 Output: customer_id | plan_type | total_usage C01 | basic | 10 C01 | pro | 40 C02 | pro | 110 Why this passes: February usage falls in C01 basic; August falls in C01 pro. Both C02 usage rows are pro.
Topics: lakebench, sql, point-in-time, scd.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Join usage to the contract valid on usage_date.
Join `usage_logs` to `contracts` so each usage row matches the contract where: `usage_date` BETWEEN `start_date` AND `end_date` for the same customer. Sum `amount` as `total_usage` per `customer_id`, `plan_type`. Only include usage rows that match a contract. Order by `customer_id`, `plan_type`.