Desk Cohesion Across Trading Sessions
Problem
On a trading floor each broker belongs to a desk. Whenever a broker passes an order ticket to another broker it is recorded as a handoff with a `clock` time of the form `HH:MM`. The day has two sessions: the **morning** session covers `00:00` through `12:00` (inclusive) and the **afternoon** session covers `12:01` through `23:59`. For each desk and each session, compute a `cohesion_score`: add `+1` when the receiving broker is on the **same** desk as the sending broker, and `-1` when the ticket crosses to a **different** desk. The session and the score are attributed to the desk of the **sending** broker. Return `desk_name`, `session_number` (1 for morning, 2 for afternoon), and `cohesion_score`, ordered by `desk_name` then `session_number`.
Tables
Example rows — the live problem includes the full dataset.
| broker_id | desk_name |
|---|---|
| 1 | Equities |
| 2 | Equities |
| 3 | Equities |
| from_broker | clock | to_broker |
|---|---|---|
| 1 | 09:15 | 2 |
| 2 | 11:45 | 3 |
| 3 | 11:50 | 1 |
Expected output
Your answer should return 4 rows with the columns desk_name, session_number, cohesion_score.
Starter code (SQL)
SELECT *
FROM desks;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock