SQL Batch Processing — Applied Soft Deletes and Pagination

QUESTION 6

Logical deletion — track deleted rows with deleted_at and retrieve only active data

UPDATEIS NULLLogical deletionsoft delete

Background ▼

Logical deletion (soft delete) is a pattern that treats a row as deleted by setting a value in the deleted_at timestamp column instead of physically DELETEing it. Use it when an API or batch job needs to restore the pre-deletion state or retain deletion history.

-- Logical deletion: set the current time in deleted_at
UPDATE users
SET    deleted_at = TIMESTAMP '2024-06-01 06:30'
WHERE  user_id = 42;

-- Retrieve only active records: rows whose deleted_at is NULL
SELECT * FROM users
WHERE  deleted_at IS NULL;

COALESCE(value, default): returns the first non-NULL value. COALESCE(deleted_at, '9999-12-31') treats a NULL deleted_at as a date far in the future. Use it for date sorting instead of a logical-deletion flag.

Problem ▼

Implement each of the following requirements as a separate query.

① In the users table, logically delete users whose last_login_at is at least 90 days old (deleted_at = TIMESTAMP '2024-06-01 06:30').

② Retrieve only active users that have not been logically deleted, sort by newest last_login_at first, and use COALESCE to put NULLs last.

Tables used

▸ users (reference date: 2024-06-01)

user_id	name	last_login_at	deleted_at (timestamp)
U01	Alice	2024-05-20	NULL
U02	Bob	2024-01-10	NULL
U03	Carol	NULL	NULL
U04	Dave	2024-02-28	NULL
U05	Eve	2024-05-01	2024-04-01

Expected Output ▼

After UPDATE ① (U02 and U04 are logical-deletion targets):

user_id	name	last_login_at	deleted_at
U01	Alice	2024-05-20	NULL
U02	Bob	2024-01-10	2024-06-01 06:30:00
U03	Carol	NULL	NULL
U04	Dave	2024-02-28	2024-06-01 06:30:00
U05	Eve	2024-05-01	2024-04-01 00:00:00

SELECT result ② (U01 and U03 only; last_login descending, with NULL last):

user_id	name	last_login_at
U01	Alice	2024-05-20
U03	Carol	NULL

Model Answer ▼

-- ① Logical deletion (setting a deletion timestamp treats the row as deleted)
UPDATE users
SET    deleted_at = TIMESTAMP '2024-06-01 06:30'
WHERE  deleted_at IS NULL                          -- target rows that are not deleted
  AND  last_login_at < TIMESTAMP '2024-06-01 06:30' - INTERVAL '90 days'; -- last login was at least 90 days ago

-- Verify every row after UPDATE ①
SELECT user_id, name, last_login_at, deleted_at
FROM users
ORDER BY user_id;

-- ② Retrieve and sort active data
SELECT
  user_id, name, last_login_at
FROM  users
WHERE deleted_at IS NULL                       -- retrieve only rows that are not deleted
ORDER BY
  COALESCE(last_login_at, '1900-01-01') DESC;  -- replace NULL with an old date and put it last in DESC (newest-first) order

/*
  Logical execution order for ①:
  1. FROM      → Read rows
  2. WHERE     → Filter rows
  3. SET       → Update target rows

  Logical execution order for ②:
  1. FROM      → Read rows
  2. WHERE     → Filter rows
  3. SELECT    → Evaluate columns
  4. ORDER BY  → Sort and output
*/

Explanation (table transitions & key points) ▼

UPDATE users
SET    deleted_at = TIMESTAMP '2024-06-01 06:30'
WHERE  deleted_at IS NULL
  AND  last_login_at < TIMESTAMP '2024-06-01 06:30' - INTERVAL '90 days';

SELECT user_id, name, last_login_at, deleted_at FROM users ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM ▼

UPDATE usersRead all 5 rows in the users table as candidates. Check deleted_at and last_login_at to narrow down the rows to update.

1 / 3

user_id	name	last_login_at	deleted_at
U01	Alice	2024-05-20	NULL
U02	Bob	2024-01-10	NULL
U03	Carol	NULL	NULL
U04	Dave	2024-02-28	NULL
U05	Eve	2024-05-01	2024-04-01

5 rows read

SELECT
  user_id, name, last_login_at
FROM   users
WHERE  deleted_at IS NULL
ORDER BY
  COALESCE(last_login_at, '1900-01-01') DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM users (after UPDATE)Read the entire users table after the logical-deletion UPDATE. U02 and U04 now have values in deleted_at.

1 / 4

user_id	name	last_login_at	deleted_at
U01	Alice	2024-05-20	NULL
U02	Bob	2024-01-10	2024-06-01 06:30:00
U03	Carol	NULL	NULL
U04	Dave	2024-02-28	2024-06-01 06:30:00
U05	Eve	2024-05-01	2024-04-01

5 rows read

LEARNING POINTS

Basic refresher (physical vs. logical deletion): Physically removing data, as with DELETE FROM users, is called physical deletion. By contrast, creating a dedicated column (deleted_at or is_deleted) and marking the row as deleted is called logical deletion. It is common in production because accidental operations are easier to recover from.

Production design for logical deletion: Instead of writing deleted_at IS NULL in every WHERE clause, hide the filter behind a view (VIEW) or RLS (Row Level Security) to prevent bugs caused by forgetting it. In an ORM, a default_scope-style implementation serves the same purpose.

INTERVAL syntax:NOW() - INTERVAL '90 days' generates a timestamp 90 days in the past.'1 month' · '1 year' · '2 hours' work similarly. In MySQL, use DATE_SUB(NOW(), INTERVAL 90 DAY).

ANTI-PATTERNS

No index on the logical-deletion table:WHERE deleted_at IS NULL can be dramatically accelerated with a partial index CREATE INDEX ON users (user_id) WHERE deleted_at IS NULL. This is especially effective when only a small fraction of rows are active.

Filtering NULL last_login_at in WHERE: If users who have never logged in should be excluded, explicitly add AND last_login_at IS NOT NULL. In PostgreSQL, NULL < date evaluates to FALSE, which can make this bug hard to notice.

PRACTICAL COLUMN

Logical-deletion table bloat and regular cleanup
Logical deletion is convenient, but deleted data remains. Over years, it consumes storage and index space and degrades query performance. For long-lived systems, design lifecycle management alongside it: for example, archive data one year after logical deletion to S3 or cold storage in a nightly batch, then physically delete it.

QUESTION 7

LAG-based change detection — Compare values with the previous batch run and extract only changed rows

LAGChange detectionDelta batchWindow function

Background ▼

LAG(column, N) returns the value N rows earlier within the same partition. It is ideal for a delta batch that compares the previous batch value with the current value and extracts only changed rows.

LAG(column, 1) OVER (
  PARTITION BY group_key        -- Compare preceding and following values within each group
  ORDER BY     time_col         -- Return the value from the previous row after sorting chronologically
)                                -- The first row is NULL because there is no previous row

Delta-batch pattern: process only changed rows instead of retrieving everything to reduce external API calls and avoid cost and rate-limit problems.

Difference from LEAD:LAG returns the previous row's value, while LEAD returns the next row's value. Use LAG for change detection because it compares the previous and current values.

Problem ▼

The stock_snapshots table records daily inventory snapshots. Extract only products whose quantity changed from the previous day and output the change amount (diff) as well.

Tables used

▸ stock_snapshots

snapshot_id	product_id	snapshot_date	quantity
1	P01	2024-06-01	100
2	P01	2024-06-02	85
3	P01	2024-06-03	85
4	P02	2024-06-01	200
5	P02	2024-06-02	200
6	P02	2024-06-03	215

Expected Output ▼

product_id	snapshot_date	quantity	prev_quantity	diff
P01	2024-06-02	85	100	-15
P02	2024-06-03	215	200	+15

Model Answer ▼

WITH daily_diff AS (
  SELECT
    product_id, snapshot_date, quantity,
    LAG(quantity, 1) OVER (               -- Retrieve the previous inventory quantity after sorting by date within each product
      PARTITION BY product_id
      ORDER BY     snapshot_date
    ) AS prev_quantity
  FROM stock_snapshots
)

SELECT
  product_id, snapshot_date, quantity, prev_quantity,
  quantity - prev_quantity AS diff           -- Calculate the difference between today and the previous day
FROM  daily_diff
WHERE prev_quantity IS NOT NULL              -- Exclude the first day (no previous-day data)
  AND quantity <> prev_quantity              -- Extract only rows where the inventory quantity changed
ORDER BY product_id, snapshot_date;

/*
  Logical execution order:
  [CTE: daily_diff]
  1. FROM stock_snapshots  → Read rows
  2. LAG()                 → Evaluate the window function (row count is preserved)

  [Main query]
  3. FROM daily_diff       → read the CTE
  4. WHERE                 → Filter rows
  5. SELECT                → Evaluate columns
  6. ORDER BY              → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH daily_diff AS (
  SELECT
    product_id, snapshot_date, quantity,
    LAG(quantity, 1) OVER (
      PARTITION BY product_id
      ORDER BY snapshot_date
    ) AS prev_quantity
  FROM stock_snapshots
)
SELECT
  product_id, snapshot_date, quantity, prev_quantity,
  quantity - prev_quantity AS diff
FROM  daily_diff
WHERE prev_quantity IS NOT NULL
  AND quantity <> prev_quantity
ORDER BY product_id, snapshot_date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM stock_snapshotsRead all 6 rows from stock_snapshots. Daily snapshots are recorded for each product_id.

1 / 5

snapshot_id	product_id	snapshot_date	quantity
1	P01	2024-06-01	100
2	P01	2024-06-02	85
3	P01	2024-06-03	85
4	P02	2024-06-01	200
5	P02	2024-06-02	200
6	P02	2024-06-03	215

6 rows read

LEARNING POINTS

Basic refresher (comparison operators): In SQL, use = for “equal” and <> or != for “not equal.” Here, quantity <> prev_quantity means that today's and yesterday's inventory quantities differ.

Delta-batch design principle: External APIs (Slack notifications, inventory integrations, payment systems, and so on) have rate limits. Sending “only changed rows” minimizes API calls. LAG-based diffs are an ideal SQL pattern for detecting them.

LAG's second argument:LAG(col, 2) returns the value from two rows earlier. The third argument can provide a default for NULL, as in LAG(col, 1, 0) (for example, when calculating the first day as a difference from 0).

ANTI-PATTERNS

Omitting PARTITION BY: Without PARTITION BY, all products become one partition, so the last row of P01 is compared with the first row of P02. Always partition by product_id.

Comparing floating-point values with <>: If quantity is FLOAT, rounding errors can make <> TRUE even when the values are actually equal. Use INTEGER or NUMERIC for values such as amounts and inventory quantities that can be represented as integers.

PRACTICAL COLUMN

SQL delta batches as an alternative to CDC tools
CDC (Change Data Capture) tools such as Debezium are ideal for real-time synchronization between systems, but infrastructure and operations can be costly. If you only need to send the changes since yesterday to an external API once a day, this SQL change-detection pattern using LAG is a highly cost-effective option that requires no additional infrastructure.

QUESTION 8

Cursor pagination — Fetch the next page while avoiding LIMIT/OFFSET problems

WHERELIMITPaginationAPI response

Background ▼

When an API returns a large dataset, LIMIT/OFFSET pagination becomes slower as OFFSET grows because more rows must be scanned. Cursor-based pagination passes the last retrieved record's ID (the cursor) in the next request and can consistently use an index.

-- OFFSET method (problematic): the page around row 1,000,000 scans all skipped rows
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 990000; -- Slow

-- Cursor method (recommended): use LIMIT to take rows greater than the previous last id
SELECT * FROM orders
WHERE  id > :last_seen_id           -- Cursor: the last ID retrieved previously
ORDER BY id
LIMIT  10;                          -- Fast because it always uses the index

When cursor pagination is a good fit: Infinite scrolling that fetches the next page in sequence and chunking batch processing. OFFSET is useful when users must jump to an arbitrary page number, such as in an admin console.

Problem ▼

Write a query that retrieves the next page from orders, five rows per page using a cursor (the last order_id).

Conditions: status = 'pending', order_id ascending, and the previous last order_id is 1003 (parameter :last_id). Also output the cursor for the next page (the last order_id).

Tables used

▸ orders

order_id	customer_id	amount	status
1001	C01	5000	pending
1002	C02	3000	completed
1003	C03	7000	pending
1004	C04	2500	pending
1005	C05	8000	pending
1006	C06	4500	pending
1007	C01	6000	cancelled
1008	C02	9000	pending

Expected Output ▼

order_id	customer_id	amount	status	next_cursor
1004	C04	2500	pending	1008
1005	C05	8000	pending	1008
1006	C06	4500	pending	1008
1008	C02	9000	pending	1008

Model Answer ▼

WITH paged AS (
  SELECT
    order_id, customer_id, amount, status
  FROM  orders
  WHERE status   = 'pending'
    AND order_id > 1003       -- Cursor condition: use rows greater than the last ID retrieved previously
  ORDER BY order_id           -- Sort by the same column as the cursor
  LIMIT    5                  -- Maximum rows for the next page
)

SELECT
  *,
  MAX(order_id) OVER () AS next_cursor  -- Attach the last retrieved ID as the next-page cursor on every row
FROM paged
ORDER BY order_id;

/*
  Logical execution order:
  [CTE: paged]
  1. FROM orders   → Read rows
  2. WHERE         → Filter rows
  3. ORDER BY      → Sort and output
  4. LIMIT         → Limit the row count

  [Main query]
  5. FROM paged    → read the CTE
  6. SELECT        → Evaluate the window function (row count is preserved)
*/

Explanation (table transitions & key points) ▼

WITH paged AS (
  SELECT
    order_id, customer_id, amount, status
  FROM  orders
  WHERE status   = 'pending'
    AND order_id > 1003
  ORDER BY order_id
  LIMIT    5
)
SELECT
  *,
  MAX(order_id) OVER () AS next_cursor
FROM paged;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM ordersRead all 8 rows from orders. The next WHERE step excludes cancelled and completed rows, as well as rows at or before the cursor.

1 / 4

order_id	customer_id	amount	status
1001	C01	5000	pending
1002	C02	3000	completed
1003	C03	7000	pending
1004	C04	2500	pending
1005	C05	8000	pending
1006	C06	4500	pending
1007	C01	6000	cancelled
1008	C02	9000	pending

8 rows read

LEARNING POINTS

Basic refresher (LIMIT and pagination): Use LIMIT 10 to retrieve the first 10 rows from 1,000. Writing LIMIT 10 OFFSET 10 for the next 10 is simple, but OFFSET skips all preceding data, so later pages become increasingly expensive. Using an ID or similar marker (a cursor) in a WHERE condition is the key to speeding it up.

Applying this to batch-processing chunks: Instead of processing millions of rows at once, process chunks with WHERE id > :last_processed_id LIMIT 1000 to reduce memory usage and prevent timeouts. Save the highest processed ID in a batch-state table so the job can resume.

Composite cursors: When ORDER BY uses multiple columns, use a row-value comparison such as WHERE (created_at, id) > (:cursor_time, :cursor_id) (PostgreSQL).

ANTI-PATTERNS

LIMIT without ORDER BY: LIMIT without ORDER BY may return different rows each time, depending on the database execution plan. Always use ORDER BY to guarantee stable pagination order.

No index on the cursor column: Cursor pagination is fast only when WHERE order_id > :cursor can use an index. The primary key order_id or whichever cursor column you use must be indexed; without an index, it can be slower than OFFSET.

Combining LIMIT with a window function: Because LIMIT is evaluated after window functions, using a window function without first isolating the limited rows in a CTE can calculate over the entire extraction range and cause bugs. Use a CTE to calculate at the intended scope.

PRACTICAL COLUMN

Hiding cursor values (opaque tokens)
When implementing cursor pagination in a public API, exposing a raw sequential database ID such as next_cursor: 1008 lets outsiders infer the record count and growth rate. In production, convert it to an opaque token such as Base64(id), send it to the client, then decode it on the server and use it in SQL.

QUESTION 9

Incremental update batches — Efficiently process only rows changed since the previous run using updated_at

WHEREIncremental updatesIncremental batchupdated_at

Background ▼

Instead of retrieving and processing every row, filter with updated_at >= the previous batch run time and process only changed records. This incremental (delta) batch pattern is common in production for API data synchronization, aggregate-table updates, and notifications.

-- Pass the previous batch run time as :last_run_at
SELECT *
FROM   orders
WHERE  updated_at >= :last_run_at   -- Only rows changed since the previous run
  AND  updated_at <  TIMESTAMP '2024-06-01 06:30';        -- Before the current time (exclude changes during this run)

indexing updated_at makes WHERE filtering fast. In production, it is important to include updated_at from the table-design stage.

Managing batch run times: Store the previous run time (last_run_at) in a control table such as batch_jobs. After the batch completes, overwrite it with “this run's start time” as the reference for the next run.

Problem ▼

Using the orders and batch_checkpoints tables, implement the following.

① Retrieve the previous batch run time from batch_checkpoints.

② Extract orders updated since the previous run and aggregate order count, total amount, and latest update time by customer_id.

③ Update batch_checkpoints with this batch’s start time (NOW()).

Tables used

▸ orders

order_id	customer_id	amount	status	updated_at
1	C01	5000	completed	2024-06-01 01:00
2	C02	3000	pending	2024-06-01 02:00
3	C01	7000	completed	2024-06-01 03:00
4	C03	2500	cancelled	2024-05-31 12:00
5	C02	8000	completed	2024-06-01 04:00

▸ batch_checkpoints

job_name	last_run_at
order_sync	2024-06-01 00:00:00

Expected Output ▼

customer_id	order_count	total_amount	last_updated_at
C01	2	12000	2024-06-01 03:00
C02	2	11000	2024-06-01 04:00

Model Answer ▼

-- ① CTE to retrieve the previous batch run time
WITH last_checkpoint AS (
  SELECT last_run_at
  FROM   batch_checkpoints
  WHERE  job_name = 'order_sync'
),
diff_summary AS (                         -- ② CTE to extract and aggregate changed records
  SELECT
    o.customer_id,
    COUNT(*)          AS order_count,     -- Order count
    SUM(o.amount)     AS total_amount,    -- Total amount
    MAX(o.updated_at) AS last_updated_at  -- Latest update time in the group
  FROM        orders o
  CROSS JOIN  last_checkpoint lc          -- Attach the previous run time (one row) to every row
  WHERE o.updated_at >= lc.last_run_at    -- Data updated since the previous batch run
    AND o.updated_at <  TIMESTAMP '2024-06-01 06:30'             -- Exclude new updates during this run and process them next time
  GROUP BY o.customer_id
)

-- Output the aggregate result
SELECT * FROM diff_summary ORDER BY customer_id;

-- ③ Update the checkpoint after the batch completes (run as a separate query)
UPDATE batch_checkpoints
SET    last_run_at = TIMESTAMP '2024-06-01 06:30'
WHERE  job_name    = 'order_sync';

/*
  Logical execution order for ②:
  1. FROM / CROSS JOIN  → Join (Cartesian product)
  2. WHERE              → Filter rows
  3. GROUP BY           → Group rows
  4. SELECT             → Evaluate aggregate functions
*/

Explanation (table transitions & key points) ▼

WITH last_checkpoint AS (
  SELECT last_run_at FROM batch_checkpoints WHERE job_name = 'order_sync'
),
diff_summary AS (
  SELECT
    o.customer_id,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_amount,
    MAX(o.updated_at) AS last_updated_at
  FROM orders o
  CROSS JOIN last_checkpoint lc
  WHERE o.updated_at >= lc.last_run_at
    AND o.updated_at < NOW()
  GROUP BY o.customer_id
)
SELECT * FROM diff_summary ORDER BY customer_id;

LEGEND ▼

Rows read / loaded

① FROM (CTE) ▼

last_checkpoint: FROM batch_checkpointsThe last_checkpoint CTE retrieves the previous run time (2024-06-01 00:00) for the order_sync job from batch_checkpoints.

1 / 6

job_name	last_run_at
order_sync	2024-06-01 00:00:00

last_run_at = 2024-06-01 00:00:00

UPDATE batch_checkpoints
SET    last_run_at = NOW()
WHERE  job_name = 'order_sync';

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM batch_checkpoints (before update)This is the state of batch_checkpoints before the update. The order_sync job’s last_run_at is the previous batch start time (2024-06-01 00:00).

1 / 3

job_name	last_run_at (current)
order_sync	2024-06-01 00:00:00

State before update

LEARNING POINTS

Basic refresher (JOIN types): JOINs connect tables in several ways: INNER JOIN keeps only matching data, while LEFT JOIN keeps every row from the left table. CROSS JOIN creates every combination; when the other side has one row, as here, it attaches the same value to every row.

Making updated_at mandatory: Always include updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL when designing a table. An UPDATE trigger or an ORM before_update hook that updates it automatically makes delta batches easier to write.

Why the NOW() upper bound matters: The reason for adding updated_at < NOW() is to ensure that rows written during the batch are picked up next time. Without an upper bound, some rows may fall through between runs and be lost.

ANTI-PATTERNS

Not indexing updated_at: The delta-batch condition WHERE updated_at >= :last_run performs a full scan without an index. On a large table, CREATE INDEX ON orders (updated_at) is essential.

Updating the checkpoint before processing: If last_run_at is updated even when batch processing fails, the next run skips the failed rows. Update the checkpoint only after processing completes and protect it with a transaction.

PRACTICAL COLUMN

The transaction and data-loss trap
With updated_at >= last_run_at, a transaction that has not committed yet can slip through the change window. If another process commits it using an older start time, the next batch may consider the row old and miss it. Defensive designs avoid this by using NOW() - INTERVAL '1 minute' as the upper bound or slightly overlapping the previous window and processing duplicates idempotently.

QUESTION 10

Batch job monitoring — Monitor duration, stalls, and consecutive failures in one SQL query

EXTRACTCOUNTJob monitoringBatch operations

Background ▼

This pattern monitors execution time, stalls, and consecutive failures in a batch-job history table with one query. It is useful for API health-check endpoints and monitoring dashboards.

-- Calculate duration: convert the timestamp difference to seconds
EXTRACT(EPOCH FROM (ended_at - started_at)) AS duration_sec
-- EXTRACT(EPOCH FROM interval): convert an interval to seconds

-- Consecutive-failure count: failures among the latest N runs
COUNT(*) FILTER (WHERE status = 'failed')  -- Count only matching rows

EXTRACT(EPOCH FROM interval): Converts the difference between two timestamps (an INTERVAL) to a number of seconds. Because (ended_at - started_at) returns an INTERVAL, converting it to seconds calculates the execution time.

Problem ▼

Using the job_runs table, output the following summary for each job in one query.

successes, failures, and average execution time (seconds) among the latest 5 runs
jobs currently running for at least 30 minutes (stall detection)
job names with at least 3 failures in the latest 5 runs (alert targets)

Tables used

▸ job_runs

run_id	job_name	status	started_at	ended_at
1	order_sync	success	2024-06-01 02:00	2024-06-01 02:05
2	order_sync	failed	2024-06-01 03:00	2024-06-01 03:02
3	email_batch	success	2024-06-01 02:00	2024-06-01 02:30
4	order_sync	failed	2024-06-01 04:00	2024-06-01 04:01
5	email_batch	failed	2024-06-01 03:00	2024-06-01 03:05
6	order_sync	running	2024-06-01 05:00	NULL
7	email_batch	failed	2024-06-01 04:00	2024-06-01 04:08
8	order_sync	failed	2024-06-01 06:00	2024-06-01 06:03
9	email_batch	failed	2024-06-01 05:00	2024-06-01 05:06

Expected Output ▼

job_name	success_count	fail_count	avg_sec	is_stalled	alert
email_batch	1	3	735.0	false	! ALERT
order_sync	1	3	165.0	true	! ALERT

Model Answer ▼

WITH recent_runs AS (
  -- ① Number each job’s runs newest-first to select its latest 5 runs
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY job_name
      ORDER BY     started_at DESC
    ) AS rn
  FROM job_runs
)

SELECT
  job_name,
  COUNT(*) FILTER (WHERE status = 'success') AS success_count,          -- ② Count successes and failures separately
  COUNT(*) FILTER (WHERE status = 'failed')  AS fail_count,
  ROUND(                                                                -- ③ Calculate average duration in seconds and round to one decimal place
    AVG(EXTRACT(EPOCH FROM (ended_at - started_at)))
  , 1) AS avg_sec,
  BOOL_OR(                                                              -- ④ Stall detection: TRUE if even one run has been running for at least 30 minutes
    status = 'running'
    AND started_at < TIMESTAMP '2024-06-01 06:30' - INTERVAL '30 minutes'
  ) AS is_stalled,
  CASE                                                                  -- ⑤ Alert rule: alert when at least 3 of the latest 5 runs failed
    WHEN COUNT(*) FILTER (WHERE status = 'failed') >= 3 THEN '! ALERT'
    ELSE 'OK'
  END AS alert

FROM  recent_runs
WHERE rn <= 5
GROUP BY job_name
ORDER BY job_name;

/*
  Logical execution order:
  1. FROM / ROW_NUMBER  → Evaluate the window function (row count is preserved)
  2. FROM(CTE) / WHERE  → Filter rows
  3. GROUP BY           → Group rows
  4. SELECT             → Evaluate aggregate functions
  5. ORDER BY           → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH recent_runs AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY job_name
      ORDER BY started_at DESC
    ) AS rn
  FROM job_runs
)
SELECT
  job_name,
  COUNT(*) FILTER (WHERE status = 'success') AS success_count,
  COUNT(*) FILTER (WHERE status = 'failed')  AS fail_count,
  ROUND(AVG(EXTRACT(EPOCH FROM (ended_at - started_at))), 1) AS avg_sec,
  BOOL_OR(status = 'running' AND started_at < TIMESTAMP '2024-06-01 06:30' - INTERVAL '30 minutes') AS is_stalled,
  CASE WHEN COUNT(*) FILTER (WHERE status = 'failed') >= 3
       THEN '! ALERT' ELSE 'OK' END AS alert
FROM  recent_runs
WHERE rn <= 5
GROUP BY job_name
ORDER BY job_name;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM job_runsRead all 9 rows from job_runs: 5 for order_sync and 4 for email_batch. The next step assigns a newest-first row number (rn) within each job.

1 / 5

run_id	job_name	status	started_at	ended_at
1	order_sync	success	02:00	02:05
2	order_sync	failed	03:00	03:02
3	email_batch	success	02:00	02:30
4	order_sync	failed	04:00	04:01
5	email_batch	failed	03:00	03:05
6	order_sync	running	05:00	NULL
7	email_batch	failed	04:00	04:08
8	order_sync	failed	06:00	06:03
9	email_batch	failed	05:00	05:06

9 rows read

LEARNING POINTS

Basic refresher (aggregate functions):COUNT (count), SUM (total), AVG (average), MAX (maximum), and MIN (minimum) are SQL's five core aggregate functions. They are often used with GROUP BY to combine multiple rows in each group into one result.

Applying this to monitoring dashboards: Expose this query as an API endpoint and poll it every 30 seconds to show real-time job health in a monitoring dashboard such as Grafana. It efficiently retrieves KPIs for all jobs in one query.

Using BOOL_OR:BOOL_OR(condition) is an aggregate that returns TRUE when at least one row in a group is TRUE. It is equivalent to COUNT(*) FILTER (WHERE ...) > 0 but expresses the intent more clearly.

Applying EXTRACT(EPOCH FROM ...):EXTRACT(EPOCH FROM NOW() - started_at) calculates a running job’s current duration in real time. It can be used to adjust stall-detection thresholds dynamically.

ANTI-PATTERNS

Averaging ended_at - started_at directly: AVG accepts INTERVAL values, but ROUND does not work as needed. Convert to seconds with EXTRACT(EPOCH FROM ...) before applying AVG/ROUND.

Using a subquery LIMIT to take the latest N rows: SELECT * FROM (SELECT * FROM job_runs ORDER BY started_at DESC LIMIT 5) returns the latest 5 rows globally instead of per job. To get the latest 5 for each job, use ROW_NUMBER with PARTITION BY.

PRACTICAL COLUMN

Automatic integration with monitoring SaaS (Datadog / PagerDuty)
No one runs a monitoring query like this manually every day. In production, a small program (an agent) runs it every minute and sends the result to monitoring SaaS such as Datadog or Mackerel as custom metrics. An automation flow can then alert Slack and page the owner through PagerDuty when a stall is detected.