Advanced SQL
Design efficient, production-aware analytical SQL and diagnose why a correct query is still a bad query.
Performance
Execution plans: A plan describes how a database intends to execute a statement. Use it to explain work, not to hunt for a universally “bad” operator.
Indexes: An index is another structure the database maintains to help locate or order data. It improves some reads and costs space and write work.
Query optimization: Optimize an answer you have already validated. A faster wrong result is not an improvement.
Advanced patterns
Recursive CTEs: A recursive CTE repeatedly applies a query to a previous step. It is useful for hierarchies, paths and sequences.
Advanced window frames: The frame tells a window aggregate exactly which neighbouring rows to include. It is separate from the partition.
Pivoting and dynamic patterns: Pivoting turns category values into output columns. It changes presentation without removing the need for a defined grouping grain.
Data architecture
Dimensional modeling: A dimensional model separates measurable events from the descriptive context people use to analyse them.
Materialized views and partitions: Some workloads benefit from storing a precomputed result or splitting a large table into managed pieces. Both choices introduce operating responsibilities.
Transactions and isolation: A transaction groups changes into one unit of work. Isolation governs what concurrent work can see and how conflicts are handled.
Production practice
Stored routines and parameters: A stored routine packages database-side logic. A parameter supplies a value without constructing SQL by concatenating that value into the statement.
Security and access: An analyst needs enough access to do the job and no more. A learning database does not model the access controls of a shared production system.
Maintainability: SQL is an explanation that another person must be able to inspect, test and change. Write for that person as well as for the database.