How to add a correlated subquery expression in hook_views_query_alter?
This doesn't answer the question as stated, but provides a very simple approach to get at the result using Views without implementing hook_views_query_alter.

The example provided is for workbench_moderation, although the principle applies in other scenarios as well:

Creating a new Workbench view showing recently updated content and its state