Reporting Queries, Cohort-Style Analysis, Pivots by Conditional Aggregation, and Reusable Views

Learning outcomes

The final step is not “learn another SQL feature.” It is to compose the features from Chapters 04 and 05 into a report that has a stable grain, a testable definition, and a clear operational boundary. We will build monthly metrics, a cohort-style retention/activity table, a pivot-like regional summary, and a view that exposes a reusable read contract.

These are operational analytics patterns. They are valuable inside MySQL when they remain bounded and close to transactional data. They are not an argument to run every historical scan and BI workload on the production primary.

Pipeline 1: monthly technician performance without double counting

Suppose operations needs one row per technician per month: work-order count, total labor, total parts cost, and each technician-month’s rank by labor inside that calendar month. Build the grouped relation first, then apply a window to the grouped rows.

USE servicehub_analytics_lab;WITH technician_month AS (  SELECT DATE_FORMAT(w.opened_at,'%Y-%m-01') AS month_start,         w.technician_id,         t.technician_name,         COUNT(*) AS order_count,         SUM(w.labor_minutes) AS labor_minutes,         SUM(w.parts_cost) AS parts_cost  FROM work_orders AS w  JOIN technicians AS t    ON t.technician_id = w.technician_id  WHERE w.technician_id IS NOT NULL  GROUP BY month_start, w.technician_id, t.technician_name)SELECT month_start,       technician_id,       technician_name,       order_count,       labor_minutes,       parts_cost,       DENSE_RANK() OVER (         PARTITION BY month_start         ORDER BY labor_minutes DESC       ) AS labor_rank_in_monthFROM technician_monthORDER BY month_start, labor_rank_in_month, technician_id;

The CTE’s grain is one row per month and technician. The outer query preserves that grain and adds a rank. If you joined a one-to-many detail table such as tags before grouping without compensating for multiplicity, sums could be inflated. Always inspect join cardinality before aggregation.

Pipeline 2: conditional aggregation as a practical pivot

MySQL does not require a special PIVOT operator for a fixed small category set. Conditional aggregation turns known categories into columns. The categories are encoded in SQL, so this is appropriate when the reporting contract is stable; it is awkward for unbounded dynamic dimensions.

SELECT c.region,       COUNT(*) AS total_orders,       SUM(CASE WHEN w.priority=1 THEN 1 ELSE 0 END) AS priority_1,       SUM(CASE WHEN w.priority=2 THEN 1 ELSE 0 END) AS priority_2,       SUM(CASE WHEN w.priority=3 THEN 1 ELSE 0 END) AS priority_3,       SUM(CASE WHEN w.status='open' THEN 1 ELSE 0 END) AS open_ordersFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idGROUP BY c.regionORDER BY c.region;

For every region, priority_1 + priority_2 + priority_3 should equal total_orders because the schema check restricts priority to 1–3. That identity is a useful verification assertion.

Cohort-style analysis: define cohort and period before SQL

A cohort groups entities by a shared starting period. Here, the cohort is the month of customers.signup_date. Activity is a month in which the customer opened at least one work order. period_no=0 means activity in the signup month, 1 means the next calendar month, and so on. This is an example of a cohort-style operational analysis, not a universal retention definition.

WITH activity AS (  SELECT DISTINCT         c.customer_id,         DATE_FORMAT(c.signup_date,'%Y-%m-01') AS cohort_month,         DATE_FORMAT(w.opened_at,'%Y-%m-01') AS activity_month  FROM customers AS c  JOIN work_orders AS w ON w.customer_id=c.customer_id), periods AS (  SELECT customer_id,         cohort_month,         activity_month,         TIMESTAMPDIFF(           MONTH,           CAST(cohort_month AS DATE),           CAST(activity_month AS DATE)         ) AS period_no  FROM activity)SELECT cohort_month,       COUNT(DISTINCT CASE WHEN period_no=0 THEN customer_id END) AS m0_customers,       COUNT(DISTINCT CASE WHEN period_no=1 THEN customer_id END) AS m1_customers,       COUNT(DISTINCT CASE WHEN period_no=2 THEN customer_id END) AS m2_customers,       COUNT(DISTINCT customer_id) AS customers_seenFROM periodsGROUP BY cohort_monthORDER BY cohort_month;

DISTINCT in the activity stage prevents multiple work orders in the same customer/month from being counted as multiple active customers. If your business definition is “number of orders” rather than “active customers,” remove that deduplication deliberately—not accidentally.

Create a stable read contract with a view

A view is a stored query that behaves like a virtual table. It can hide repetitive joins and expose stable column names to applications. It is not a materialized cache: selecting from the view executes according to the view definition and optimizer processing.

Security context matters. SQL SECURITY INVOKER means underlying-object privileges are checked in the invoker context; DEFINER uses the view definer’s context. For this local learning view, we choose INVOKER explicitly so the choice is visible rather than inherited silently.

CREATE OR REPLACESQL SECURITY INVOKERVIEW v_customer_service_summary ASSELECT c.customer_id,       c.customer_name,       c.region,       COUNT(w.work_order_id) AS order_count,       SUM(CASE WHEN w.status='open' THEN 1 ELSE 0 END) AS open_orders,       SUM(COALESCE(w.labor_minutes,0)) AS labor_minutes,       SUM(COALESCE(w.parts_cost,0.00)) AS parts_cost,       MAX(w.opened_at) AS latest_opened_atFROM customers AS cLEFT JOIN work_orders AS w  ON w.customer_id=c.customer_idGROUP BY c.customer_id, c.customer_name, c.region;SELECT *FROM v_customer_service_summaryORDER BY customer_id;

The explicit column expressions form a read API. Avoid SELECT * inside durable views simply to save typing: MySQL stores the view definition at creation time, and a stable contract should change intentionally through migration/review.

Inspect the stored definition and metadata

Do not assume the server stored exactly the text you typed. Use metadata. SHOW CREATE VIEW exposes the canonical definition; INFORMATION_SCHEMA.VIEWS exposes security type, definer, check option, and updatability metadata.

SHOW CREATE VIEW v_customer_service_summary;SELECT TABLE_SCHEMA,       TABLE_NAME,       CHECK_OPTION,       IS_UPDATABLE,       DEFINER,       SECURITY_TYPEFROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_SCHEMA='servicehub_analytics_lab'  AND TABLE_NAME='v_customer_service_summary';

This aggregate view is expected to be non-updatable because grouping/aggregation prevents simple one-row mapping back to a base table. Treat it as a read contract.

Side-effect verification: a report should not mutate operational data

A plain SELECT from this view has no intended data-changing side effect. Verify that assumption in a lab by taking base-table counts before and after a controlled invocation. This is simple, but it establishes a habit that becomes essential when later chapters introduce stored programs, triggers, events, and administrative actions.

SELECT COUNT(*) AS before_orders FROM work_orders;SELECT COUNT(*) AS before_customers FROM customers;SELECT *FROM v_customer_service_summaryWHERE region='north'ORDER BY customer_id;SELECT COUNT(*) AS after_orders FROM work_orders;SELECT COUNT(*) AS after_customers FROM customers;-- Before and after counts must match.

Intentional reporting failure: mix grains in one SELECT

A common report bug joins customers to work orders and then to a one-to-many detail relation, sums work-order measures, and assumes the totals are still one row per customer. The SQL may be valid while the result is wrong. The fix is not DISTINCT sprinkled across aggregates; it is to aggregate each fact at the correct grain before joining, or use a semijoin/existence test when only presence matters.

SELECT region,       total_orders,       priority_1 + priority_2 + priority_3 AS priority_sum,       total_orders = (priority_1 + priority_2 + priority_3) AS identity_holdsFROM (  SELECT c.region,         COUNT(*) AS total_orders,         SUM(CASE WHEN w.priority=1 THEN 1 ELSE 0 END) AS priority_1,         SUM(CASE WHEN w.priority=2 THEN 1 ELSE 0 END) AS priority_2,         SUM(CASE WHEN w.priority=3 THEN 1 ELSE 0 END) AS priority_3  FROM work_orders AS w  JOIN customers AS c ON c.customer_id=w.customer_id  GROUP BY c.region) AS rORDER BY region;

Every identity_holds value should be 1. Build assertions like this into report tests when domain rules provide useful invariants.

When MySQL is the right reporting boundary—and when it is not

The exact boundary depends on workload, freshness, isolation, concurrency, recovery goals, and available architecture. “MySQL can execute the SQL” is not enough justification to put the workload on the production primary.

Hands-on capstone for Chapter 05

1. Build the technician-month aggregate and add a monthly labor rank.
2. Build the regional priority pivot and assert that the three priority columns sum to the total.
3. Run the cohort-style query and explain what one row, one cohort, and one period mean.
4. Create v_customer_service_summary with SQL SECURITY INVOKER.
5. Inspect SHOW CREATE VIEW and INFORMATION_SCHEMA.VIEWS; record IS_UPDATABLE and SECURITY_TYPE.
6. Run the view and prove base row counts did not change.
7. Drop only the view if you want to reset the reporting layer; keep the chapter data for your own experiments.

DROP VIEW IF EXISTS v_customer_service_summary;SELECT COUNT(*) AS work_orders_still_present FROM work_orders;SELECT COUNT(*) AS customers_still_present FROM customers;

Chapter summary and bridge to Chapter 06

Chapter 05 moved from row-reducing aggregates to row-preserving windows, semi-structured JSON, scalar transformation semantics, and composed reporting contracts. Across all five lessons the same rule held: define meaning first, make MySQL behavior observable, then optimize using evidence.

The next chapter changes the problem from reading data to changing it safely. Chapter 06 — Data Modification, Transactions, Locking, and Concurrency Semantics begins with INSERT, generated values, and bulk writes, then develops transaction boundaries, isolation, locks, deadlocks, retries, and concurrency testing.

Authoritative references

• MySQL 8.4 Reference Manual — CREATE VIEW Statement
• MySQL 8.4 Reference Manual — Using Views
• MySQL 8.4 Reference Manual — INFORMATION_SCHEMA VIEWS Table
• MySQL 8.4 Reference Manual — Aggregate Functions
• MySQL 8.4 Reference Manual — Window Functions
• MySQL 8.4 Reference Manual — Common Table Expressions