SQL query explained

Explain this SQL query in plain English and suggest how to check the results: [PASTE]. Point out anything that might give wrong numbers.