Sessionization Part 2: Generating Session IDs & Project Budget SQL Interview Problems

This chapter covers two advanced SQL interview problems: finalizing session identification logic using window functions, and calculating complex project budgets with proportional salary allocations based on active project durations.

1. Why Do We Need Session IDs and Project Budget Forecasting?

In data engineering and analytics engineering interviews, window functions and temporal calculations are frequently tested.

• Sessionization: Raw user clickstreams or event logs must be grouped into logical user sessions based on inactivity thresholds or time gaps. Once grouped, we need to assign clean sequential running session IDs (1, 2, 3...) per user.
• Project Budgeting: Companies often track fixed project budgets alongside employee annual salaries. Because employees may work on projects for fractions of a year, queries must proportionally allocate annual salaries across exact project durations to determine whether a project is over budget or within budget.

2. Core Mental Model: The Session ID Pipeline

When generating session IDs from grouped event data, the intermediate grouping key ensures events are clustered correctly, but the final presentation requires a clean running counter per user.

Real-Life Analogy: The Customer Service Call Log

Imagine a customer calling a bank's support line multiple times throughout the day.

• If calls are made within 30 minutes of each other, they belong to the same support session.
• If a customer calls at 9:00 AM, again at 9:15 AM, and then returns at 4:00 PM, the bank logs two distinct customer sessions.
• Within a user's history, we want to label these sessions sequentially: Session #1 for the morning cluster, and Session #2 for the afternoon cluster.

3. Step-by-Step Evolution: Finalizing Session ID Queries

In the sessionization problem, we compute aggregate metrics (start time, end time, duration, event count) grouped by a calculated group_id. To present this cleanly to an end user, we strip out the internal group ID from the select clause and introduce a clean running row number partitioned by user_id.

SELECT 
    user_id,
    ROW_NUMBER() OVER (
        PARTITION BY user_id 
        ORDER BY MIN(event_time)
    ) AS session_id,
    MIN(event_time) AS session_start_time,
    MAX(event_time) AS session_end_time,
    DATEDIFF(MINUTE, MIN(event_time), MAX(event_time)) AS session_duration,
    COUNT(*) AS event_count
FROM (
    -- Subquery or CTE that assigns group_id based on time gaps
    SELECT *, 
           SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS group_id
    FROM event_logs
) t
GROUP BY user_id, group_id;

4. Understanding the Project Budget Schema

The second major problem tackles project budget management across relational databases. We deal with three core tables:

1. employees: Stores individual employee details and annual compensation.
   • employee_id, name, salary (Annual salary)
2. projects: Stores high-level project metadata and financial limits.
   • project_id, title, start_date, end_date, budget (Total salary budget allocated)
3. project_employees: A junction table linking employees to the projects they work on.
   • project_id, employee_id

5. Defining "Over Budget" and Proportional Salary Allocation

A project is defined as over budget when the total salaries allocated to participating employees on a per-day (or proportional time) basis exceed the fixed budget of the project.

Mathematical & Proportional Formula

Let:

• $S_{\text{annual}}$ be the annual salary of an employee.
• $D_{\text{project}}$ be the duration of the project in days (or years).
• $B_{\text{project}}$ be the total allocated project budget.

For a single employee working on a project that spans a fraction of a year ($f = \frac{\text{Project Duration in Days}}{365}$):

For multiple employees working on the same project, we sum their pro-rated salaries:

If $\text{Total Project Labor Cost} > B_{\text{project}}$, the label returned is 'over budget'. Otherwise, it is 'within budget'.

6. Real-World Walkthrough: Analytics Platform Example

Let's walk through the creator's explanation of the Analytics Platform project to understand how pro-rating works in practice:

1. Project Duration: Runs from May 5th through August (approx. 3 months / 0.25 of a year).
2. Project Budget: $80,000.
3. Assigned Employees: Employee #6 (Salary: $105,000) and Employee #7 (Salary: $98,000).
4. Combined Annual Salary:

5. Pro-rated Salary for 3 Months (approx. 1/4th year):

6. Comparison: Since the allocated labor cost (~$50,750) is less than the project budget ($80,000), the project is classified as within budget.

7. Comparative Summary of Table Relationships

8. Summary and Key Takeaways

• Session ID Generation: Use ROW_NUMBER() partitioned by user_id and ordered by the session start time (MIN(event_time)) to transform internal group hashes into clean sequential session numbers (1, 2, 3...).
• Grouping Integrity: Always retain grouping keys (like group_id) in your GROUP BY clause during aggregation, even if you omit them from the final SELECT projection.
• Multi-Table Joins: Calculating project budgets requires joining projects with project_employees and employees to aggregate total annual compensation per project.
• Temporal Pro-rating: Salaries are typically stored as annual figures. You must scale salaries by the exact project duration fraction (DATEDIFF divided by 365) to compute true labor costs.
• Conditional Labeling: Use conditional logic (CASE WHEN ... THEN 'over budget' ELSE 'within budget' END) to output required business labels based on financial thresholds.
• Ordering Results: Final output datasets should be ordered logically (e.g., ORDER BY budget, title) as specified by interview requirements.