💡 SQL Real-World Problem: Match Work Dates with Correct Billing Rate Using Window Functions

Have you ever wondered how companies calculate employee billing when rates change over time? Let’s solve a real-world SQL problem using window functions (LEAD) and date range joins — a concept that’s both interview-worthy and practically useful for data analysts and engineers.

🧩 Problem Statement

You are working as a Data Analyst in a consulting firm. Your company maintains two tables — one for billing rate changes over time and another for employee working hours.

Your task is to calculate the total billed amount for each employee by matching every work date with the correct billing rate period.

🧱 Table Structures

Table 1: billings

Stores billing rate and the date from which it became effective.

CREATE TABLE billings (
    emp_name VARCHAR(10),
    bill_date DATE,
    bill_rate INT
);

DELETE FROM billings;

INSERT INTO billings VALUES
('Sachin', '1990-01-01', 25),
('Sehwag', '1989-01-01', 15),
('Dhoni',  '1989-01-01', 20),
('Sachin', '1991-02-05', 30);

Table 2: HoursWorked

Stores the hours worked by employees on specific dates.

CREATE TABLE HoursWorked (
    emp_name VARCHAR(20),
    work_date DATE,
    bill_hrs INT
);

INSERT INTO HoursWorked VALUES
('Sachin', '1990-07-01', 3),
('Sachin', '1990-08-01', 5),
('Sehwag', '1990-07-01', 2),
('Sachin', '1991-07-01', 4);

🧠 Step 1: Build the Billing Date Ranges

We’ll use the LEAD() window function to get the next billing date for each employee and subtract one day from it to create the billing period.

WITH date_range AS (
    SELECT 
        emp_name,
        bill_date,
        bill_rate,
        DATE_ADD(
            LEAD(bill_date, 1, '1999-12-31') OVER (PARTITION BY emp_name ORDER BY bill_date ASC),
            INTERVAL -1 DAY
        ) AS bill_end_date
    FROM billings
)
SELECT * FROM date_range;

This gives us the start and end dates of each billing period.

🧮 Step 2: Match Work Dates to Correct Billing Period

Now, we join the above date range with the HoursWorked table — making sure the work_date falls between bill_date and bill_end_date.

WITH date_range AS (
    SELECT 
        emp_name,
        bill_date,
        bill_rate,
        DATE_ADD(
            LEAD(bill_date, 1, '1999-12-31') OVER (PARTITION BY emp_name ORDER BY bill_date ASC),
            INTERVAL -1 DAY
        ) AS bill_end_date
    FROM billings
)
SELECT 
    hw.emp_name,
    SUM(dr.bill_rate * hw.bill_hrs) AS bill_total
FROM date_range AS dr
JOIN HoursWorked AS hw 
    ON dr.emp_name = hw.emp_name
   AND hw.work_date BETWEEN dr.bill_date AND dr.bill_end_date
GROUP BY hw.emp_name;

💬 Explanation

• LEAD() helps us get the next billing date for each employee.
• DATE_ADD(…, INTERVAL -1 DAY) converts it into a valid billing end date.
• The BETWEEN condition ensures each work record picks the correct billing rate period.
• Finally, we multiply hours × rate and sum them per employee.

🚀 Key Takeaways

• This approach is dynamic — it works even if rates change multiple times.
• No need for manual date filtering — everything is handled through window functions.
• Perfect example of a real business logic problem solved elegantly in SQL.

💼 Use Case: Consulting companies, freelancers, and agencies often use similar logic to calculate project billing when rates change mid-contract.

🔥 Bonus Tip

This same logic can be used for:

• Commission rate changes
• Discount validity tracking
• Employee salary revisions over time

If you found this useful, ❤️ clap, comment, and follow for more real-world SQL and data analytics tutorials!