Advanced SQL and Analytical QueriesIntroductionOperational databases answer questions such as “Which order has identifier 1842?” Analytical queries answer broader questions: “How does this order compare with previous orders?”, “Which regions are growing?”, or “What paths exist through an organizational hierarchy?” These questions require operations extending beyond simple filtering, joins, and grouping. This chapter develops advanced SQL techniques in PostgreSQL and MySQL. It emphasizes semantic correctness, executable patterns, performance, and maintainability. Although the two database systems implement much of standard SQL, important differences exist in JSON, arrays, regular expressions, temporal functions, and full-text search. Learning ObjectivesAfter completing this chapter, students should be able to: - use window functions for ranking, comparison, and cumulative analysis;
- control window partitions, ordering, and frames;
- construct safe recursive common table expressions;
- perform conditional aggregation and static pivot-like reporting;
- query and update JSON documents correctly;
- use PostgreSQL arrays and comparable MySQL JSON-array patterns;
- apply temporal, textual, and regular-expression operations;
- create and maintain full-text search indexes;
- combine advanced SQL features in operational analytics; and
- evaluate performance, security, governance, and portability concerns.
Working SchemaThe examples use an online retail schema. A crucial relationship is that region belongs to customers, not orders. Consequently, regional analysis must join the two tables. CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(200) NOT NULL,
region VARCHAR(50) NOT NULL,
profile JSON
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
ordered_at TIMESTAMP NOT NULL,
status VARCHAR(20) NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CHECK (total_amount >= 0)
);
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
description TEXT,
category VARCHAR(80) NOT NULL,
price DECIMAL(12,2) NOT NULL,
CHECK (price >= 0)
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL,
unit_price DECIMAL(12,2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
CHECK (quantity > 0),
CHECK (unit_price >= 0)
);
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_name VARCHAR(200) NOT NULL,
manager_id BIGINT,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);PostgreSQL applications commonly replace JSON with JSONB for indexing and efficient document operations. MySQL provides a binary-backed native JSON type but does not provide PostgreSQL-style SQL array columns. Window FunctionsA window function evaluates a group of rows related to the current row without collapsing that group into one result row. This distinguishes it from GROUP BY, which reduces each group to one row. A window specification may contain: PARTITION BY: divides rows into independent groups;ORDER BY: establishes sequence within each partition; and- a frame clause: determines which ordered rows contribute to the current calculation.
RankingSELECT
customer_id,
order_id,
ordered_at,
total_amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY total_amount DESC, order_id
) AS row_number,
RANK() OVER (
PARTITION BY customer_id
ORDER BY total_amount DESC
) AS amount_rank,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY total_amount DESC
) AS dense_amount_rank
FROM orders;ROW_NUMBER always generates distinct consecutive numbers. RANK gives tied rows the same rank and leaves gaps afterward. DENSE_RANK gives ties the same rank without gaps. A deterministic tie-breaker such as order_id should be included when exactly one row must be selected.
Because window functions are evaluated after WHERE, their aliases cannot ordinarily be filtered in the same query block. PostgreSQL and MySQL can use a derived table or CTE: WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC, order_id DESC
) AS rn
FROM orders AS o
)
SELECT *
FROM ranked
WHERE rn = 1;This returns the latest order for each customer. Navigation FunctionsLAG accesses an earlier row, while LEAD accesses a later row.
SELECT
customer_id,
order_id,
ordered_at,
total_amount,
LAG(total_amount) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
) AS previous_amount,
total_amount - LAG(total_amount) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
) AS change_from_previous
FROM orders;The first row in each partition has no predecessor, so LAG returns NULL unless a default is supplied. Preserving NULL is often preferable because zero would incorrectly imply that a prior order existed. Running Totals and Window FramesSELECT
ordered_at,
order_id,
total_amount,
SUM(total_amount) OVER (
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders
WHERE status = 'completed';The explicit ROWS frame processes physical rows in the stated order. A default RANGE frame can include all peer rows sharing the same ordering value, which may produce unexpected jumps when timestamps or dates tie. For a three-row moving average, let xi be the value at position i. Where three observations exist, the moving average is: MAi = (xi + xi-1 + xi-2) ÷ 3 At a partition boundary, SQL averages only the available non-NULL rows: SELECT
ordered_at,
total_amount,
AVG(total_amount) OVER (
ORDER BY ordered_at, order_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_average
FROM orders;If consecutive values are 120, 180, and 300 monetary units, the third moving average is (120 + 180 + 300) ÷ 3 = 200. Distribution FunctionsNTILE(4) divides ordered rows into four buckets whose sizes differ by at most one. PERCENT_RANK expresses relative rank from 0 to 1, while CUME_DIST gives the proportion of rows at or below the current ordering value. These functions are useful for segmentation, but small populations and ties require cautious interpretation.
Conditional Aggregation and Pivot-Like ReportsConditional aggregation places a CASE expression inside an aggregate. It is portable and usually preferable to generating many nearly identical queries. SELECT
c.region,
COUNT(*) AS total_orders,
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END)
AS completed_orders,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END)
AS cancelled_orders,
SUM(CASE WHEN o.status = 'completed'
THEN o.total_amount ELSE 0 END) AS completed_revenue
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
GROUP BY c.region
ORDER BY c.region;The join is mandatory because region is stored in customers. COUNT(*) counts orders after the join. If one-to-many tables such as order_items were also joined, order counts could be inflated; pre-aggregation or COUNT(DISTINCT o.order_id) would then be needed. A conditional share can be expressed as: p = nc ÷ n where nc is the number satisfying the condition and n is the total number. Division by zero must be prevented: SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) * 1.0
/ NULLIF(COUNT(*), 0) AS completion_rate Static Pivot PatternSQL result sets are naturally row-oriented, but conditional aggregation can turn known category values into columns: SELECT
c.region,
SUM(CASE WHEN EXTRACT(YEAR FROM o.ordered_at) = 2025
AND EXTRACT(QUARTER FROM o.ordered_at) = 1
THEN o.total_amount ELSE 0 END) AS q1_revenue,
SUM(CASE WHEN EXTRACT(YEAR FROM o.ordered_at) = 2025
AND EXTRACT(QUARTER FROM o.ordered_at) = 2
THEN o.total_amount ELSE 0 END) AS q2_revenue,
SUM(CASE WHEN EXTRACT(YEAR FROM o.ordered_at) = 2025
AND EXTRACT(QUARTER FROM o.ordered_at) = 3
THEN o.total_amount ELSE 0 END) AS q3_revenue,
SUM(CASE WHEN EXTRACT(YEAR FROM o.ordered_at) = 2025
AND EXTRACT(QUARTER FROM o.ordered_at) = 4
THEN o.total_amount ELSE 0 END) AS q4_revenue
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.region;This is a static pivot because its columns are known when the statement is written. Dynamic pivots require generated SQL and should validate identifiers against an allow-list. Values must remain bound parameters rather than string-concatenated input. PostgreSQL also supports the concise aggregate FILTER syntax, but MySQL does not: COUNT(*) FILTER (WHERE status = 'completed') Common Table Expressions and RecursionA nonrecursive CTE assigns a name to an intermediate relation. It improves decomposition, although it does not automatically guarantee faster execution. A recursive CTE consists of an anchor query, UNION ALL, and a recursive query. Recursion ends when the recursive query produces no additional rows. Safe Hierarchy Traversal in PostgreSQLThe following query starts with employee 10, follows subordinates, records the path, prevents revisiting an employee already in that path, and enforces a maximum depth: WITH RECURSIVE hierarchy AS (
SELECT
employee_id,
employee_name,
manager_id,
0 AS depth,
ARRAY[employee_id] AS path
FROM employees
WHERE employee_id = 10
UNION ALL
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
h.depth + 1,
h.path || e.employee_id
FROM employees AS e
JOIN hierarchy AS h
ON e.manager_id = h.employee_id
WHERE h.depth < 20
AND NOT (e.employee_id = ANY(h.path))
)
SELECT *
FROM hierarchy
ORDER BY path;The array implements a cycle check. The depth guard limits damage from unusually deep or corrupt structures. The chosen limit of 20 is a business rule, not a universal mathematical limit. PostgreSQL also supports standard SEARCH and CYCLE clauses in current releases, but explicit paths remain useful for display and debugging. Safe Hierarchy Traversal in MySQLMySQL can represent the visited path as a comma-separated string when identifiers are integers: WITH RECURSIVE hierarchy AS (
SELECT
employee_id,
employee_name,
manager_id,
0 AS depth,
CAST(employee_id AS CHAR(2000)) AS path
FROM employees
WHERE employee_id = 10
UNION ALL
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
h.depth + 1,
CONCAT(h.path, ',', e.employee_id)
FROM employees AS e
JOIN hierarchy AS h
ON e.manager_id = h.employee_id
WHERE h.depth < 20
AND FIND_IN_SET(e.employee_id, h.path) = 0
)
SELECT *
FROM hierarchy;The cast reserves sufficient width because MySQL infers the recursive column type from the anchor member. For arbitrary textual identifiers, a JSON array is safer than comma separation. Production systems should also validate hierarchical integrity during writes and index manager_id. Recursive CTEs are useful for trees, bills of materials, graph reachability, date generation, and dependency traversal. They may perform poorly on highly connected graphs because the number of paths can grow rapidly. JSON and Array FacilitiesJSON is appropriate for attributes that are genuinely variable or nested. Stable entities and relationships should ordinarily remain normalized so that constraints, joins, and indexes remain effective. PostgreSQL JSONBAssume customers.profile has been converted to JSONB: ALTER TABLE customers
ALTER COLUMN profile TYPE JSONB
USING profile::JSONB; Extraction operators distinguish JSON values from text: SELECT
customer_id,
profile -> 'preferences' AS preferences_json,
profile -> 'preferences' ->> 'theme' AS theme_text
FROM customers;Containment can be indexed using GIN: CREATE INDEX idx_customer_profile_gin
ON customers USING GIN (profile);
SELECT customer_id
FROM customers
WHERE profile @> '{"membership":"gold"}'::jsonb;jsonb_set(target, path, value, create_if_missing) can create a missing final key when create_if_missing is true, but all intermediate path components must already exist. Thus this works only when preferences exists as an object:
UPDATE customers
SET profile = jsonb_set(
profile,
'{preferences,theme}',
'"dark"'::jsonb,
true
)
WHERE customer_id = 42;To safely create or preserve the intermediate object, rebuild that level: UPDATE customers
SET profile = COALESCE(profile, '{}'::jsonb)
|| jsonb_build_object(
'preferences',
COALESCE(profile -> 'preferences', '{}'::jsonb)
|| jsonb_build_object('theme', 'dark')
)
WHERE customer_id = 42;This pattern assumes that an existing preferences value is an object. Data validation should reject incompatible types. MySQL JSONSELECT
customer_id,
profile->>'$.preferences.theme' AS theme
FROM customers;
UPDATE customers
SET profile = JSON_SET(profile, '$.preferences.theme', 'dark')
WHERE customer_id = 42;Applications should test expected document shapes rather than assume that every requested nested path can be constructed from malformed or incompatible existing values. Frequently queried JSON properties can be exposed through generated columns and indexed. PostgreSQL ArraysPostgreSQL arrays are useful for small, homogeneous collections attached to one row: ALTER TABLE products ADD COLUMN tags TEXT[];
UPDATE products
SET tags = ARRAY['database', 'backend']
WHERE product_id = 7;
SELECT product_id, name
FROM products
WHERE tags @> ARRAY['database'];
CREATE INDEX idx_products_tags_gin
ON products USING GIN (tags); Arrays should not replace relational tables when elements require their own attributes, references, uniqueness rules, or frequent individual updates. MySQL applications commonly use JSON arrays or normalized child tables instead. Temporal AnalysisTime-based analysis must distinguish timestamps, dates, durations, time zones, and calendar boundaries. Grouping and Interval ArithmeticPostgreSQL: SELECT DATE_TRUNC('month', ordered_at) AS month_start,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY month_start;
SELECT *
FROM orders
WHERE ordered_at >= CURRENT_TIMESTAMP - INTERVAL '30 days';MySQL: SELECT DATE_FORMAT(ordered_at, '%Y-%m-01') AS month_start,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m-01')
ORDER BY month_start;
SELECT *
FROM orders
WHERE ordered_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY;For index-friendly range filtering, avoid applying a function to the indexed timestamp in WHERE. Use half-open intervals: WHERE ordered_at >= '2025-01-01'
AND ordered_at < '2025-02-01' Half-open intervals avoid ambiguity at the final instant and work correctly with fractional seconds. Time-zone-aware applications should store instants consistently—often as PostgreSQL TIMESTAMPTZ or UTC values—and convert to the user’s zone for presentation. A “day” in local civil time is not always exactly 24 hours because of daylight-saving transitions. Text Processing and Regular ExpressionsBasic text functions include LOWER, UPPER, TRIM, LENGTH, SUBSTRING, REPLACE, and concatenation. Case normalization can support matching, but locale and collation influence results. PostgreSQL regular-expression operators include ~ for case-sensitive matching and ~* for case-insensitive matching: SELECT customer_id, customer_name
FROM customers
WHERE customer_name ~* '^[a-z][a-z .''-]{1,99}$';MySQL 8 uses REGEXP_LIKE: SELECT customer_id, customer_name
FROM customers
WHERE REGEXP_LIKE(
customer_name,
'^[a-z][a-z .''-]{1,99}$',
'i'
);Regular expressions are useful for classification, extraction, and data-quality checks, but they are not complete validators for complex standards such as email addressing. Patterns should be bounded and tested because poorly designed expressions may consume substantial resources. Full-Text SearchLIKE '%database%' performs substring matching but lacks linguistic tokenization, relevance ranking, and efficient general-purpose indexing. Full-text search converts documents and queries into searchable lexemes.
Creating and Maintaining PostgreSQL Search DataThe following generated column is defined on the existing products table. PostgreSQL automatically recalculates it whenever name or description changes: ALTER TABLE products
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
to_tsvector(
'english'::regconfig,
COALESCE(name, '') || ' ' || COALESCE(description, '')
)
) STORED;
CREATE INDEX idx_products_search
ON products USING GIN (search_vector);The relation and search column are now explicitly defined and maintained. They can be queried as follows: SELECT
product_id,
name,
ts_rank(
search_vector,
plainto_tsquery('english', 'relational database')
) AS relevance
FROM products
WHERE search_vector @@
plainto_tsquery('english', 'relational database')
ORDER BY relevance DESC, product_id;plainto_tsquery treats user input as ordinary text and is safer for simple search boxes than accepting raw to_tsquery syntax. More elaborate weighting—for example, weighting names above descriptions—can be maintained with a trigger when a generated expression is unsuitable.
MySQL Full-Text SearchALTER TABLE products
ADD FULLTEXT INDEX idx_products_search (name, description);
SELECT
product_id,
name,
MATCH(name, description)
AGAINST('relational database' IN NATURAL LANGUAGE MODE)
AS relevance
FROM products
WHERE MATCH(name, description)
AGAINST('relational database' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC, product_id;Tokenization, stop words, minimum token lengths, language support, and ranking differ between systems. Search behavior must therefore be tested with representative domain vocabulary. Integrated Illustrative Case: Regional Commerce DashboardBackgroundThis fictional case concerns Northstar Retail, which needs a dashboard showing regional revenue, quarterly trends, repeat-order behavior, category search, and management responsibility for sales staff. ProblemEarlier reporting code incorrectly assumed that orders contained region, counted orders after joining item rows, and used unrestricted hierarchy recursion. Product search relied on slow wildcard matching. Analysis and ApproachThe engineering team adopted five measures: - Join
orders to customers before regional aggregation. - Aggregate orders before joining to item-level data when both order counts and product measures are required.
- Use
LAG to compare each customer’s order with the preceding order. - traverse employee hierarchies with path-based cycle detection and a documented depth limit;
- create maintained full-text indexes for product search.
Suppose completed regional revenue is 40,000 in Q1 and 46,000 in Q2. The quarter-on-quarter growth rate is: g = (R2 - R1) ÷ R1 where R1 and R2 are revenues for consecutive quarters and R1 > 0. Therefore: g = (46,000 - 40,000) ÷ 40,000 = 0.15 = 15% If the earlier value is zero, percentage growth is undefined rather than infinite; the query should return NULL or a separately labelled status. A window-based growth query is: WITH quarterly AS (
SELECT
c.region,
EXTRACT(YEAR FROM o.ordered_at) AS yr,
EXTRACT(QUARTER FROM o.ordered_at) AS qtr,
SUM(o.total_amount) AS revenue
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.region,
EXTRACT(YEAR FROM o.ordered_at),
EXTRACT(QUARTER FROM o.ordered_at)
), compared AS (
SELECT q.*,
LAG(revenue) OVER (
PARTITION BY region
ORDER BY yr, qtr
) AS previous_revenue
FROM quarterly AS q
)
SELECT *,
(revenue - previous_revenue) * 1.0
/ NULLIF(previous_revenue, 0) AS growth_rate
FROM compared;OutcomeThe corrected design produces regionally accurate totals, prevents recursive runaway, supports indexed product search, and makes undefined growth explicit. The dashboard becomes more auditable because every metric has a documented grain and denominator. Learning PointsAdvanced SQL features are most valuable when combined with correct data modeling. A syntactically sophisticated query can still be wrong if it joins at the wrong grain, assumes a nonexistent attribute, or hides undefined calculations. Performance and ReliabilityAdvanced queries should be evaluated with EXPLAIN and, in controlled environments, EXPLAIN ANALYZE. Important practices include: - index join and hierarchy keys such as
orders.customer_id and employees.manager_id; - use GIN indexes for appropriate PostgreSQL JSONB, array, and full-text operations;
- filter early where doing so preserves semantics;
- pre-aggregate before joining relations at different grains;
- specify deterministic window ordering;
- bound recursion and detect cycles;
- avoid unbounded dynamic SQL;
- use half-open timestamp ranges; and
- test plans with realistic data volumes and distributions.
CTEs improve readability but optimization behavior depends on database version and whether materialization is requested. Window sorts can require significant memory or temporary disk space. Wide JSON documents and excessive indexes increase write costs. Governance, Security, and Ethical ConsiderationsAnalytical queries can reveal sensitive behavior even when individual fields appear harmless. Customer profiles, search terms, transaction times, and regional groupings may enable re-identification. Systems should apply least-privilege access, retention rules, encryption, audit logging, and output suppression for very small groups. Dynamic pivots, regular expressions, full-text queries, and JSON paths must not be assembled directly from untrusted input. Parameterization, identifier allow-lists, resource limits, and query timeouts reduce injection and denial-of-service risk. Ranking and segmentation may affect offers or service levels. Developers should test whether missing data, regional proxies, or historical bias produce unfair outcomes. SQL computes the requested metric; it does not establish that the metric is ethically appropriate. Contemporary DevelopmentsModern database systems increasingly combine relational, JSON, full-text, geospatial, and vector-search facilities. Full-text search remains preferable for exact lexical requirements, explainable token matching, and filters. Vector embeddings can support semantic similarity, but introduce model governance, computational cost, and reproducibility concerns. Hybrid search commonly combines lexical relevance, semantic similarity, and relational constraints rather than replacing SQL. Chapter SummaryAdvanced SQL extends relational querying from simple retrieval to comparative and analytical computation. Window functions retain row detail while calculating ranks, navigation values, cumulative measures, and distributions. Conditional aggregation provides portable pivot-like reporting, provided joins and data grain are correct. Recursive CTEs model hierarchical and graph traversal but require implemented cycle checks and depth controls. PostgreSQL and MySQL both support JSON, temporal analysis, regular expressions, and full-text search, although their syntax and indexing mechanisms differ. PostgreSQL additionally provides native arrays and extensive JSONB operators. Reliable implementations define and maintain search structures, preserve NULL where values are unknown or undefined, use deterministic ordering, and verify performance with execution plans. Advanced SQL is therefore both a language skill and an exercise in data modeling, governance, and analytical reasoning. Key TermsWindow function: A function that computes across related rows without collapsing them. Partition: A subset of rows independently processed by a window function. Window frame: The ordered range of rows contributing to a window calculation. ROW_NUMBER: A function assigning a unique sequential number within a partition. RANK: A ranking function that preserves ties and leaves gaps. LAG: A navigation function that accesses an earlier row. Conditional aggregation: Aggregation over values selected by conditional expressions. Static pivot: A query that converts known category values into predefined columns. Common table expression: A named intermediate query result scoped to one statement. Recursive CTE: A CTE that repeatedly references its own accumulated result. Cycle detection: Prevention or identification of repeated nodes in recursive traversal. JSONB: PostgreSQL’s decomposed binary JSON representation. GIN index: An inverted index suited to composite values such as JSONB, arrays, and text vectors. Array: An ordered collection of homogeneous values stored in one PostgreSQL column. Half-open interval: A range including its lower boundary but excluding its upper boundary. Regular expression: A pattern language for matching and transforming text. Lexeme: A normalized searchable token used by full-text search. Search vector: A PostgreSQL TSVECTOR representation of indexed document terms. Query grain: The entity level represented by each row at a stage of query processing. Review QuestionsShort Answer Questions- How does a window function differ from a grouped aggregate?
- Why should a running-total query usually specify its window frame explicitly?
- Why must regional order analysis join
orders to customers in the chapter schema? - What two safeguards are implemented in the recursive hierarchy examples?
- What limitation applies when
jsonb_set creates a missing nested key?
Long Answer Questions- Compare
ROW_NUMBER, RANK, and DENSE_RANK, including the treatment of ties. - Explain how conditional aggregation creates a static pivot and discuss its limitations.
- Describe the anchor, recursive member, termination, cycle check, and depth guard of a recursive CTE.
- Compare PostgreSQL and MySQL facilities for JSON, arrays, regular expressions, and full-text search.
- Discuss performance and governance risks associated with advanced analytical SQL.
Analytical / Application Questions- Design a query that returns each customer’s three largest completed orders with deterministic tie handling.
- Given an employee hierarchy that may contain corrupt circular references, design a safe traversal and justify its maximum depth.
- Design a regional monthly dashboard combining completed revenue, cancellation rate, prior-month revenue, and growth rate while preserving correct query grain.
Multiple Choice Questions with AnswersQuestion 1Which SQL feature calculates a running total while retaining each original order row? A. A windowed SUM B. A CHECK constraint C. A scalar subquery returning one constant D. A FULLTEXT index Correct Answer: A. A windowed SUM Question 2In the chapter schema, which join is required to group orders by region? A. orders to products on order_id B. orders to customers on customer_id C. customers to employees on manager_id D. products to employees on product_id Correct Answer: B. orders to customers on customer_id Question 3What does RANK do after two rows tie for first place? A. It assigns the next row rank 2 B. It assigns every later row rank 1 C. It assigns the next row rank 3 D. It rejects the tied rows Correct Answer: C. It assigns the next row rank 3 Question 4Why is an explicit ROWS frame useful in a running-total query? A. It converts text to numbers B. It prevents all joins C. It defines accumulation by ordered physical rows rather than peer groups D. It automatically creates an index Correct Answer: C. It defines accumulation by ordered physical rows rather than peer groups Question 5Which combination makes the recursive hierarchy query safer? A. A path-based cycle check and a depth guard B. DISTINCT and alphabetical ordering only C. A cross join and no termination condition D. A JSON index and a full-text index Correct Answer: A. A path-based cycle check and a depth guard Question 6When PostgreSQL jsonb_set uses create_if_missing as true, what must generally already exist for a nested update? A. Only the table name B. All intermediate path components C. A full-text dictionary D. An array index on every column Correct Answer: B. All intermediate path components Question 7A region has revenue of 40,000 in Q1 and 46,000 in Q2. What is the quarter-on-quarter growth rate when Q1 is the denominator? A. 6% B. 13% C. 15% D. 115% Correct Answer: C. 15% Question 8Which PostgreSQL index type is used in the chapter for a TSVECTOR search column? A. HASH B. GIN C. BRIN only D. No index can be used Correct Answer: B. GIN Question 9Why can COUNT(*) become inflated after joining orders to order_items? A. Each order may be repeated once per item B. COUNT(*) ignores every joined row C. Window functions delete duplicate orders D. Foreign keys convert counts to decimals Correct Answer: A. Each order may be repeated once per item Question 10Which timestamp predicate represents January 2025 as a half-open interval? A. ordered_at > '2025-01-01' AND ordered_at > '2025-02-01' B. ordered_at = '2025-01-01' C. ordered_at >= '2025-01-01' AND ordered_at < '2025-02-01' D. ordered_at <= '2025-01-01' AND ordered_at >= '2025-02-01' Correct Answer: C. ordered_at >= '2025-01-01' AND ordered_at < '2025-02-01'
|