Category: Analytics

Recursive queries extend SQL beyond ordinary set-based operations by allowing a query to repeatedly process the results produced by its previous iteration. The core mechanism is the recursive CTE, which consists of an anchor query that runs once to seed the working table and a recursive term that joins the previous iteration back to the base table. The process continues until an iteration produces no new rows. This makes recursive CTEs particularly useful for hierarchical structures such as employee reporting lines, category trees, organizational charts, and dependency relationships. A depth limit should be treated as a practical safety mechanism because a cycle in the underlying data can otherwise cause runaway recursion and excessive resource consumption.

Tree traversal is one of the most common applications of recursive SQL. To find descendants, the recursive join moves from a parent node to its children; to find ancestors, the direction is reversed. During traversal, the query can carry additional state such as the current depth and an accumulated path. A path array is particularly useful because it can provide a deterministic depth-first sort order while also acting as a cycle guard by preventing a node from being revisited. Recursive queries can also identify leaf nodes by checking for the absence of child rows. The same recursive pattern applies to bills of material, where each level multiplies the quantity required and the final results are aggregated by component to determine total material requirements across multiple branches.
Recursive CTEs can also operate on graph-shaped data rather than strict trees. When relationships are stored as an edge table, recursion can accumulate a visited path and a running cost or hop count to explore reachability and candidate routes. Cycle protection is essential because graphs can contain arbitrary loops, while a hop limit prevents the search space from growing without bound. The presentation recommends three main defences: explicitly tracking visited nodes, imposing a maximum depth or hop count, and using the SQL CYCLE clause where supported. These techniques allow SQL to handle modest graph problems such as reachability and path listing, although very large graph workloads may be better suited to specialized graph engines.
The way a hierarchy is stored has a major impact on query and write performance. An adjacency list stores a parent_id and is simple to maintain, with recursive CTEs performing traversal when needed. Materialised path stores the ancestry as a string or array, enabling index-friendly prefix or containment queries but making subtree moves more expensive. A closure table precomputes every ancestor-descendant relationship, turning many hierarchy reads into a straightforward indexed join at the cost of additional rows and more maintenance during updates. Nested sets provide very fast range-based reads but make inserts and structural changes expensive. For many applications, an adjacency list is the sensible starting point; a closure table or materialised path becomes attractive when hierarchy reads dominate the workload.
Performance depends heavily on the recursive join and the amount of data carried through each iteration. An index on the recursion column, such as parent_id in a hierarchy table, is essential because the recursive join executes repeatedly for each level. Filtering should be pushed into the anchor where possible, unnecessary columns should not be carried through the working table, and recursion should have an appropriate depth or hop limit. If the same hierarchy is queried extremely frequently, precomputing relationships in a closure table can remove recursion from the hot path. The broader principle is to match the storage model to the read/write workload while treating path tracking, cycle protection, depth limits, and appropriate indexing as core parts of production recursive SQL rather than optional additions.

https://docs.google.com/presentation/d/e/2PACX-1vRw94aHXskbtSD6klffQHulD2ZQP1B-kIMvcK2vbnyMDPH4uOLlVJaU-HPD3wEsNg/pub?start=true&loop=true&delayms=10000

SQL numeric functions are fundamental to producing accurate calculations, especially when working with money, percentages, measurements, and analytical data. The first decision is choosing the correct numeric type: exact integers such as SMALLINT, INT, and BIGINT are appropriate for counts, identifiers, and quantities; NUMERIC or DECIMAL should be used when decimal values must be stored exactly, particularly for financial data; and REAL, FLOAT, or DOUBLE are approximate binary types better suited to measurements, statistics, and machine-learning features. Decimal fractions such as 0.1 generally cannot be represented exactly in binary floating point, which can produce drift in calculations and unreliable equality comparisons. For financial applications, the presentation recommends exact NUMERIC values or integer minor units such as paise or cents rather than floating-point storage.
Rounding operations must also be chosen deliberately because ROUND, CEIL, FLOOR, and TRUNC perform different operations. ROUND returns the nearest value at a specified decimal scale, while CEIL and FLOOR move toward positive and negative infinity respectively. TRUNC, in contrast, removes digits toward zero, making it different from FLOOR for negative values. The deck also highlights that rounding behaviour can depend on both the database engine and numeric type, including differences between half-up and half-even behaviour. Integer division is another common source of silent errors: dividing two integers can discard the fractional component, so percentage calculations should explicitly force numeric division and protect the denominator with NULLIF. Functions such as MOD, POWER, SQRT, ABS, SIGN, GREATEST, LEAST, and WIDTH_BUCKET extend SQL’s arithmetic capabilities for reporting, bucketing, growth calculations, and statistical analysis.
Explicit conversion with CAST is preferable to relying on implicit type conversion because implicit casts can introduce both correctness and performance problems. The presentation shows that CAST(x AS type) is the ANSI form, while PostgreSQL also supports the x::type shorthand. Dirty text data should be validated before conversion, or TRY_CAST/TRY_CONVERT can be used where supported to turn failed conversions into NULL rather than aborting the query. A particularly important performance issue occurs when a cast is applied to an indexed column: a predicate such as amount::int = 100 may prevent a normal index from being used because the database must evaluate an expression on the column. Casting the parameter to the column’s native type is generally preferable. Similarly, text columns containing numeric identifiers should be compared with appropriately typed text literals rather than forcing the database to convert the entire column.
Financial calculations require particular attention to precision and the order of operations. The deck recommends keeping intermediate calculations exact and rounding at the business-defined reporting point rather than repeatedly rounding intermediate values. For example, line-level tax and totals can be calculated using exact numerics and rounded once according to the invoice’s reporting rules. Importantly, the sum of individually rounded lines can differ slightly from the rounded sum of exact values, so reconciliation logic may be necessary. Integer minor units provide another robust approach for monetary storage because they eliminate decimal representation issues while retaining exact arithmetic. Weighted averages should use the sum of products divided by the sum of weights rather than a simple average when observations carry different quantities, while functions such as PERCENTILE_CONT can provide median and percentile measures.
The broader lesson is that numeric correctness and query performance are closely connected to data types and conversion choices. Integers are efficient for keys and counts, NUMERIC provides exact decimal arithmetic for values requiring precision, and approximate floating-point types should be reserved for domains where approximation is acceptable. Common mistakes include storing money as FLOAT, accidentally performing integer division, allowing division by zero, rounding too early, casting indexed columns inside predicates, and assuming a particular rounding mode without testing the database and data type involved. Keeping predicates cast-free, choosing the narrowest exact type that fits the domain, using NULLIF for safe ratios, and documenting the business rounding rule produces SQL that is both more reliable and easier for the database optimizer to execute efficiently.

https://docs.google.com/presentation/d/e/2PACX-1vQ8gQMBGU8nrFwgsfaEpJ6WICMhwSzcevAhSnm9eLwp3IXavKma02MQ1kiV1ePDtg/pub?start=true&loop=true&delayms=10000

Modern relational databases can handle semi-structured data without abandoning the relational model, making JSON particularly useful when part of a schema is genuinely variable. JSON works well for sparse category-specific attributes, external API or webhook payloads, and audit snapshots that need to preserve an original document. However, core entities that are regularly joined, constrained, filtered, sorted, or aggregated are generally better represented by typed relational columns. Putting an entire application schema into a single JSON document sacrifices constraints, data types, and useful planner statistics, while repeated extraction and casting can add unnecessary processing overhead. The practical approach is therefore to use JSON for the variable portion of a model while keeping frequently queried business data relational.

For PostgreSQL workloads, jsonb is generally the preferred type when JSON needs to be queried. Unlike plain json, which preserves the original textual representation, jsonb stores parsed binary data and supports indexing and containment operations. The deck’s extraction model is built around an important distinction: -> returns a JSON/JSONB value that can be navigated further, while ->> extracts text for comparison or casting. Path operators such as #> and #>> provide another way to reach nested values. JSON arrays can also be expanded into relational rows with functions such as JSONB_ARRAY_ELEMENTS, allowing nested items to be grouped, aggregated, and analysed like ordinary table data. PostgreSQL’s documentation confirms these extraction operators and JSONB-specific querying capabilities.
JSONB becomes especially powerful when filtering and indexing are designed around the actual access pattern. Containment with @> and key-existence operators such as ?, ?|, and ?& can be accelerated with GIN indexes, while expression indexes are useful when one extracted field is queried repeatedly. PostgreSQL provides both the default jsonb_ops GIN operator class and the more specialized jsonb_path_ops; the latter supports fewer operators but can provide better performance for supported containment and JSON-path workloads. The deck also highlights partial indexes for frequently queried subsets of data. The important principle is that indexing JSON is not simply about adding a GIN index everywhere: the index type should match the predicates the application actually executes.
JSON can also be generated directly from relational data, allowing databases to construct nested API responses using functions such as JSONB_BUILD_OBJECT and JSONB_AGG. At the modelling layer, the deck recommends promoting a JSON attribute to a typed column when it becomes a regular filtering, joining, constraint, sorting, or aggregation target. Generated columns can provide a practical bridge by extracting a frequently queried JSON value into a typed, indexable field while retaining the original document. This approach also addresses an important performance issue: repeated JSON extraction and casting consumes CPU, large documents may require additional storage and decompression work, and JSON predicates can provide weaker planner statistics than ordinary typed columns.
The central lesson is to treat JSON as a deliberate modelling tool rather than a replacement for relational design. Avoid confusing -> with ->>, compare numeric JSON values only after appropriate casting, index frequently queried keys, avoid putting frequently updated counters inside JSONB when whole-row rewrites would become expensive, and remember that JSONB_AGG returns NULL for an empty input unless it is wrapped with COALESCE. A robust production pattern is to retain raw webhook or external payloads, promote frequently accessed fields into generated or typed columns, expand nested arrays only when analytical queries require them, and aggregate the resulting rows at the appropriate grain. In this hybrid model, relational columns provide structure, constraints, statistics, and efficient access, while JSONB provides the flexibility needed for genuinely variable data.

https://docs.google.com/presentation/d/e/2PACX-1vSKTdTyPxNfLWz6ehjy4aYK_VaXrZde1mWMimU3LWI8l-69AgxISUqyndqT2aP1yA/pub?start=true&loop=true&delayms=10000

A particularly important aspect of SQL Joins is filtering correctly, especially when working with outer joins. The presentation demonstrates the difference between conditions placed in ON and WHERE: with an outer join, a condition on the optional table in WHERE can eliminate the NULL-padded rows and effectively turn a LEFT JOIN into an INNER JOIN. It also examines USING, NATURAL JOIN, compound keys, multi-table join chains, and joining to pre-aggregated subqueries. USING can provide concise syntax when column names match, whereas NATURAL JOIN is considered fragile because a newly added same-named column can unexpectedly change the join condition.

The presentation also goes beyond conventional joins to cover semi-joins, anti-joins, non-equi joins, range joins, and LATERAL joins. EXISTS can answer whether a matching record exists without duplicating the rows from the left table, while NOT EXISTS provides a NULL-safe way to find records with no match. Non-equi and range joins are useful when relationships depend on conditions such as ranges, validity periods, or tiers rather than simple equality. LATERAL allows a subquery to reference the current row from the left side, making it particularly useful for patterns such as retrieving the top three most recent orders for every customer.

Join performance depends heavily on the database engine’s execution strategy and on maintaining the correct grain of the result. The presentation explains three major physical join algorithms—Nested Loop, Hash Join, and Merge Join—and highlights the importance of reading EXPLAIN ANALYZE rather than guessing about performance. It also addresses the fan-out problem, where joining a one-to-many table before aggregation can multiply rows and artificially inflate sums or counts. Practical recommendations include indexing both sides of join keys, keeping key data types consistent, filtering before joining when appropriate, selecting only required columns, pre-aggregating one-to-many branches, and checking estimated versus actual row counts. These principles help produce SQL queries that are both accurate and efficient.

https://docs.google.com/presentation/d/e/2PACX-1vQUq-PqRJgAGmifrj8Uef22nuj3CZEPHSr6IiVEPmatp5W8QE7muYclRBfL2X4UuQ/pub?start=true&loop=true&delayms=10000

SQL Views provide a powerful mechanism for simplifying complex queries, improving data security, and presenting customized perspectives of data without storing duplicate information. A view is a virtual table created from the result of a SQL query, allowing users to access data as if it were stored in a regular table. As discussed in this presentation, views help abstract complex joins, filters, and aggregations, making SQL queries easier to write, maintain, and understand. They are widely used in enterprise databases to provide consistent business logic, simplify reporting, and restrict direct access to sensitive tables.

The presentation explains the process of creating and managing views using the CREATE VIEW statement. Since views store only the SQL query rather than the actual data, they always reflect the latest changes in the underlying tables whenever queried. SQL also supports updatable views, enabling users to perform INSERT, UPDATE, and DELETE operations under specific conditions, while read-only views protect data from accidental modifications. Advanced options such as WITH CHECK OPTION ensure that updates made through a view continue to satisfy the view’s filtering conditions, preserving data integrity and consistency.

One of the key advantages highlighted in the presentation is the role of views in database security and application development. Instead of granting users direct access to base tables, administrators can expose only the necessary columns and rows through carefully designed views. This approach protects confidential information while allowing different departments to work with the same database according to their access privileges. The presentation also discusses materialized views, which physically store query results to improve performance for computationally expensive reports and analytical workloads. Although materialized views require periodic refreshing, they significantly reduce query execution time for large datasets and business intelligence applications.

SQL Views are extensively used in reporting systems, dashboards, data warehouses, enterprise resource planning (ERP) applications, and financial systems where reusable and secure data access is essential. By encapsulating complex business logic into reusable database objects, views improve maintainability, reduce code duplication, and provide a stable interface even when underlying database schemas evolve. Mastering SQL Views enables database developers and data analysts to build scalable, secure, and efficient database applications while simplifying data access for end users.

https://docs.google.com/presentation/d/e/2PACX-1vTsSha8bQUUJYfl4Dg5I3SCwNbkJGDsXUkauF4_KOaaGOjOSiNmz-n5WmkAcpK-yg/pub?start=true&loop=true&delayms=10000

Modern business reports often require data to be summarized at multiple levels, such as detailed records, regional subtotals, and grand totals, all within a single report. The presentation “SQL Grouping Sets, ROLLUP & Pivoting” introduces advanced SQL aggregation techniques that simplify these reporting requirements while improving query performance. Instead of writing multiple GROUP BY queries combined with UNION ALL, SQL provides powerful constructs like GROUPING SETS, ROLLUP, and CUBE, allowing multiple aggregation levels to be computed in a single scan of the data. These features are invaluable for financial reporting, business intelligence dashboards, and multidimensional data analysis.

The presentation explains that GROUPING SETS gives developers complete control over which aggregation levels should appear in the final result. Instead of repeatedly scanning the same table, a single query can generate detailed rows, subtotals, and grand totals simultaneously. It also explores ROLLUP, which automatically creates hierarchical summaries—ideal for dimensions such as Country → State → City—and CUBE, which generates every possible combination of multiple dimensions for multidimensional analysis. To distinguish actual NULL values from subtotal rows, SQL provides the GROUPING() and GROUPING_ID() functions, enabling accurate labeling, filtering, and sorting of aggregated results.

Another major topic covered is Pivoting and Unpivoting, techniques that transform data between row-oriented and column-oriented formats. Pivoting converts rows into columns using conditional aggregation with FILTER or CASE expressions, making it easier to build cross-tab reports such as quarterly sales summaries or performance dashboards. Conversely, Unpivoting converts wide datasets back into a normalized row format, simplifying further analysis and reporting. The presentation also demonstrates how these techniques can be combined with GROUPING SETS to create comprehensive management reports containing quarterly metrics, regional subtotals, percentage contributions, and grand totals—all generated from a single SQL query.

The presentation concludes with practical guidance on portability, performance optimization, and common pitfalls. While ROLLUP is widely supported across database systems, features such as CUBE, GROUPING SETS, and PIVOT vary between SQL dialects, making conditional aggregation a highly portable alternative. Developers are also advised to avoid unnecessary CUBE operations on high-cardinality columns, recompute ratios at each aggregation level rather than averaging subtotals, and request only the grouping levels actually required by the report. By mastering SQL Grouping Sets, ROLLUP & Pivoting, database professionals can build faster, more maintainable, and highly expressive analytical queries that power sophisticated reporting and decision-making systems.

https://docs.google.com/presentation/d/e/2PACX-1vR2qwQgUWlb_zhlU9DfTqRjvBD2IVn8pDvASl5MJWMw-xX6MGX5HTfHd0XFz31nWg/pub?start=true&loop=true&delayms=10000

Handling dates and times correctly is essential for building reliable database applications and analytical reports. The SQL Date & Time Functions presented in this PPT provide the foundation for working with temporal data, including storing dates, performing calendar arithmetic, grouping records by time periods, handling time zones, and optimizing date-based queries. From sales dashboards and financial reporting to event logging and user activity analysis, almost every production database relies on accurate temporal operations. Choosing the appropriate data type—such as DATE, TIME, TIMESTAMP, TIMESTAMPTZ, or INTERVAL—is the first step toward ensuring correctness and avoiding common issues related to time zones and daylight saving time.

The presentation explores several essential SQL functions used to manipulate and analyze temporal data. Functions like CURRENT_DATE, NOW(), and CURRENT_TIMESTAMP retrieve the current date and time, while DATE_TRUNC() groups records into meaningful periods such as days, weeks, months, or years for reporting purposes. In contrast, EXTRACT() retrieves individual components like the year, month, weekday, or hour, making it ideal for filtering and seasonal analysis. The PPT also demonstrates INTERVAL arithmetic for adding or subtracting time durations, calculating ages using the AGE() function, and safely handling month-end calculations without manually counting days. These functions enable developers to write concise, readable, and accurate SQL queries for a wide range of business scenarios.

Another major focus of the presentation is the correct handling of time zones and date filtering. The recommended practice is to store event timestamps as TIMESTAMPTZ in UTC and convert them to the user’s local time only when displaying results using AT TIME ZONE. The presentation also explains why half-open date ranges (>= start_date AND < next_date) are preferred over BETWEEN when filtering timestamps, as they prevent missing records from the final day and allow database indexes to remain effective. Additional practical techniques include generating continuous calendars using GENERATE_SERIES, filling missing dates with zero values through LEFT JOIN, formatting dates with TO_CHAR, and implementing efficient rolling reports, cohort analyses, and business-day calculations.

Beyond functionality, the presentation emphasizes writing high-performance SQL by avoiding functions on indexed date columns, using B-tree indexes for temporal queries, and partitioning large tables by date ranges. It also highlights common mistakes such as storing timestamps without time zone information, grouping by formatted date strings, assuming every month has 30 days, and dividing days by 365 when calculating ages. By mastering SQL Date & Time Functions, database developers and data analysts can build scalable, accurate, and efficient applications that handle temporal data correctly while producing reliable business insights across reporting, analytics, and operational systems.

https://docs.google.com/presentation/d/e/2PACX-1vTtrQgMuLKf5Yx5cGl5-GQ3n2e35jYrtiTdM9nx6ty5srA4sRpbohct-gY-wat7kA/pub?start=true&loop=true&delayms=10000