ShaikBlog

  🔹 PATTERN 3: Ranking & Ties 📘 Assumed Table: employees emp_id | emp_name | department | salary | join_date 1️⃣ Basic Ranking (No Partition) ✅ SQL – Basic SELECT emp_id, emp_name, salary, ROW_NUMBER () OVER ( ORDER BY salary DESC ) AS row_num, RANK () OVER ( ORDER BY salary DESC ) AS rank_val, DENSE_RANK () OVER ( ORDER BY salary DESC ) AS dense_rank_val FROM employees; 📌 Interview must-know ROW_NUMBER → unique sequence RANK → gaps after ties DENSE_RANK → no gaps ✅ PySpark – Basic from pyspark.sql import functions as F from pyspark.sql.window import Window w = Window.orderBy(F.col( "salary" ).desc()) df = employees_df.select( "*" , F.row_number().over(w).alias( "row_num" ), F.rank().over(w).alias( "rank_val" ), F.dense_rank().over(w).alias( "dense_rank_val" ) ) 2️⃣ Ranking Within Groups (Department-wise) ✅ SQL – Basic SELECT emp_i...

  a complete, senior-level (6–8 yrs) coverage of 🔹 PATTERN : Self-Join (Hierarchy) 🎯 Core skill: Joining a table to itself 🔹 PATTERN : Self-Join (Hierarchy) 📘 Assumed Table: employees emp_id | emp_name | manager_id | department | salary manager_id → references emp_id CEO has manager_id = NULL 1️⃣ Employee → Manager Mapping ✅ SQL – Basic SELECT e.emp_id, e.emp_name, m.emp_name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; 📌 Basic self-join concept ✅ SQL – Advanced (Include CEO) SELECT e.emp_id, e.emp_name, COALESCE (m.emp_name, 'CEO' ) AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; ✅ PySpark – Basic emp = employees_df.alias( "e" ) mgr = employees_df.alias( "m" ) df = emp.join( mgr, emp.manager_id == mgr.emp_id, "left" ).select( "e.emp_id" , "e.emp_name" , ...

  🎯 Running Totals & Moving Averages — written at 6–8 years experience level , with basic + advanced / optimized approaches . 🔹 Assumed Sample Tables sales sale_id | customer_id | product_id | sale_date | amount | status transactions txn_id | account_id | txn_date | txn_type | amount -- credit/debit 1️⃣ Running Total of Daily Sales ✅ SQL – Basic SELECT sale_date, SUM (amount) AS daily_sales, SUM ( SUM (amount)) OVER ( ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM sales GROUP BY sale_date ORDER BY sale_date; 📌 Interview Tip Always aggregate before applying window functions when dealing with dates. ✅ SQL – Advanced (Partitioned Running Total) SELECT customer_id, sale_date, SUM (amount) AS daily_sales, SUM ( SUM (amount)) OVER ( PARTITION BY customer_id ORDER BY sale_date ) AS running_total FROM sales GROUP BY customer_i...