Operational Remediation Reports

Incident Owner Report

Assign Follow-Up

A SQL incident remediation report can count visible incidents and assign the owner for follow-up.

Program

Play the query to choose minimum severity and inspect which owner receives the incident follow-up.

min_severity
incident_owner_report.sql
Replay: real traced execution (multi-file project)
CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);
INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');
WITH params(min_severity) AS (VALUES (2)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;

CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);
INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');
WITH params(min_severity) AS (VALUES (1)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;

CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);
INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');
WITH params(min_severity) AS (VALUES (3)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;

  1. tables ← 1 row

    1CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');
    values this step1 rowtables

  2. incidents ← 4 rows

    1CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');3WITH params(min_severity) AS (VALUES (2)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;
    values this step4 rowsincidents

  3. result ← 1 row

    2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');3WITH params(min_severity) AS (VALUES (2)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');
    values this step1 rowtables

  2. incidents ← 4 rows

    1CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');3WITH params(min_severity) AS (VALUES (1)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;
    values this step4 rowsincidents

  3. result ← 1 row

    2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');3WITH params(min_severity) AS (VALUES (1)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');
    values this step1 rowtables

  2. incidents ← 4 rows

    1CREATE TABLE incidents (id INTEGER, severity INTEGER, status TEXT);2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');3WITH params(min_severity) AS (VALUES (3)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;
    values this step4 rowsincidents

  3. result ← 1 row

    2INSERT INTO incidents VALUES (1, 1, 'open'), (2, 3, 'open'), (3, 2, 'open'), (4, 3, 'open');3WITH params(min_severity) AS (VALUES (3)), visible AS (SELECT * FROM incidents WHERE status = 'open' AND severity >= (SELECT min_severity FROM params)) SELECT (SELECT min_severity FROM params) AS min_severity, COUNT(*) AS visible, CASE WHEN COUNT(*) >= 3 THEN 'ops' ELSE 'lead' END AS owner FROM visible;
    values this step1 rowresult
severity parameter `min_severity` controls which fixed incident rows are visible.
visible count The visible CTE keeps the filtered incident set small and inspectable.
owner assignment The aggregate CASE expression maps the count to operations or lead ownership.