SQL Sub Query

I was having some problem with a SQL query few hour ago, it required me to write a sub query to execute it correctly.

Hmm, not good, heavy process.