### 🚀 Why SQL Competency Defines the Highest-Paid Tech Roles In major technical evaluation interviews at companies like Google, Netflix, and Meta, SQL is the ultimate filtering mechanism. Why? Because writing syntax isn't just about matching words; it is a direct measurement of your **algorithmic set-theory intelligence**. While generic developers write slow queries that crash under production scales, senior architects design highly optimized, index-friendly structures using window partitions, hierarchical CTEs, and mathematical intervals. This handbook represents **The Forbidden SQL Codex**—a collection of 50 comprehensive interview questions spanning Beginner, Intermediate, Advanced, and Extreme Logic tiers. --- ### 🟢 Part I: The Beginner Core (Questions 1 - 10) These questions test your foundational fluency with core projection, filtering, updates, deletes, and relationships. #### 1. Retrieve all columns for all employees. * **Table Structure:** `employees(id, first_name, last_name, salary, dept_id)` ```sql SELECT * FROM employees; ``` * **Logical Breakdown:** `SELECT *` queries the database catalog, matches all column headers, and retrieves the complete row data. While excellent for quick exploration, avoid in high-throughput APIs to save network bandwidth. #### 2. List employees with a salary greater than 50,000. ```sql SELECT first_name, last_name, salary FROM employees WHERE salary > 50000; ``` * **Logical Breakdown:** `WHERE` acts as a row filter. The query engine tests the condition for each row. Standard numeric operators (`>`, `<`, `=`, `!=`) compile efficiently without quotation wraps. #### 3. Sort employees by salary descending, then by last name ascending. ```sql SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC, last_name ASC; ``` * **Logical Breakdown:** `ORDER BY` performs an on-the-fly sort. Sorting is secondary: rows are grouped by salary first, and ties are broken by sorting the last names alphabetically. #### 4. Count the number of employees per department. ```sql SELECT dept_id, COUNT(*) AS employee_count FROM employees GROUP BY dept_id; ``` * **Logical Breakdown:** `GROUP BY` collapses rows sharing matching `dept_id`s. `COUNT(*)` accumulates the count of all active indexes inside each categorized bucket. #### 5. Find departments having more than 5 employees. ```sql SELECT dept_id, COUNT(*) AS employee_count FROM employees GROUP BY dept_id HAVING COUNT(*) > 5; ``` * **Logical Breakdown:** `HAVING` filters aggregated grouping statistics. Writing `WHERE COUNT(*)` triggers compiler failures because `WHERE` executes before categories are compiled. #### 6. Join employees with departments. * **Table Structure:** `departments(id, name)` ```sql SELECT e.first_name, e.last_name, d.name AS department_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; ``` * **Logical Breakdown:** `INNER JOIN` finds intersecting keys between columns. Declaring aliases (`e`, `d`) shortens code references and prevents naming ambiguity. #### 7. Insert a new department and an employee safely. ```sql BEGIN TRANSACTION; INSERT INTO departments (id, name) VALUES (10, 'Research'); INSERT INTO employees (id, first_name, last_name, salary, dept_id) VALUES (101, 'John', 'Doe', 60000, 10); COMMIT; ``` * **Logical Breakdown:** Transactions guarantee atomic execution. If either statement crashes, the entire block is undone (`ROLLBACK`), protecting state integrity. #### 8. Update the salary of department 5 by 10%. ```sql UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 5; ``` * **Logical Breakdown:** Modifies physical records. Always include a strict `WHERE` condition; otherwise, every salary across the database will be altered! #### 9. Delete employees with no assigned department. ```sql DELETE FROM employees WHERE dept_id IS NULL; ``` * **Logical Breakdown:** Sweeps orphan records. In SQL, `NULL` represents the absence of a value, meaning comparisons must use `IS NULL` rather than `= NULL`. #### 10. Find employees whose last name starts with 'Sm'. ```sql SELECT * FROM employees WHERE last_name LIKE 'Sm%'; ``` * **Logical Breakdown:** Pattern filtering. The `%` wildcard matches any sequence of characters, while `_` matches exactly one character. --- ### 🟡 Part II: Intermediate Operations (Questions 11 - 22) Intermediate topics explore advanced joins, subqueries, set operators, conditional formatting, and window aggregations. #### 11. List all departments and their employees, including departments with no employees. ```sql SELECT d.name AS department, e.first_name, e.last_name FROM departments d LEFT JOIN employees e ON d.id = e.dept_id; ``` * **Logical Breakdown:** `LEFT JOIN` retains all records from the left table (`departments`). If a department carries no staff, right-side cells are padded with `NULL`. #### 12. Self-join: Find employees earning more than their manager. ```sql SELECT e.first_name AS employee, e.salary AS emp_salary, m.first_name AS manager, m.salary AS mgr_salary FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary; ``` * **Logical Breakdown:** Joins a table to itself by creating two distinct variables (`e` for employee, `m` for manager) to compare recursive hierarchies. #### 13. Subquery: Employees earning above their department's average salary. ```sql SELECT e.first_name, e.last_name, e.salary, e.dept_id FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id ); ``` * **Logical Breakdown:** A correlated subquery. The inner SELECT dynamically matches the outer row's `dept_id` on each individual iteration. #### 14. Find departments with at least one employee earning above 80,000 using EXISTS. ```sql SELECT d.name FROM departments d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.dept_id = d.id AND e.salary > 80000 ); ``` * **Logical Breakdown:** `EXISTS` checks for matching record existence. It halts processing the instant a match is found, outperforming large `IN` subqueries. #### 15. Merge unique employee and department names into a single column. ```sql SELECT first_name AS name FROM employees UNION SELECT name FROM departments; ``` * **Logical Breakdown:** `UNION` merges and de-duplicates sets. Column counts and data types must match exactly between both SELECT branches. #### 16. Categorize salaries dynamically. ```sql SELECT first_name, last_name, salary, CASE WHEN salary < 30000 THEN 'Low' WHEN salary BETWEEN 30000 AND 70000 THEN 'Medium' ELSE 'High' END AS salary_grade FROM employees; ``` * **Logical Breakdown:** `CASE WHEN` performs inline logical branching. Evaluates sequentially; the first matching clause blocks further evaluation. #### 17. Replace NULL department values with 'Not Assigned'. ```sql SELECT first_name, COALESCE(d.name, 'Not Assigned') AS department FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; ``` * **Logical Breakdown:** `COALESCE` takes a variable list of arguments and returns the very first non-NULL value it encounters. #### 18. Count employee hiring volume by year and month. ```sql SELECT EXTRACT(YEAR FROM hire_date) AS year, EXTRACT(MONTH FROM hire_date) AS month, COUNT(*) AS hires FROM employees GROUP BY EXTRACT(YEAR FROM hire_date), EXTRACT(MONTH FROM hire_date) ORDER BY year, month; ``` * **Logical Breakdown:** Uses `EXTRACT` to slice timestamp metadata. Highly database-specific (PostgreSQL uses `EXTRACT`, MySQL uses `YEAR()`, `MONTH()`). #### 19. Group by multi-column cases. ```sql SELECT dept_id, CASE WHEN salary < 40000 THEN 'Low' WHEN salary < 80000 THEN 'Mid' ELSE 'High' END AS grade, COUNT(*) AS count FROM employees GROUP BY dept_id, grade ORDER BY dept_id, grade; ``` * **Logical Breakdown:** Shows that databases can group rows by dynamic CASE expressions on the fly. #### 20. Rank employees by salary within each department. ```sql SELECT first_name, last_name, dept_id, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept FROM employees; ``` * **Logical Breakdown:** An analytical window function. `PARTITION BY` isolates department groupings, and `RANK` assigns rankings inside those boundaries. #### 21. Select employees earning above their department average using a CTE. ```sql WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ) SELECT e.first_name, e.last_name, e.salary, da.avg_salary FROM employees e JOIN dept_avg da ON e.dept_id = da.dept_id WHERE e.salary > da.avg_salary; ``` * **Logical Breakdown:** Builds a temporary modular named view on disk (`dept_avg`) which simplifies query planning and increases readability. #### 22. Simulate a Pivot Table showing annual department payrolls. ```sql SELECT dept_id, SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2020 THEN salary ELSE 0 END) AS salary_2020, SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2021 THEN salary ELSE 0 END) AS salary_2021, SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 2022 THEN salary ELSE 0 END) AS salary_2022 FROM employees GROUP BY dept_id; ``` * **Logical Breakdown:** Rotates rows into columns. Evaluates a conditional sum for each specified year, returning zero if there are no matches. --- ### 🔴 Part III: Advanced Database Architecture (Questions 23 - 35) Advanced topics dive into recursion, window framing, PL/pgSQL routines, triggers, isolation levels, and string builders. #### 23. Recursive CTE: Generate full manager-to-staff reporting trees. ```sql WITH RECURSIVE emp_hierarchy AS ( -- Anchor: Top-level managers SELECT id, first_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive path: Append child levels SELECT e.id, e.first_name, e.manager_id, h.level + 1 FROM employees e JOIN emp_hierarchy h ON e.manager_id = h.id ) SELECT * FROM emp_hierarchy ORDER BY level, manager_id; ``` * **Logical Breakdown:** Evaluates the anchor query once, then repeatedly executes the recursive join to build a deep hierarchical tree structure. #### 24. Compare current salary to adjacent salaries using LAG and LEAD. ```sql SELECT first_name, salary, LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary, LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary FROM employees; ``` * **Logical Breakdown:** Offsets index lookups. `LAG` pulls values from the preceding row, while `LEAD` pulls values from the following row within the ordered dataset. #### 25. Calculate a cumulative running salary total using window frames. ```sql SELECT first_name, hire_date, salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees ORDER BY hire_date; ``` * **Logical Breakdown:** Defines a sliding calculation frame. The cumulative sum aggregates values starting from the absolute first row up to the current row index. #### 26. Optimize queries by avoiding function wraps on index columns. ```sql -- SLOW: SELECT * FROM orders WHERE DATE(order_date) = '2023-01-15'; -- FAST & SARGABLE: SELECT * FROM orders WHERE order_date >= '2023-01-15' AND order_date < '2023-01-16'; ``` * **Logical Breakdown:** Wrapping column names in functions (like `DATE()`) prevents the database engine from leveraging existing column indexes. #### 27. PL/pgSQL: Stored procedure returning a newly created ID. ```sql CREATE OR REPLACE FUNCTION add_department(dept_name TEXT) RETURNS INTEGER AS $$ DECLARE new_id INTEGER; BEGIN INSERT INTO departments (name) VALUES (dept_name) RETURNING id INTO new_id; RETURN new_id; END; $$ LANGUAGE plpgsql; ``` * **Logical Breakdown:** Encapsulates insert routines on the server, avoiding network latency during multi-stage insertion executions. #### 28. Audit Trigger: Track and log salary adjustments. ```sql CREATE TABLE salary_audit ( employee_id INT, old_salary DECIMAL, new_salary DECIMAL, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE OR REPLACE FUNCTION log_salary_change() RETURNS TRIGGER AS $$ BEGIN IF NEW.salary <> OLD.salary THEN INSERT INTO salary_audit (employee_id, old_salary, new_salary) VALUES (NEW.id, OLD.salary, NEW.salary); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER salary_update_trigger AFTER UPDATE OF salary ON employees FOR EACH ROW EXECUTE FUNCTION log_salary_change(); ``` * **Logical Breakdown:** Binds a trigger block that fires automatically after target salary columns undergo updates, populating auditing ledgers. #### 29. Concurrency: Demonstate isolation anomalies. ```sql -- Session 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; SELECT salary FROM employees WHERE id = 1; -- Returns 50000 -- Session 2 (concurrently) UPDATE employees SET salary = 60000 WHERE id = 1; COMMIT; -- Session 1 again SELECT salary FROM employees WHERE id = 1; -- Returns 60000! (Non-repeatable read occurred) COMMIT; ``` * **Logical Breakdown:** READ COMMITTED guarantees that a transaction will never read uncommitted data, but allows other transactions to modify data mid-session. #### 30. Dynamic SQL Builder (PL/pgSQL). ```sql CREATE OR REPLACE FUNCTION search_employees( p_first_name TEXT DEFAULT NULL, p_last_name TEXT DEFAULT NULL, p_min_salary NUMERIC DEFAULT NULL ) RETURNS SETOF employees AS $$ DECLARE sql_query TEXT; BEGIN sql_query := 'SELECT * FROM employees WHERE 1=1'; IF p_first_name IS NOT NULL THEN sql_query := sql_query || ' AND first_name ILIKE ''%' || p_first_name || '%'''; END IF; IF p_last_name IS NOT NULL THEN sql_query := sql_query || ' AND last_name ILIKE ''%' || p_last_name || '%'''; END IF; IF p_min_salary IS NOT NULL THEN sql_query := sql_query || ' AND salary >= ' || p_min_salary; END IF; RETURN QUERY EXECUTE sql_query; END; $$ LANGUAGE plpgsql; ``` * **Logical Breakdown:** Concatentates search filters on-the-fly at runtime based on parameters provided, executing dynamic sql queries. #### 31. Concatenate employee names as a comma-separated string per department. ```sql SELECT dept_id, STRING_AGG(first_name || ' ' || last_name, ', ' ORDER BY last_name) AS employee_list FROM employees GROUP BY dept_id; ``` * **Logical Breakdown:** Aggregates textual records horizontally across rows, applying internal sorting on the concatenation flow. #### 32. Deduplicate records using ROW_NUMBER(). ```sql WITH numbered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, dept_id ORDER BY id DESC) AS rn FROM employees ) DELETE FROM employees WHERE (first_name, last_name, dept_id, id) IN ( SELECT first_name, last_name, dept_id, id FROM numbered WHERE rn > 1 ); ``` * **Logical Breakdown:** Assigns unique incremental indices per duplicate set. It keeps the absolute newest record (assigned `1`) and deletes all older duplicates (ranked `> 1`). #### 33. MERGE / UPSERT: Conditional database updates. ```sql MERGE INTO employees AS target USING (VALUES (101, 'Jane', 'Smith', 75000, 3)) AS source (id, first_name, last_name, salary, dept_id) ON target.id = source.id WHEN MATCHED THEN UPDATE SET salary = source.salary WHEN NOT MATCHED THEN INSERT (id, first_name, last_name, salary, dept_id) VALUES (source.id, source.first_name, source.last_name, source.salary, source.dept_id); ``` * **Logical Breakdown:** Conditionally updates rows if a key matches, or inserts a new row if there is no match. #### 34. Calculate continuous department medians and quartiles. ```sql SELECT dept_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary, PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS first_quartile, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS third_quartile FROM employees GROUP BY dept_id; ``` * **Logical Breakdown:** Computes interpolated values for exact continuous percentiles within ordered groupings. #### 35. Eliminate index-bypassing implicit data type conversions. ```sql -- BAD: SELECT * FROM customers WHERE phone_number = 123456789; -- GOOD: SELECT * FROM customers WHERE phone_number = '123456789'; ``` * **Logical Breakdown:** Comparing varchar columns to numeric literals forces the database engine to perform slow implicit type conversions, bypassing indexes on the column. --- ### ⚡ Part IV: Extreme Logic & Algorithmic Queries (Questions 36 - 50) This final tier tests your ability to solve complex, set-based mathematical challenges using advanced SQL techniques. #### 36. Gaps and Islands: Identify consecutive activity date ranges. * **Problem Statement:** For each customer, find all ranges of consecutive dates (where next date is exactly 1 day after the previous). * **Table Structure:** `customer_orders(customer_id, order_date)` (no duplicate dates per customer) ```sql WITH numbered AS ( SELECT customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM customer_orders ), grouped AS ( SELECT customer_id, order_date, DATE(order_date, '-' || rn || ' days') AS group_start FROM numbered ) SELECT customer_id, MIN(order_date) AS start_date, MAX(order_date) AS end_date, COUNT(*) AS days_in_range FROM grouped GROUP BY customer_id, group_start ORDER BY customer_id, start_date; ``` * **Extreme Logical Deep-Dive:** If a customer has orders on consecutive days, the difference between the actual dates and their sequential row numbers remains constant! Grouping on this constant value (`group_start`) allows us to isolate consecutive date islands. #### 37. Calculate the Median Salary per Department without built-in percentile functions. ```sql WITH ordered AS ( SELECT dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) AS rn_asc, COUNT(*) OVER (PARTITION BY dept_id) AS cnt FROM employees ) SELECT dept_id, AVG(salary) AS median FROM ordered WHERE rn_asc IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY dept_id; ``` * **Extreme Logical Deep-Dive:** `(cnt+1)/2` and `(cnt+2)/2` perform integer division to dynamically pinpoint the middle index for both odd and even counts, averaging the two middle rows for even populations. #### 38. Moving Average: Compute 7-day rolling revenue while accounting for missing calendar dates. ```sql WITH date_series AS ( SELECT generate_series(MIN(sale_date), MAX(sale_date), '1 day'::interval)::date AS dt FROM sales ), daily_sales AS ( SELECT ds.dt, COALESCE(SUM(s.amount), 0) AS total FROM date_series ds LEFT JOIN sales s ON ds.dt = s.sale_date GROUP BY ds.dt ) SELECT dt, total, AVG(total) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d FROM daily_sales ORDER BY dt; ``` * **Extreme Logical Deep-Dive:** First, `generate_series` constructs a continuous date axis. Performing a `LEFT JOIN` ensures days with zero activity are included as `0`. A window frame of `6 PRECEDING` averages exactly 7 continuous days. #### 39. Dense Top-3 earners per department, resolving ties. ```sql WITH ranked AS ( SELECT dept_id, name, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM employees ) SELECT * FROM ranked WHERE dr <= 3 ORDER BY dept_id, dr, name; ``` * **Extreme Logical Deep-Dive:** Standard `ROW_NUMBER` breaks ties arbitrarily, while `DENSE_RANK()` guarantees that tied salaries receive identical ranks without skipping index increments. #### 40. Grand-Manager Audit: Identify employees earning more than their manager's manager. ```sql SELECT e.name AS employee, e.salary AS emp_sal, m.name AS manager, g.name AS grand_manager, g.salary AS grand_sal FROM employees e JOIN employees m ON e.manager_id = m.id JOIN employees g ON m.manager_id = g.id WHERE e.salary > g.salary; ``` * **Extreme Logical Deep-Dive:** Self-joins the table twice. This traces recursive paths (Employee ➔ Manager ➔ Grand-Manager), automatically ignoring employees with no grand-manager. #### 41. Dynamic Pivot columns using String Aggregation inside Dynamic SQL. ```sql DO $$ DECLARE category_list TEXT; sql_query TEXT; BEGIN SELECT string_agg(DISTINCT format('SUM(CASE WHEN product_category = %L THEN revenue ELSE 0 END) AS %I', product_category, product_category), ', ') INTO category_list FROM sales; sql_query := format('SELECT region, %s FROM sales GROUP BY region', category_list); EXECUTE sql_query; END; $$; ``` * **Extreme Logical Deep-Dive:** Dynamically reads all unique product categories, generates a list of `SUM(CASE WHEN)` column projection statements, and executes the compiled SQL string. #### 42. Sessionization: Group actions into unique sessions based on a 30-minute inactivity threshold. ```sql WITH diffs AS ( SELECT user_id, activity_time, COALESCE(EXTRACT(EPOCH FROM (activity_time - LAG(activity_time) OVER (PARTITION BY user_id ORDER BY activity_time)))/60, 999) AS minutes_since_prev FROM user_activity ), sessions AS ( SELECT user_id, activity_time, SUM(CASE WHEN minutes_since_prev > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY activity_time) AS session_id FROM diffs ) SELECT user_id, MIN(activity_time) AS session_start, MAX(activity_time) AS session_end, COUNT(*) AS action_count FROM sessions GROUP BY user_id, session_id ORDER BY user_id, session_start; ``` * **Extreme Logical Deep-Dive:** `LAG` computes the time difference between consecutive actions. A conditional windowed `SUM` acts as an active counter, incrementing the session ID only when a gap exceeds 30 minutes. #### 43. Re-order rows sorting priority ASC, but displaying even IDs first, then odd IDs. ```sql SELECT * FROM tasks ORDER BY priority ASC, CASE WHEN id % 2 = 0 THEN 0 ELSE 1 END, id ASC; ``` * **Extreme Logical Deep-Dive:** Demonstrates sorting using arbitrary modulo expressions, segregating even/odd ids before applying secondary sort logic. #### 44. Recursive Graph Traversal: Find all simple paths between nodes A and F in a directed graph. ```sql WITH RECURSIVE paths AS ( -- Anchor: start at 'A' SELECT from_node, to_node, ARRAY[from_node, to_node] AS path_nodes, 1 AS depth FROM edges WHERE from_node = 'A' UNION ALL -- Recursive: extend path, but avoid cycles SELECT e.from_node, e.to_node, p.path_nodes || e.to_node, p.depth + 1 FROM paths p JOIN edges e ON p.to_node = e.from_node WHERE e.to_node <> ALL(p.path_nodes) AND p.depth < 10 ) SELECT path_nodes, depth FROM paths WHERE to_node = 'F' ORDER BY depth; ``` * **Extreme Logical Deep-Dive:** Accumulates nodes into a PostgreSQL array. The condition `e.to_node <> ALL(p.path_nodes)` prevents cyclical backtracking. #### 45. Sweep-Line Algorithm: Calculate the maximum concurrent events at any point in time. ```sql WITH points AS ( SELECT start_time AS time_point, +1 AS delta FROM events UNION ALL SELECT end_time, -1 FROM events ), sorted AS ( SELECT time_point, delta, SUM(delta) OVER (ORDER BY time_point, delta DESC) AS concurrent FROM points ) SELECT MAX(concurrent) AS max_concurrent FROM sorted; ``` * **Extreme Logical Deep-Dive:** Converts events into discrete points (+1 for start, -1 for end). Running a window sum across time points calculates active overlaps at any given moment. #### 46. Anti-Join: Locate dangling orphaned child rows. ```sql SELECT o.order_id, o.customer_id FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL; ``` * **Extreme Logical Deep-Dive:** Performs a LEFT JOIN to preserve child records. Filtering where parent key cells contain `NULL` isolates orphaned rows faster than slow `NOT IN` scans. #### 47. Keyset Pagination (Seek Method): Fast pagination without high offsets. ```sql SELECT order_id, order_date, amount FROM orders WHERE (order_date, id) < ('2023-01-15', 1234) ORDER BY order_date DESC, id DESC LIMIT 10; ``` * **Extreme Logical Deep-Dive:** Avoids performance bottlenecks associated with high offsets by comparing values as tuples, seeking target records in constant `O(log N)` time. #### 48. Full Outer Join: Compare actual sales to targets across all periods. ```sql SELECT COALESCE(s.region_id, t.region_id) AS region_id, COALESCE(s.month, t.month) AS month, COALESCE(s.amount, 0) AS actual_sales, t.target_amount, CASE WHEN s.amount > t.target_amount THEN 'above' WHEN s.amount < t.target_amount THEN 'below' WHEN s.amount = t.target_amount THEN 'exact' ELSE 'missing_data' END AS status FROM sales s FULL OUTER JOIN targets t ON s.region_id = t.region_id AND s.month = t.month ORDER BY region_id, month; ``` * **Extreme Logical Deep-Dive:** Uses `FULL OUTER JOIN` to preserve records from both sides, applying `COALESCE` to handle missing alignments cleanly. #### 49. Hierarchical Rollups using GROUPING SETS. ```sql SELECT region, city, product, SUM(revenue) AS total_revenue, GROUPING(region) AS grp_region, GROUPING(city) AS grp_city, GROUPING(product) AS grp_product, CASE WHEN GROUPING(region)=0 AND GROUPING(city)=0 AND GROUPING(product)=0 THEN 'detailed' WHEN GROUPING(region)=0 AND GROUPING(city)=0 AND GROUPING(product)=1 THEN 'city_subtotal' WHEN GROUPING(region)=0 AND GROUPING(city)=1 AND GROUPING(product)=1 THEN 'region_subtotal' WHEN GROUPING(region)=1 AND GROUPING(city)=1 AND GROUPING(product)=1 THEN 'grand_total' END AS level_desc FROM sales GROUP BY GROUPING SETS ( (), (region), (region, city), (product) ) ORDER BY region NULLS LAST, city NULLS LAST, product NULLS LAST; ``` * **Extreme Logical Deep-Dive:** Generates multiple aggregation groups in a single query pass. The `GROUPING()` function identifies which specific attributes are aggregated. #### 50. High-Speed Bulk Synchronous updates using subquery joins. ```sql UPDATE inventory i SET quantity = i.quantity + COALESCE(delta_sum.total_delta, 0) FROM ( SELECT product_id, warehouse_id, SUM(delta_quantity) AS total_delta FROM stock_moves WHERE move_date > '2023-01-01' GROUP BY product_id, warehouse_id ) AS delta_sum WHERE i.product_id = delta_sum.product_id AND i.warehouse_id = delta_sum.warehouse_id; ``` * **Extreme Logical Deep-Dive:** Aggregates increments inside a subquery before joining it to the master table in a single atomic update pass, avoiding slow row-by-row updates.