NAT
Q.
Consider a table **EmployeeLogs(emp_id INT, log_time TIMESTAMP, action VARCHAR(10))** where **action ∈ { 'LOGIN', 'LOGOUT' }**. Each employee may log in and out multiple times per day. Assume the table contains **10,00,000 rows**, and the following advanced SQL query is executed on a PostgreSQL-like system:
```
WITH session_pairs AS (
SELECT emp_id,
log_time AS login_time,
LEAD(log_time) OVER (PARTITION BY emp_id ORDER BY log_time) AS logout_time,
LEAD(action) OVER (PARTITION BY emp_id ORDER BY log_time) AS next_action
FROM EmployeeLogs
)
SELECT COUNT(*)
FROM session_pairs
WHERE next_action = 'LOGOUT'
AND logout_time IS NOT NULL
AND logout_time > login_time;
```
Assume:
- For every employee, **exactly 60%** of all rows are `'LOGIN'` actions.
- Every `'LOGIN'` is eventually followed by a `'LOGOUT'` for the same employee.
- No two consecutive actions for the same employee are `'LOGIN'`.
- The distribution of rows across employees does not affect the ratio.
Compute the **exact number of valid login–logout session pairs** returned by the query.
Give the numerical answer.
Correct Answer: 600000
Total rows = 10,00,000. Exactly 60% are LOGIN → 6,00,000 LOGIN rows. Each LOGIN is eventually followed by a LOGOUT and no two consecutive LOGINs exist, so every LOGIN corresponds to exactly one valid session pair. Therefore, the query returns exactly the number of LOGIN rows, i.e., 6,00,000.