Application Workflow Reports

Support Queue

Rank Tickets by Priority

A support queue report can filter by status, derive urgency scores, and rank the next tickets to review.

Program

Play the script to choose the ticket status and inspect the ranked support queue.

wanted_status
support_queue_report.sql
Replay: real traced execution (multi-file project)
CREATE TABLE tickets (id INTEGER, customer TEXT, status TEXT, severity TEXT, opened_day INTEGER);
INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);
WITH params(wanted_status) AS (VALUES ('open')), scored AS (SELECT id, customer, severity, 7 - opened_day + CASE WHEN severity = 'high' THEN 5 ELSE 0 END AS urgency FROM tickets WHERE status = (SELECT wanted_status FROM params)), ranked AS (SELECT id, customer, severity, urgency, ROW_NUMBER() OVER (ORDER BY urgency DESC, id) AS queue_rank FROM scored) SELECT id, customer, severity, urgency, queue_rank FROM ranked ORDER BY queue_rank;

CREATE TABLE tickets (id INTEGER, customer TEXT, status TEXT, severity TEXT, opened_day INTEGER);
INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);
WITH params(wanted_status) AS (VALUES ('waiting')), scored AS (SELECT id, customer, severity, 7 - opened_day + CASE WHEN severity = 'high' THEN 5 ELSE 0 END AS urgency FROM tickets WHERE status = (SELECT wanted_status FROM params)), ranked AS (SELECT id, customer, severity, urgency, ROW_NUMBER() OVER (ORDER BY urgency DESC, id) AS queue_rank FROM scored) SELECT id, customer, severity, urgency, queue_rank FROM ranked ORDER BY queue_rank;

  1. tables ← 1 row

    1CREATE TABLE tickets (id INTEGER, customer TEXT, status TEXT, severity TEXT, opened_day INTEGER);2INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);
    values this step1 rowtables

  2. tickets ← 4 rows

    1CREATE TABLE tickets (id INTEGER, customer TEXT, status TEXT, severity TEXT, opened_day INTEGER);2INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);3WITH params(wanted_status) AS (VALUES ('open')), scored AS (SELECT id, customer, severity, 7 - opened_day + CASE WHEN severity = 'high' THEN 5 ELSE 0 END AS urgency FROM tickets WHERE status = (SELECT wanted_status FROM params)), ranked AS (SELECT id, customer, severity, urgency, ROW_NUMBER() OVER (ORDER BY urgency DESC, id) AS queue_rank FROM scored) SELECT id, customer, severity, urgency, queue_rank FROM ranked ORDER BY queue_rank;
    values this step4 rowstickets

  3. result ← 3 rows

    2INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);3WITH params(wanted_status) AS (VALUES ('open')), scored AS (SELECT id, customer, severity, 7 - opened_day + CASE WHEN severity = 'high' THEN 5 ELSE 0 END AS urgency FROM tickets WHERE status = (SELECT wanted_status FROM params)), ranked AS (SELECT id, customer, severity, urgency, ROW_NUMBER() OVER (ORDER BY urgency DESC, id) AS queue_rank FROM scored) SELECT id, customer, severity, urgency, queue_rank FROM ranked ORDER BY queue_rank;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE tickets (id INTEGER, customer TEXT, status TEXT, severity TEXT, opened_day INTEGER);2INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);
    values this step1 rowtables

  2. tickets ← 4 rows

    1CREATE TABLE tickets (id INTEGER, customer TEXT, status TEXT, severity TEXT, opened_day INTEGER);2INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);3WITH params(wanted_status) AS (VALUES ('waiting')), scored AS (SELECT id, customer, severity, 7 - opened_day + CASE WHEN severity = 'high' THEN 5 ELSE 0 END AS urgency FROM tickets WHERE status = (SELECT wanted_status FROM params)), ranked AS (SELECT id, customer, severity, urgency, ROW_NUMBER() OVER (ORDER BY urgency DESC, id) AS queue_rank FROM scored) SELECT id, customer, severity, urgency, queue_rank FROM ranked ORDER BY queue_rank;
    values this step4 rowstickets

  3. result ← 1 row

    2INSERT INTO tickets VALUES (1, 'Ada', 'open', 'high', 1), (2, 'Lin', 'waiting', 'normal', 2), (3, 'Mira', 'open', 'normal', 3), (4, 'Noor', 'open', 'high', 4);3WITH params(wanted_status) AS (VALUES ('waiting')), scored AS (SELECT id, customer, severity, 7 - opened_day + CASE WHEN severity = 'high' THEN 5 ELSE 0 END AS urgency FROM tickets WHERE status = (SELECT wanted_status FROM params)), ranked AS (SELECT id, customer, severity, urgency, ROW_NUMBER() OVER (ORDER BY urgency DESC, id) AS queue_rank FROM scored) SELECT id, customer, severity, urgency, queue_rank FROM ranked ORDER BY queue_rank;
    values this step1 rowresult
derived urgency The query combines age and severity into one sortable score.
status filter The selector focuses the queue on one ticket status.
queue rank `ROW_NUMBER` gives each visible ticket a stable review order.