SQL

Write optimized SQL queries involving window functions, CTEs, and joins.

Write queries combining Joins and Group By operations.

You need to create a workflow where Task B runs only if Task A is successful, and Task C should always run regardless of Task A or B's status. How would you define this dependency using Airflow?

You need to design a Kafka topic for a logging service. How would you decide the number of partitions and the key for partitioning to balance throughput and ordering requirements?

Your Kafka consumer shows significant lag during peak hours. What strategies would you employ to reduce lag and ensure timely data processing?

map() vs mapPartitions(): Highlight the difference between map (row-level transformation) and mapPartitions (partition-level transformation).

repartition() vs coalesce(): Explain when to use repartition() (increases partitions) vs coalesce() (reduces partitions).