Requesting assistance with how approach on building an SQL Query to return desired result

If you post usable data -- CREATE TABLE with INSERT statement(s) -- I'll try to code something up.

Btw, when you have 3 users overlap, as you did here, what results do you expect to see?

Sure thing, attached is the script.

Trying to answer your question...,

The scenario I am trying to capture is when a user is checking out a secret and checking it in immediately, which is most likely to allow additional users to check it out and use the secret simultaneously.

The approach I considered but am not sure if it's the best one is to have both the check in and checkout for a specific user/secret pivoted so that instead of two rows to represent both the check in and check out, there would be a single row.

Then to potentially use a CTE or whatever approach is best to capture the time span between when one user checks out a secret and the next user checks the same secret out in a column.

I'm not sure the best way to approach this and that is why I posted. I need to be able to identify these scenarios and to compare the frequency of this scenario to the normal activity I see.

I hope this helps make things clearer.

Thanks for taking a look
SecretActivity.sql

This is really cool stuff man. Gotta get up to speed on this CTE stuff. Thank you

Common Table Expressions are pretty cool. Not to different from  derived table, but, you can do a lot more with CTE's (such as recursive queries).

Need to always assess when to use - I see a lot of CTE's being used where sometimes a straight query would work.

In the case of the above CTE, you probably dont need the CTE because the real strength is in the APPLY.

Where it is really beneficial is derived columns (like row_number or aggregates / window functions) so you can use the derived column in the rest of your query. And, you can embed CTE's....

Yep, CTE's are really cool in my opinion :)