Conditional WHERE clause

No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter). Let’s try to figure out the problem and its solution with a simple scenario.