Advanced

SQL · Proficient - advanced patterns and performance

• HAVING and advanced aggregationsFilter after aggregation with HAVING and pivot your data with CASE WHEN inside aggregate functions.
• CTEs (WITH): making complex queries readableStructure your queries into named steps with WITH and chain CTEs like a pipeline of transformations.
• Window functions: ROW_NUMBER, RANK, SUM OVERCompute ranks, running totals, and within-group comparisons without collapsing rows thanks to OVER and PARTITION BY.
• Transactions: BEGIN, COMMIT, ROLLBACK and ACIDMake your writes atomic with transactions and understand what the ACID guarantees mean in day-to-day practice.
• Data modeling: normalization and denormalizationStructure your schemas from 1NF to 3NF to eliminate anomalies, and know when to denormalize with full awareness.