Usage by plan with point-in-time contracts

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 18 minutes. Part of the Pro drill bank.

A subscription company has customers who change plans over time. The contracts table says which plan a customer was on between a start date and an end date, and usage_logs records how much they used on each day. To know how much usage belongs to each plan, you must match each usage day to the contract that was active on that day. Join usage_logs to contracts for the same customer, where usage_date is between start_date and end_date. Add up amount as total_usage for each customer_id and plan_type. Only include usage rows that match a contract. Sort by customer_id, then plan_type.

Requirements

• Match each usage row to the contract of the same customer that covers the usage date.
• Use usage_date BETWEEN start_date AND end_date in the join condition.
• Add up amount for each customer and plan type.
• Drop usage rows that no contract covers.
• Sort by customer_id, then plan_type.

Constraints

• The tables are usage_logs and contracts.
• Join on customer_id and on the date range, not on customer_id alone.
• Return only these 3 columns, in this order: customer_id, plan_type, total_usage.

Examples

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