Given a theoretical complex SQL query involving five table joins, subqueries, and window functions, explain how a query optimizer determines the join order. Discuss the impact of indexed columns versus non-indexed columns on the choice of join algorithms (e.g., Nested Loop vs. Hash Join) and how statistical density of data influences the decision to scan versus seek.