Explain how joins, window functions, and subqueries are used in advanced SQL for marketing analytics.