Database Engineering & Data Systems Topics
Database design patterns, optimization, scaling strategies, storage technologies, data warehousing, and operational database management. Covers database selection criteria, query optimization, replication strategies, distributed databases, backup and recovery, and performance tuning at database layer. Distinct from Systems Architecture (which addresses service-level distribution) and Data Science (which addresses analytical approaches).
Structured Query Language Join Operations
Comprehensive coverage of Structured Query Language join types and multi table query patterns used to combine relational data and answer business questions. Topics include inner join, left join, right join, full outer join, cross join, self join, and anti join patterns implemented with NOT EXISTS and NOT IN. Candidates should understand equi joins versus non equi joins, joining on expressions and composite keys, and how join choice affects row counts and null semantics. Practical skills include translating business requirements into correct join logic, chaining joins across two or more tables, constructing multi table aggregations, handling one to many relationships and duplicate rows, deduplication strategies, and managing orphan records and referential integrity issues. Additional areas covered are join conditions versus WHERE clause filtering, aliasing for readability, using functions such as coalesce to manage null values, avoiding unintended Cartesian products, and basic performance considerations including join order, appropriate indexing, and interpreting query execution plans to diagnose slow joins. Interviewers may probe result correctness, edge cases such as null and composite key behavior, and the candidate ability to validate outputs against expected business logic.
Aggregation and Grouping
Covers SQL grouping and aggregation concepts used to summarize data across rows. Key skills include using GROUP BY with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, counting distinct values, and filtering grouped results with HAVING while understanding the difference between WHERE and HAVING. Candidates should demonstrate correct handling of NULL values in aggregates, grouping by expressions and multiple columns, and writing multi level aggregations using ROLLUP, CUBE, and GROUPING SETS. Also important is knowing when to use subqueries or common table expressions for intermediate aggregation, the difference between aggregate functions and window functions, and how grouping interacts with joins and data types. Interview questions may test correctness of queries, edge cases, performance considerations such as appropriate indexes and query plans, and the ability to transform business questions like who are the top customers or which categories have declining sales into correct aggregated SQL statements.
SQL Fundamentals and Query Writing
Comprehensive query writing skills from basic to intermediate level. Topics include SELECT and WHERE, joining tables with inner and outer joins, grouping with GROUP BY and filtering groups with HAVING, common aggregation functions such as COUNT SUM AVG MIN and MAX, ORDER BY and DISTINCT, subqueries and common table expressions, basic window functions such as ROW_NUMBER and RANK, union operations, and principles of readable and maintainable query composition. Also covers basic query execution awareness and common performance pitfalls and how to write correct, efficient queries for combining and summarizing relational data.
Advanced SQL Window Functions
Mastery of Structured Query Language window functions and advanced aggregation techniques for analytical queries. Core function families include ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE; offset functions such as LAG and LEAD; value functions such as FIRST_VALUE, LAST_VALUE, and NTH_VALUE; and aggregate window expressions such as SUM OVER and AVG OVER. Candidates should understand the OVER clause with PARTITION BY and ORDER BY, frame specifications using ROWS BETWEEN and RANGE BETWEEN, tie handling, null behavior, and how frame definitions affect results. Common application patterns include top N per group, deduplication using row numbering, running totals and cumulative aggregates, moving averages, percent rank and distribution calculations, event sequencing and period over period comparisons, gap and island analysis, cohort and retention analysis, and trend and growth calculations. The topic also covers structuring complex queries with Common Table Expressions including recursive Common Table Expressions to break multi step analytical pipelines and to handle hierarchical or iterative problems, and choosing between window functions, GROUP BY, joins, and subqueries for correctness and readability. Performance and correctness considerations are essential, including join and sort costs, index usage, memory and sort spill behavior, execution planning and query optimization techniques, and trade offs across different database dialects and large data volumes. Interview assessments typically ask candidates to write and explain queries that use these functions, reason about frame semantics for edge cases such as ties, nulls, and partition boundaries, and to rewrite or optimize expensive queries.
Query Optimization and Execution Plans
Focuses on diagnosing slow queries and reducing execution cost through analysis of query execution plans and systematic query rewrites. Candidates should be able to read and interpret explain output and execution plans including identifying expensive operators such as sequential table scans index scans sorts nested loop join hash join and merge join and explaining why those operators appear. Core skills include cost and cardinality estimation understanding join order and predicate placement predicate pushdown and selectivity reasoning comparing exists versus in versus join patterns and identifying common anti patterns such as N plus one queries. The topic covers profiling and benchmarking approaches using explain analyze and runtime statistics comparing estimated and actual row counts proposing and validating query rewrites and configuration or schema changes and reasoning about trade offs when using materialized views caching denormalization or partitioning to improve performance. Candidates should present step by step approaches to diagnose problems measure improvements and assess impact on other workloads.
Advanced Querying with Structured Query Language
Covers authoring correct, maintainable, and high quality Structured Query Language statements for analytical and transactional problems. Candidates should demonstrate writing Select Insert Update and Delete statements and using filtering grouping ordering and aggregation correctly. Emphasis is on complex query constructs and patterns such as multi table joins and join condition logic self joins for hierarchical data nested and correlated subqueries common table expressions including recursive common table expressions window functions such as row number rank dense rank lag and lead set operations like union and union all and techniques for calculating running totals moving averages cohort metrics and consecutive event detection. Candidates should be able to break down and refactor complex requirements into composable queries for readability and maintainability while reasoning about performance implications on large data sets. Senior expectations may include mentoring on best practices for query composition and understanding how schema and configuration choices influence query performance.