Skip to main content

Queries: the SQL behind every table and chart on the other four pages

This page holds the SQL for the numbers on the other four pages of the auto-select timing initiative. Paste a statement into a Metabase native question and you get the same number again. Every statement below was run by the analysts who wrote the four pages.

How to run these​

  • Database: jodgig-ai, Metabase database id 2. Schema: jodgig_ai. It is a nightly copy of production with personal data removed.
  • Every datetime column is naive Singapore time. There is no time zone on them.
  • Never compare a column to NOW(). The copy is a day or so behind production, so NOW() gives a different window every day. Write the date you mean.
  • Metabase stops a statement at 30 seconds. A statement that needs more is marked below, with the way to split it.
  • Two variables carry the window: {{start_date}} and {{end_date}}. Create both as Text or Date filter widgets.
  • The range is always half-open: job_start_date >= {{start_date}} AND job_start_date < {{end_date}}.

Default values for the twelve-week window, shifts starting 22 June to 13 September 2026:

variablevaluenote
{{start_date}}2026-06-22 00:00:00the first Monday of the window
{{end_date}}2026-09-14 00:00:00the end is excluded, so the window closes 2026-09-13 23:59:59

Some queries need a different start date, and each one says which value to paste:

  • the weekly trend on the current metrics page starts at 2026-04-27 00:00:00.
  • the before-and-after queries on the impact page start at 2025-06-02 00:00:00, at 2025-12-01 00:00:00, or at 2024-06-03 00:00:00.

Definitions every query shares​

termhow a query writes it
a missjod_jobs.status = 8 at an outlet with locations.is_auto_select_applicant = 1, with at least one live eligible applicant. 703 jobs in the twelve weeks.
live eligible applicanta job_user row with deleted_at IS NULL, status IN (1, 10), app_user_id <> 100641, whose users row has status = 1 and is_deleted = 0
normal jobcreated_at <= job_start_date - INTERVAL 24 HOUR. It expires at job_start_date - INTERVAL 8 HOUR.
short-notice jobcreated_at > job_start_date - INTERVAL 24 HOUR. It expires at job_start_date.
the day-before run09:00 the day before the shift when the shift starts before noon, 17:00 the day before when it starts at noon or later. The expression is below this table.
auto-selected jobauto_select_user_id IS NOT NULL and not a repost that only copied the column: NOT (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id), where p is the parent row
manager-selected jobhired_by_manager_id IS NOT NULL and not auto-selected by the rule above
worked slota payments row with deleted_at IS NULL, payment_status = 2, status = 1 and user_id <> 100641. The company charge is admin_jod_credit.
the excluded userapp_user_id = 100641 is a test account. The selection code always skips it, so every query skips it too.

The day-before run, as an expression on a jod_jobs row named j:

CASE WHEN HOUR(j.job_start_date) < 12
     THEN TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), '09:00:00')
     ELSE TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), '17:00:00') END AS cutoff

Traps​

trapwhat to write instead
job_user.apply_date is NULL on about 8% of the rows, 1,788 of 80,390 in the window. created_at is never NULL.Take the application time as COALESCE(ju.apply_date, ju.created_at).
jod_jobs.prev_job_id has no index, so looking for a parent's children scans the table.Query from the child row: start at the clone and read j.prev_job_id.
Joining locations onto a large set of jod_jobs rows makes the optimizer start from locations, and the statement passes 30 seconds.Write STRAIGHT_JOIN on that join. j.location_id IN (SELECT ...) fails the same way.
slot_user has no index on slot_id alone, so joining slots to slot_user directly passes 30 seconds.Group slot_user by slot_id in a sub-query first, then join that sub-query to slots.
A repost copies its parent's auto_select_user_id. A plain = inside NOT (...) returns NULL when the parent's value is NULL, and the row silently drops out. It drops 421 real auto-selections.Compare with <=>, which is null-safe.
job_user.applicant_rank can be NULL, and MySQL sorts NULL first in ORDER BY applicant_rank.An applicant with no rank comes first. Sort NULL last when you want the applicant the code should have picked.
A job where only user 100641 applied keeps no_users_apply_job = 0 and ends in status: 7, not status: 8.Leave those jobs out. status = 8 already excludes them.
payments joins to a job on job_id, and its jodder column is user_id, not app_user_id.Write py.job_id = j.id and py.user_id <> 100641.

Queries for the current metrics page​

No-selection jobs and slots at every outlet, split by the outlet's auto-select flag​

Feeds the loss table in section 1. It returns two rows, one for is_auto_select_applicant = 1 and one for 0. The two rows add to 1,009 jobs and 1,062 slots, and the flag-off row holds 224 jobs. The bucket columns are close to the 515 / 147 / 41 on the problems page, but not equal to them. This query does not check that the applicant's users row is enabled.

SELECT x.loc_auto,
  COUNT(*) AS jobs_no_selection, SUM(x.n_slots) AS slots_no_selection,
  SUM(x.live_at_end = 0) AS D_no_live_applicant_left,
  SUM(x.live_at_end > 0 AND x.created_at >= x.cutoff) AS A_posted_after_pass,
  SUM(x.live_at_end > 0 AND x.created_at < x.cutoff AND x.first_live >= x.cutoff) AS B_live_applicant_after_pass,
  SUM(x.live_at_end > 0 AND x.first_live < x.cutoff) AS C_live_applicant_at_pass
FROM (
  SELECT j.id, j.created_at, j.job_start_date, l.is_auto_select_applicant AS loc_auto,
    CASE WHEN HOUR(j.job_start_date) < 12
         THEN TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), '09:00:00')
         ELSE TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), '17:00:00') END AS cutoff,
    (SELECT COUNT(*) FROM jodgig_ai.slots s WHERE s.jod_job_id = j.id AND s.deleted_at IS NULL) AS n_slots,
    (SELECT COUNT(*) FROM jodgig_ai.job_user ju WHERE ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.status IN (1,10)) AS live_at_end,
    (SELECT MIN(COALESCE(ju.apply_date, ju.created_at)) FROM jodgig_ai.job_user ju
       WHERE ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.status IN (1,10)) AS first_live
  FROM jodgig_ai.jod_jobs j JOIN jodgig_ai.locations l ON l.id = j.location_id
  WHERE j.deleted_at IS NULL AND j.status = 8
    AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
    AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
) x
GROUP BY 1;

The 703 misses: no-selection jobs at an outlet with the flag on that still had a live eligible applicant​

Feeds the miss count in the loss table in section 1, and the same count on the three other pages. It returns 778 no-selection jobs and 703 misses. It takes about 2.3 seconds.

SELECT STRAIGHT_JOIN COUNT(*) AS status8_jobs,
       SUM(EXISTS (SELECT 1 FROM jodgig_ai.job_user ju JOIN jodgig_ai.users u ON u.id = ju.app_user_id
                   WHERE ju.job_id = j.id AND ju.status IN (1,10) AND ju.deleted_at IS NULL
                     AND ju.app_user_id <> 100641 AND u.status = 1 AND u.is_deleted = 0)) AS misses,
       SUM(EXISTS (SELECT 1 FROM jodgig_ai.job_user ju
                   WHERE ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.app_user_id <> 100641)) AS status8_with_any_application,
       SUM(l.status = 0) AS at_disabled_outlet
FROM jodgig_ai.jod_jobs j
JOIN jodgig_ai.locations l ON l.id = j.location_id
WHERE j.deleted_at IS NULL AND l.deleted_at IS NULL AND l.is_auto_select_applicant = 1 AND j.status = 8
  AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
  AND j.job_start_date <  {{end_date}};   -- 2026-09-14 00:00:00

No-selection slots by shift week, all outlets​

Feeds the twenty-week trend table in section 1.1. Set {{start_date}} to 2026-04-27 00:00:00 for this one. It returns 20 rows: 82 slots for the week of 27 April and 89 for the week of 7 September.

SELECT DATE_SUB(DATE(s.slot_start_date), INTERVAL WEEKDAY(s.slot_start_date) DAY) AS week_monday,
       COUNT(*) AS no_selection_slots
FROM jodgig_ai.slots s
JOIN jodgig_ai.jod_jobs j ON j.id = s.jod_job_id AND j.status = 8 AND j.deleted_at IS NULL
WHERE s.deleted_at IS NULL
  AND s.slot_start_date >= {{start_date}}  -- 2026-04-27 00:00:00
  AND s.slot_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
GROUP BY week_monday ORDER BY week_monday;

Applications by weekday and hour, behind the applications heat map​

Feeds the heat map in section 3 and the hourly counts under it. It returns 168 rows that add to 78,252 applications. WEEKDAY() returns 0 for Monday. Add the hours 17 to 23 and 00 to 08. That gives the 43,304 applications (55%) the problems page counts between the evening run and the morning run.

SELECT WEEKDAY(t.app_time) AS weekday_monday_is_0,
       HOUR(t.app_time)    AS hour_of_day,
       COUNT(*)            AS applications
FROM (
  SELECT COALESCE(ju.apply_date, ju.created_at) AS app_time
  FROM jodgig_ai.job_user ju
  JOIN jodgig_ai.jod_jobs j ON j.id = ju.job_id AND j.deleted_at IS NULL
  WHERE ju.deleted_at IS NULL
    AND ju.app_user_id <> 100641
    AND COALESCE(ju.apply_date, ju.created_at) >= {{start_date}}  -- 2026-06-22 00:00:00
    AND COALESCE(ju.apply_date, ju.created_at) <  {{end_date}}    -- 2026-09-14 00:00:00
) t
GROUP BY 1, 2
ORDER BY 1, 2;

Applications by clock band, split by the kind of job applied to​

Feeds the clock band table in section 3. It returns two rows: 68,335 applications to normal jobs and 9,917 to short-notice jobs.

SELECT CASE WHEN j.created_at <= j.job_start_date - INTERVAL 24 HOUR THEN 'normal' ELSE 'short_notice' END AS job_kind,
       COUNT(*) AS applications,
       SUM(HOUR(COALESCE(ju.apply_date, ju.created_at)) < 6)               AS band_00_00_to_05_59,
       SUM(HOUR(COALESCE(ju.apply_date, ju.created_at)) BETWEEN  6 AND 11) AS band_06_00_to_11_59,
       SUM(HOUR(COALESCE(ju.apply_date, ju.created_at)) BETWEEN 12 AND 17) AS band_12_00_to_17_59,
       SUM(HOUR(COALESCE(ju.apply_date, ju.created_at)) >= 18)             AS band_18_00_to_23_59
FROM jodgig_ai.job_user ju
JOIN jodgig_ai.jod_jobs j ON j.id = ju.job_id AND j.deleted_at IS NULL
WHERE ju.deleted_at IS NULL
  AND ju.app_user_id <> 100641
  AND COALESCE(ju.apply_date, ju.created_at) >= {{start_date}}  -- 2026-06-22 00:00:00
  AND COALESCE(ju.apply_date, ju.created_at) <  {{end_date}}    -- 2026-09-14 00:00:00
GROUP BY 1;

Hiring manager selections by weekday and hour, behind the selections heat map​

Feeds the heat map in section 4. The selection time is the earliest slot_user.created_at on the job. It returns 168 rows that add to 17,754 jobs. It takes about 25 seconds. If the 30-second limit stops it, run it twice. First set {{end_date}} to 2026-08-03 00:00:00. Then set {{start_date}} to 2026-08-03 00:00:00 and put {{end_date}} back. Add the two grids together.

SELECT WEEKDAY(t.sel_at) AS weekday_monday_is_0,
       HOUR(t.sel_at)    AS hour_of_day,
       COUNT(*)          AS manager_selected_jobs
FROM (
  SELECT s.jod_job_id, MIN(x.min_created) AS sel_at
  FROM jodgig_ai.slots s
  JOIN (
    SELECT su.slot_id, MIN(su.created_at) AS min_created
    FROM jodgig_ai.slot_user su
    WHERE su.deleted_at IS NULL
    GROUP BY su.slot_id
  ) x ON x.slot_id = s.id
  WHERE s.deleted_at IS NULL
    AND s.jod_job_id IN (
      SELECT j.id
      FROM jodgig_ai.jod_jobs j
      LEFT JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
      WHERE j.deleted_at IS NULL
        AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
        AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
        AND j.hired_by_manager_id IS NOT NULL
        AND (j.auto_select_user_id IS NULL
             OR (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id))
    )
  GROUP BY s.jod_job_id
) t
GROUP BY 1, 2
ORDER BY 1, 2;

Hours from the first application on the job to the hiring manager's selection​

Feeds the first lead-time table in section 4. It returns two rows: 14,929 normal jobs with a median of 15.5 hours, and 2,825 short-notice jobs with a median of 0.5 hours.

WITH mj AS (
  SELECT j.id,
         j.job_start_date,
         CASE WHEN j.created_at <= j.job_start_date - INTERVAL 24 HOUR THEN 'normal' ELSE 'short_notice' END AS job_kind
  FROM jodgig_ai.jod_jobs j
  LEFT JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
  WHERE j.deleted_at IS NULL
    AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
    AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
    AND j.hired_by_manager_id IS NOT NULL
    AND (j.auto_select_user_id IS NULL
         OR (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id))
),
sel AS (
  SELECT s.jod_job_id AS job_id, MIN(x.min_created) AS sel_at
  FROM jodgig_ai.slots s
  JOIN (
    SELECT su.slot_id, MIN(su.created_at) AS min_created
    FROM jodgig_ai.slot_user su
    WHERE su.deleted_at IS NULL
    GROUP BY su.slot_id
  ) x ON x.slot_id = s.id
  WHERE s.deleted_at IS NULL AND s.jod_job_id IN (SELECT id FROM mj)
  GROUP BY s.jod_job_id
),
app AS (
  SELECT ju.job_id, MIN(COALESCE(ju.apply_date, ju.created_at)) AS first_app
  FROM jodgig_ai.job_user ju
  WHERE ju.deleted_at IS NULL
    AND ju.app_user_id <> 100641
    AND ju.job_id IN (SELECT id FROM mj)
  GROUP BY ju.job_id
),
b AS (
  SELECT mj.job_kind, TIMESTAMPDIFF(MINUTE, app.first_app, sel.sel_at) / 60.0 AS h
  FROM mj
  JOIN sel ON sel.job_id = mj.id
  JOIN app ON app.job_id = mj.id
),
r AS (
  SELECT b.*,
         ROW_NUMBER() OVER (PARTITION BY job_kind ORDER BY h) AS rn,
         COUNT(*)     OVER (PARTITION BY job_kind)            AS c
  FROM b
)
SELECT job_kind,
       MAX(c) AS jobs,
       ROUND(MIN(h), 2) AS min_hours,
       ROUND(MAX(h), 2) AS max_hours,
       MAX(CASE WHEN rn = FLOOR((c + 1) * 0.25) THEN ROUND(h, 2) END) AS lower_quartile_hours,
       MAX(CASE WHEN rn = FLOOR((c + 1) * 0.50) THEN ROUND(h, 2) END) AS median_hours,
       MAX(CASE WHEN rn = FLOOR((c + 1) * 0.75) THEN ROUND(h, 2) END) AS upper_quartile_hours,
       SUM(h <  0)                AS selected_before_first_application,
       SUM(h >= 0  AND h <  1)    AS under_1h,
       SUM(h >= 1  AND h <  3)    AS h_1_to_3,
       SUM(h >= 3  AND h <  6)    AS h_3_to_6,
       SUM(h >= 6  AND h < 12)    AS h_6_to_12,
       SUM(h >= 12 AND h < 24)    AS h_12_to_24,
       SUM(h >= 24)               AS over_24h
FROM r
GROUP BY job_kind;

Hours from the selection to the shift start, the clock band of the selection, and the day-before run​

Feeds the second lead-time table and the clock band table in section 4. It returns two rows: a median of 129 hours on normal jobs and 8.7 hours on short-notice jobs. selected_after_the_day_before_run is 2.1% of the normal jobs and 97.7% of the short-notice ones.

WITH mj AS (
  SELECT j.id,
         j.job_start_date,
         CASE WHEN j.created_at <= j.job_start_date - INTERVAL 24 HOUR THEN 'normal' ELSE 'short_notice' END AS job_kind
  FROM jodgig_ai.jod_jobs j
  LEFT JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
  WHERE j.deleted_at IS NULL
    AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
    AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
    AND j.hired_by_manager_id IS NOT NULL
    AND (j.auto_select_user_id IS NULL
         OR (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id))
),
sel AS (
  SELECT s.jod_job_id AS job_id, MIN(x.min_created) AS sel_at
  FROM jodgig_ai.slots s
  JOIN (
    SELECT su.slot_id, MIN(su.created_at) AS min_created
    FROM jodgig_ai.slot_user su
    WHERE su.deleted_at IS NULL
    GROUP BY su.slot_id
  ) x ON x.slot_id = s.id
  WHERE s.deleted_at IS NULL AND s.jod_job_id IN (SELECT id FROM mj)
  GROUP BY s.jod_job_id
),
b AS (
  SELECT mj.job_kind,
         sel.sel_at,
         TIMESTAMPDIFF(MINUTE, sel.sel_at, mj.job_start_date) / 60.0 AS h,
         CASE WHEN HOUR(mj.job_start_date) < 12
              THEN TIMESTAMP(DATE(mj.job_start_date) - INTERVAL 1 DAY, '09:00:00')
              ELSE TIMESTAMP(DATE(mj.job_start_date) - INTERVAL 1 DAY, '17:00:00') END AS run_at
  FROM mj
  JOIN sel ON sel.job_id = mj.id
),
r AS (
  SELECT b.*,
         ROW_NUMBER() OVER (PARTITION BY job_kind ORDER BY h) AS rn,
         COUNT(*)     OVER (PARTITION BY job_kind)            AS c
  FROM b
)
SELECT job_kind,
       MAX(c) AS jobs,
       MAX(CASE WHEN rn = FLOOR((c + 1) * 0.25) THEN ROUND(h, 2) END) AS lower_quartile_hours,
       MAX(CASE WHEN rn = FLOOR((c + 1) * 0.50) THEN ROUND(h, 2) END) AS median_hours,
       MAX(CASE WHEN rn = FLOOR((c + 1) * 0.75) THEN ROUND(h, 2) END) AS upper_quartile_hours,
       SUM(h < 0)                 AS selected_after_the_shift_started,
       SUM(h <  1)                AS under_1h,
       SUM(h >= 1  AND h <  3)    AS h_1_to_3,
       SUM(h >= 3  AND h <  6)    AS h_3_to_6,
       SUM(h >= 6  AND h < 12)    AS h_6_to_12,
       SUM(h >= 12 AND h < 24)    AS h_12_to_24,
       SUM(h >= 24)               AS over_24h,
       SUM(sel_at > run_at)       AS selected_after_the_day_before_run,
       SUM(HOUR(sel_at) < 6)                 AS band_00_00_to_05_59,
       SUM(HOUR(sel_at) BETWEEN  6 AND 11)   AS band_06_00_to_11_59,
       SUM(HOUR(sel_at) BETWEEN 12 AND 17)   AS band_12_00_to_17_59,
       SUM(HOUR(sel_at) >= 18)               AS band_18_00_to_23_59
FROM r
GROUP BY job_kind;

Queries for the problems page​

Every miss in exactly one of the three buckets​

Feeds the bucket table in section 1. It returns three rows: 515 jobs created after their run, 147 whose first live applicant came after the run, and 41 where both were there and the run did nothing. The ukg column on the third row is 31. It takes about 4.3 seconds.

SELECT bucket, COUNT(*) AS misses,
       SUM(is_repost) AS reposts, SUM(short_notice) AS short_notice, SUM(source = 'ukg') AS ukg
FROM (
  SELECT STRAIGHT_JOIN j.id, j.source, (j.prev_job_id IS NOT NULL) AS is_repost,
         (TIMESTAMPDIFF(SECOND, j.created_at, j.job_start_date) < 24 * 3600) AS short_notice,
         TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), IF(HOUR(j.job_start_date) < 12, '09:00:00', '17:00:00')) AS run_time,
         GREATEST(j.created_at, COALESCE(j.approved_datetime, j.created_at)) AS open_at,
         (SELECT MIN(COALESCE(ju.apply_date, ju.created_at)) FROM jodgig_ai.job_user ju JOIN jodgig_ai.users u ON u.id = ju.app_user_id
           WHERE ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.app_user_id <> 100641
             AND ju.status IN (1, 10) AND u.status = 1 AND u.is_deleted = 0) AS first_live_apply,
         CASE
           WHEN j.created_at > TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), IF(HOUR(j.job_start_date) < 12, '09:00:00', '17:00:00')) THEN 'B1 job created after its run'
           WHEN (SELECT MIN(COALESCE(ju.apply_date, ju.created_at)) FROM jodgig_ai.job_user ju JOIN jodgig_ai.users u ON u.id = ju.app_user_id
                  WHERE ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.app_user_id <> 100641
                    AND ju.status IN (1, 10) AND u.status = 1 AND u.is_deleted = 0)
                > TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), IF(HOUR(j.job_start_date) < 12, '09:00:00', '17:00:00')) THEN 'B2 first live applicant after the run'
           ELSE 'B3 both present at the run' END AS bucket
  FROM jodgig_ai.jod_jobs j
  JOIN jodgig_ai.locations l ON l.id = j.location_id
  WHERE j.deleted_at IS NULL AND l.deleted_at IS NULL AND l.is_auto_select_applicant = 1 AND j.status = 8
    AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
    AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
) m
WHERE first_live_apply IS NOT NULL
GROUP BY bucket ORDER BY bucket;

The parent job's unselected applicants who applied again to the clone​

Feeds the two re-application rows in the repost table in section 3. It returns 3,688 unselected applications on the parent jobs and 2,051 of them applied again to the clone.

SELECT COUNT(*) AS parent_unselected_applications,
       COUNT(DISTINCT c.id) AS clones_with_parent_unselected,
       SUM(cu.app_user_id IS NOT NULL) AS reapplied_to_clone,
       SUM(cu.status = 2) AS selected_on_clone
FROM jodgig_ai.jod_jobs c
JOIN jodgig_ai.job_user pu ON pu.job_id = c.prev_job_id AND pu.status = 3 AND pu.deleted_at IS NULL AND pu.app_user_id <> 100641
LEFT JOIN jodgig_ai.job_user cu ON cu.job_id = c.id AND cu.app_user_id = pu.app_user_id AND cu.deleted_at IS NULL
WHERE c.prev_job_id IS NOT NULL AND c.deleted_at IS NULL
  AND c.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
  AND c.job_start_date <  {{end_date}};   -- 2026-09-14 00:00:00

What the repost clones earned, behind the repost facts on the problems page​

Feeds the problems page, section 3. For the default window it returns 1,302 clones with paid slots, 1,416 paid slots and SGD 137,253 of company credits.

SELECT COUNT(DISTINCT c.id) AS clones_with_paid_slots,
       COUNT(p.id) AS paid_slots,
       ROUND(SUM(p.admin_jod_credit), 0) AS company_credits
FROM jodgig_ai.jod_jobs c
JOIN jodgig_ai.payments p
  ON p.job_id = c.id AND p.payment_status = 2 AND p.status = 1 AND p.deleted_at IS NULL
WHERE c.prev_job_id IS NOT NULL AND c.deleted_at IS NULL
  AND c.job_start_date >= {{start_date}}  -- '2026-06-22 00:00:00'
  AND c.job_start_date <  {{end_date}}    -- '2026-09-14 00:00:00'

Clones whose auto-select marker is only a copy of the parent's​

Feeds the fourth defect in section 4. It returns two rows: 207 clones of 425 carry a marker copied from the parent, and 169 of those 207 were never selected at all. The <=> is required, as the traps table says.

SELECT CASE WHEN p.auto_select_user_id <=> j.auto_select_user_id THEN 'marker copied from parent' ELSE 'auto-selected on the clone itself' END AS kind,
       COUNT(*) AS clones, SUM(j.hired_by_manager_id IS NOT NULL) AS a_hire_happened,
       SUM(j.status IN (3, 16)) AS completed, SUM(j.status = 8) AS expired_no_selection
FROM jodgig_ai.jod_jobs j
JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
WHERE j.deleted_at IS NULL AND j.auto_select_user_id IS NOT NULL
  AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
GROUP BY kind;

Queries for the solution page​

How many of the 703 misses a selection at the job's last safe moment would catch, at each floor​

Feeds the rule 1 rows of the replay table in section 5. It returns 703 jobs, of which 452 are caught at a 6-hour floor, 492 at 4 hours and 517 at 3 hours. The 251 misses the problems page calls out of reach at a 6-hour floor are 703 minus 452.

SELECT COUNT(*) AS jobs,
       SUM(m.normal) AS normal_jobs,
       SUM(m.normal AND m.x < DATE_SUB(m.t, INTERVAL 8 HOUR)) AS normal_caught_at_t_minus_8h,
       SUM(NOT m.normal) AS short_notice_jobs,
       SUM(NOT m.normal AND m.x <= DATE_SUB(m.t, INTERVAL 6 HOUR)) AS sn_caught_floor_6h,
       SUM(NOT m.normal AND m.x <= DATE_SUB(m.t, INTERVAL 4 HOUR)) AS sn_caught_floor_4h,
       SUM(NOT m.normal AND m.x <= DATE_SUB(m.t, INTERVAL 3 HOUR)) AS sn_caught_floor_3h,
       SUM(m.is_repost) AS repost_jobs,
       SUM(m.is_repost AND m.x <= DATE_SUB(m.t, INTERVAL 6 HOUR)) AS repost_caught_floor_6h
FROM (
  SELECT j.id, j.job_start_date AS t, (j.prev_job_id IS NOT NULL) AS is_repost,
         (j.created_at <= DATE_SUB(j.job_start_date, INTERVAL 24 HOUR)) AS normal,
         GREATEST(j.created_at, MIN(COALESCE(ju.apply_date, ju.created_at))) AS x
  FROM jodgig_ai.jod_jobs j
  JOIN jodgig_ai.locations l ON l.id = j.location_id AND l.is_auto_select_applicant = 1
  JOIN jodgig_ai.job_user ju ON ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.status IN (1, 10) AND ju.app_user_id <> 100641
  JOIN jodgig_ai.users u ON u.id = ju.app_user_id AND u.status = 1 AND u.is_deleted = 0
  WHERE j.status = 8 AND j.deleted_at IS NULL
    AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
    AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
  GROUP BY j.id, j.job_start_date, j.created_at, j.prev_job_id
) m;

How many of the 703 misses a pair of fixed clock hours would catch​

Feeds the fixed-hour rows of the replay table in section 5, and the search in section 4. As written it tests three pairs and returns 184 caught for 09:00 and 17:00, 388 for 01:00 and 06:00, and 357 for 06:00 and 23:00. Replace the CROSS JOIN list with every pair of hours from 0 to 23 to search all 276 pairs, which takes about 29 seconds.

SELECT h1, h2, COUNT(*) AS jobs, SUM(caught) AS caught
FROM (
  SELECT hh.h1, hh.h2, m.id,
         (LEAST(
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h1 * 3600)) END,
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h2 * 3600)) END
          ) < m.expiry
          AND LEAST(
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h1 * 3600)) END,
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h2 * 3600)) END
          ) <= DATE_SUB(m.t, INTERVAL 6 HOUR)) AS caught
  FROM (
    SELECT j.id, j.job_start_date AS t,
           GREATEST(j.created_at, MIN(COALESCE(ju.apply_date, ju.created_at))) AS x,
           CASE WHEN j.created_at <= DATE_SUB(j.job_start_date, INTERVAL 24 HOUR) THEN DATE_SUB(j.job_start_date, INTERVAL 8 HOUR) ELSE j.job_start_date END AS expiry
    FROM jodgig_ai.jod_jobs j
    JOIN jodgig_ai.locations l ON l.id = j.location_id AND l.is_auto_select_applicant = 1
    JOIN jodgig_ai.job_user ju ON ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.status IN (1, 10) AND ju.app_user_id <> 100641
    JOIN jodgig_ai.users u ON u.id = ju.app_user_id AND u.status = 1 AND u.is_deleted = 0
    WHERE j.status = 8 AND j.deleted_at IS NULL
      AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
      AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
    GROUP BY j.id, j.job_start_date, j.created_at
  ) m
  CROSS JOIN (SELECT 9 h1, 17 h2 UNION ALL SELECT 1, 6 UNION ALL SELECT 6, 23) hh
) r
GROUP BY h1, h2 ORDER BY caught DESC;

The hourly sweep, a variant of the fixed-hour query​

Feeds the solution page, section 5, the row "hourly sweep from the first applicant", 447 of 703. Take the fixed-hour query above and replace the LEAST(...) expression, both times it appears, with the next full hour after the job and its first applicant both exist:

DATE_ADD(DATE_FORMAT(m.x, '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR)

The CROSS JOIN of hours is then not needed.

Hiring manager picks on short-notice jobs that a selection 6 hours before the shift would have made first​

Feeds the short-notice row of the manager table in section 5. It returns 2,631 manager-selected short-notice jobs and 239 that rule 1 would have taken first, which is the 9.5%.

SELECT COUNT(*) AS manager_selected_short_notice_jobs,
       SUM(m.pick_at > DATE_SUB(m.t, INTERVAL 6 HOUR)) AS picked_inside_last_6h,
       SUM(m.x <= DATE_SUB(m.t, INTERVAL 6 HOUR) AND m.pick_at > DATE_SUB(m.t, INTERVAL 6 HOUR)) AS preempted_by_select_at_t_minus_6h
FROM (
  SELECT j.id, j.job_start_date AS t,
         GREATEST(j.created_at, MIN(COALESCE(ju.apply_date, ju.created_at))) AS x,
         MIN(su.created_at) AS pick_at
  FROM jodgig_ai.jod_jobs j
  JOIN jodgig_ai.locations l ON l.id = j.location_id AND l.is_auto_select_applicant = 1
  JOIN jodgig_ai.job_user ju ON ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.app_user_id <> 100641
  JOIN jodgig_ai.slots s ON s.jod_job_id = j.id AND s.deleted_at IS NULL
  JOIN jodgig_ai.slot_user su ON su.slot_id = s.id AND su.deleted_at IS NULL
  WHERE j.deleted_at IS NULL AND j.hired_by_manager_id IS NOT NULL AND j.auto_select_user_id IS NULL
    AND j.created_at > DATE_SUB(j.job_start_date, INTERVAL 24 HOUR)
    AND j.job_start_date >= {{start_date}}  -- 2026-06-22 00:00:00
    AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
  GROUP BY j.id, j.job_start_date, j.created_at
) m;

Hiring manager picks a pair of fixed clock hours would have made first​

Feeds the solution page, section 5, the row "any two fixed clock times, windows removed", 49% to 54% of picks on normal jobs. This query uses a four-week window, shifts starting 17 August to 13 September 2026, because the join to slot_user on twelve weeks exceeds 30 seconds. For that window it returns 5,443 manager-selected jobs, 4,559 of them normal, and 2,642 to 2,949 pre-empted depending on the pair.

SELECT r.label,
       COUNT(*) AS manager_selected_jobs,
       SUM(r.preempted) AS preempted_by_run,
       SUM(r.normal) AS normal_jobs,
       SUM(r.preempted AND r.normal) AS preempted_normal,
       SUM(r.preempted AND NOT r.normal) AS preempted_short_notice
FROM (
  SELECT CONCAT(LPAD(hh.h1,2,'0'), ':00 + ', LPAD(hh.h2,2,'0'), ':00') AS label, m.id, m.normal,
         (LEAST(
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h1 * 3600)) END,
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h2 * 3600)) END
          ) < m.pick_at
          AND LEAST(
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h1 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h1 * 3600)) END,
            CASE WHEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) >= m.x THEN TIMESTAMP(DATE(m.x), SEC_TO_TIME(hh.h2 * 3600)) ELSE TIMESTAMP(DATE(m.x) + INTERVAL 1 DAY, SEC_TO_TIME(hh.h2 * 3600)) END
          ) <= DATE_SUB(m.t, INTERVAL 6 HOUR)) AS preempted
  FROM (
    SELECT j.id, j.job_start_date AS t,
           (j.created_at <= DATE_SUB(j.job_start_date, INTERVAL 24 HOUR)) AS normal,
           GREATEST(j.created_at, MIN(COALESCE(ju.apply_date, ju.created_at))) AS x,
           MIN(su.created_at) AS pick_at
    FROM jodgig_ai.jod_jobs j
    JOIN jodgig_ai.locations l ON l.id = j.location_id AND l.is_auto_select_applicant = 1
    JOIN jodgig_ai.job_user ju ON ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.app_user_id <> 100641
    JOIN jodgig_ai.slots s ON s.jod_job_id = j.id AND s.deleted_at IS NULL
    JOIN jodgig_ai.slot_user su ON su.slot_id = s.id AND su.deleted_at IS NULL
    WHERE j.deleted_at IS NULL AND j.hired_by_manager_id IS NOT NULL AND j.auto_select_user_id IS NULL
      AND j.job_start_date >= '2026-08-17 00:00:00' AND j.job_start_date < '2026-09-14 00:00:00'
    GROUP BY j.id, j.job_start_date, j.created_at
  ) m
  CROSS JOIN (
    SELECT 9 h1, 17 h2 UNION ALL SELECT 10, 22 UNION ALL SELECT 1, 6 UNION ALL SELECT 0, 6 UNION ALL SELECT 1, 17 UNION ALL SELECT 22, 6
  ) hh
) r
GROUP BY r.label ORDER BY preempted_by_run

Queries for the impact page​

Weekly jobs, jobs with applicants, selections and completions, by the outlet's flag today​

Feeds the three-period table in section 1, the seasonal check in section 2 and the rates in section 7. Set {{start_date}} to 2025-06-02 00:00:00. It returns one row per week and outlet flag, about 67 weeks in all. Summed by period, the rows give the 8.59%, 9.22% and 4.71% no-selection rates. It takes about 20 seconds.

SELECT DATE(DATE_SUB(j.job_start_date, INTERVAL WEEKDAY(j.job_start_date) DAY)) AS wk,
       l.is_auto_select_applicant AS flag_on,
       COUNT(*)                                AS jobs,
       SUM(j.no_users_apply_job > 0)           AS jobs_with_applicants,
       SUM(j.hired_by_manager_id IS NOT NULL)  AS selected,
       SUM(j.status = 8)                       AS no_selection,
       SUM(j.status IN (3, 16))                AS completed,
       SUM(j.status = 7)                       AS expired_no_applicants,
       COUNT(DISTINCT j.location_id)           AS outlets
FROM jodgig_ai.jod_jobs j
STRAIGHT_JOIN jodgig_ai.locations l ON l.id = j.location_id
WHERE j.deleted_at IS NULL
  AND j.job_start_date >= {{start_date}}  -- 2025-06-02 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
GROUP BY wk, flag_on
ORDER BY wk, flag_on
LIMIT 200;

The selected column counts auto-selected and manager-selected jobs together. Subtract the auto-selected jobs from the next query to get the manager-selected ones.

Weekly real auto-selected jobs, by the outlet's flag today​

Feeds the auto-selected rows of the three-period table in section 1 and the gross value table in section 3. Set {{start_date}} to 2025-12-01 00:00:00. It returns 47 auto-selected jobs a week in the opt-in period and 126 a week in the twelve weeks, of which 1,010 of 1,513 completed.

SELECT DATE(DATE_SUB(j.job_start_date, INTERVAL WEEKDAY(j.job_start_date) DAY)) AS wk,
       l.is_auto_select_applicant AS flag_on,
       COUNT(*)                 AS auto_selected,
       SUM(j.status IN (3, 16)) AS auto_completed
FROM jodgig_ai.jod_jobs j
STRAIGHT_JOIN jodgig_ai.locations l ON l.id = j.location_id
LEFT JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
WHERE j.deleted_at IS NULL
  AND j.auto_select_user_id IS NOT NULL
  AND NOT (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id)
  AND j.job_start_date >= {{start_date}}  -- 2025-12-01 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
GROUP BY wk, flag_on
ORDER BY wk, flag_on
LIMIT 200;

Weekly paid slots and credits on every job, by the outlet's flag today​

Feeds the credits rows of the three-period table in section 1 and the SGD 90.89 a worked slot used on every page. Set {{start_date}} to 2025-06-02 00:00:00. Credits divided by paid slots is 90.89 in the twelve weeks and 89.1 in the before period. It takes about 20 seconds.

SELECT DATE(DATE_SUB(j.job_start_date, INTERVAL WEEKDAY(j.job_start_date) DAY)) AS wk,
       l.is_auto_select_applicant AS flag_on,
       COUNT(*)                 AS paid_slots,
       SUM(py.admin_jod_credit) AS credits
FROM jodgig_ai.jod_jobs j
STRAIGHT_JOIN jodgig_ai.payments py ON py.job_id = j.id
STRAIGHT_JOIN jodgig_ai.locations l ON l.id = j.location_id
WHERE j.deleted_at IS NULL
  AND j.job_start_date >= {{start_date}}  -- 2025-06-02 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
  AND py.deleted_at IS NULL AND py.payment_status = 2 AND py.status = 1
  AND py.user_id <> 100641
GROUP BY wk, flag_on
ORDER BY wk, flag_on
LIMIT 200;

Weekly paid slots and credits on auto-selected jobs only​

Feeds the gross value table in section 3. Set {{start_date}} to 2025-12-01 00:00:00. It returns 1,023 paid slots and SGD 7,176 a week in the twelve weeks, and 819 slots and SGD 2,454 a week in the opt-in period.

SELECT DATE(DATE_SUB(j.job_start_date, INTERVAL WEEKDAY(j.job_start_date) DAY)) AS wk,
       l.is_auto_select_applicant AS flag_on,
       COUNT(*)                 AS auto_paid_slots,
       SUM(py.admin_jod_credit) AS auto_credits
FROM jodgig_ai.jod_jobs j
LEFT JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
STRAIGHT_JOIN jodgig_ai.payments py ON py.job_id = j.id
STRAIGHT_JOIN jodgig_ai.locations l ON l.id = j.location_id
WHERE j.deleted_at IS NULL
  AND j.auto_select_user_id IS NOT NULL
  AND NOT (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id)
  AND j.job_start_date >= {{start_date}}  -- 2025-12-01 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
  AND py.deleted_at IS NULL AND py.payment_status = 2 AND py.status = 1
  AND py.user_id <> 100641
GROUP BY wk, flag_on
ORDER BY wk, flag_on
LIMIT 200;

The same weeks one year earlier, with no auto-select anywhere​

Feeds the seasonal check in section 2. Set {{start_date}} to 2024-06-03 00:00:00 and {{end_date}} to 2025-06-02 00:00:00 for the 2024 weeks. The weeks of 2025 and 2026 come from the weekly query with the flag split above. The after-window rate is 8.24% in 2024 and 7.70% in 2025.

SELECT DATE(DATE_SUB(j.job_start_date, INTERVAL WEEKDAY(j.job_start_date) DAY)) AS wk,
       COUNT(*)                               AS jobs,
       SUM(j.no_users_apply_job > 0)          AS jobs_with_applicants,
       SUM(j.hired_by_manager_id IS NOT NULL) AS selected,
       SUM(j.status = 8)                      AS no_selection,
       SUM(j.status IN (3, 16))               AS completed
FROM jodgig_ai.jod_jobs j
WHERE j.deleted_at IS NULL
  AND j.job_start_date >= {{start_date}}  -- 2024-06-03 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2025-06-02 00:00:00
GROUP BY wk
ORDER BY wk
LIMIT 60;

The final status of every selected job, auto-selected against manager-selected​

Feeds the outcome table in section 5. Set {{start_date}} to 2025-12-01 00:00:00. The period cut at 22 June 2026 is a fixed date inside the query. For the twelve weeks it gives 14.1% rejected and 11.4% cancelled on auto-selected jobs, against 8.7% and 9.8% on manager-selected ones, and completion of 66.8% against 78.2%.

SELECT CASE WHEN j.job_start_date < '2026-06-22 00:00:00' THEN 'P1_optin' ELSE 'P2_allon' END AS period,
       (j.auto_select_user_id IS NOT NULL
        AND NOT (j.prev_job_id IS NOT NULL AND p.auto_select_user_id <=> j.auto_select_user_id)) AS is_auto,
       j.status,
       COUNT(*) AS n
FROM jodgig_ai.jod_jobs j
LEFT JOIN jodgig_ai.jod_jobs p ON p.id = j.prev_job_id
WHERE j.deleted_at IS NULL
  AND j.hired_by_manager_id IS NOT NULL
  AND j.job_start_date >= {{start_date}}  -- 2025-12-01 00:00:00
  AND j.job_start_date <  {{end_date}}    -- 2026-09-14 00:00:00
GROUP BY period, is_auto, j.status
ORDER BY period, is_auto, j.status
LIMIT 80;

Changi Business Park jobs before, during and after the flag was on​

Feeds the first three rows of the table in section 6. This query does not use the two variables: its three periods are fixed dates. It returns 1,352 jobs with applicants before, 1,023 during and 608 after, with no-selection rates of 9.0%, 8.6% and 11.0%.

SELECT CASE WHEN j.job_start_date < '2026-06-22 00:00:00' THEN '1 before'
            WHEN j.job_start_date < '2026-08-12 00:00:00' THEN '2 during' ELSE '3 after' END AS period,
       COUNT(*) AS jobs, SUM(j.no_users_apply_job > 0) AS jobs_with_applicants,
       SUM(j.auto_select_user_id IS NOT NULL) AS auto_selected,
       SUM(j.hired_by_manager_id IS NOT NULL AND j.auto_select_user_id IS NULL) AS manager_selected,
       SUM(j.status = 8) AS no_selection, SUM(j.status IN (3, 16)) AS completed
FROM jodgig_ai.jod_jobs j
WHERE j.deleted_at IS NULL AND j.location_id = 693
  AND j.job_start_date >= '2026-04-01 00:00:00' AND j.job_start_date < '2026-09-14 00:00:00'
GROUP BY period ORDER BY period;

Changi Business Park paid slots and credits in the same three periods​

Feeds the credits row of the table in section 6. This query does not use the two variables either. Divide the credits by the days in each period, 82, 51 and 33, to get SGD 1,017, SGD 1,273 and SGD 1,040 a day.

SELECT CASE WHEN j.job_start_date < '2026-06-22 00:00:00' THEN '1 before'
            WHEN j.job_start_date < '2026-08-12 00:00:00' THEN '2 during' ELSE '3 after' END AS period,
       COUNT(p.id) AS paid_slots, ROUND(SUM(p.admin_jod_credit), 0) AS credits
FROM jodgig_ai.payments p
JOIN jodgig_ai.jod_jobs j ON j.id = p.job_id AND j.deleted_at IS NULL AND j.location_id = 693
WHERE p.payment_status = 2 AND p.status = 1 AND p.deleted_at IS NULL
  AND j.job_start_date >= '2026-04-01 00:00:00' AND j.job_start_date < '2026-09-14 00:00:00'
GROUP BY period ORDER BY period;

Changi Business Park: how many of its misses each rule reaches​

Feeds the solution page, section 5, and the impact page, section 8: 150 no-selection jobs with a live applicant at outlet 693, of which 123 (82%) had the applicant 6 hours or more before the shift, 126 at 4 hours, and 65 (43%) had the applicant sitting there at the day-before run.

SELECT COUNT(*) AS cbp_no_selection_jobs_with_live_applicant,
       SUM(m.normal) AS normal_jobs,
       SUM(NOT m.normal) AS short_notice_jobs,
       SUM(m.normal OR m.x <= DATE_SUB(m.t, INTERVAL 6 HOUR)) AS caught_by_last_safe_moment_6h,
       SUM(m.normal OR m.x <= DATE_SUB(m.t, INTERVAL 4 HOUR)) AS caught_4h,
       SUM(m.x <= m.pass AND m.created_at < m.pass) AS applicant_at_day_before_run
FROM (
  SELECT j.id, j.job_start_date AS t, j.created_at,
         (j.created_at <= DATE_SUB(j.job_start_date, INTERVAL 24 HOUR)) AS normal,
         GREATEST(j.created_at, MIN(COALESCE(ju.apply_date, ju.created_at))) AS x,
         CASE WHEN HOUR(j.job_start_date) < 12
              THEN TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), '09:00:00')
              ELSE TIMESTAMP(DATE_SUB(DATE(j.job_start_date), INTERVAL 1 DAY), '17:00:00') END AS pass
  FROM jodgig_ai.jod_jobs j
  JOIN jodgig_ai.job_user ju ON ju.job_id = j.id AND ju.deleted_at IS NULL AND ju.status IN (1, 10) AND ju.app_user_id <> 100641
  JOIN jodgig_ai.users u ON u.id = ju.app_user_id AND u.status = 1 AND u.is_deleted = 0
  WHERE j.status = 8 AND j.deleted_at IS NULL AND j.location_id = 693
    AND j.job_start_date >= {{start_date}}  -- '2026-06-22 00:00:00'
    AND j.job_start_date <  {{end_date}}    -- '2026-09-14 00:00:00'
  GROUP BY j.id, j.job_start_date, j.created_at
) m

Not yet reproducible​

These tables and numbers have no tested query yet. Most of them came from a replay script that ran on a local copy of the per-job pull in scratchpad/verify-apply-timing-queries.sql, query 1.3. Tables that state what the code does, such as the four defects and the five rules on the problems page, come from reading the code, so no query applies to them.

  • Current metrics section 2, the ten companies with the most no-selection jobs, including the manager picks made after 24 hours.
  • Current metrics section 2, the ten outlets with the most no-selection jobs, including the "applicant 6h or more" column.
  • Problems section 2, the three job-posting facts: jobs posted between 17:00 and 08:59, jobs posted in the 17:00 hour, and misses whose shift starts between 07:00 and 18:59.
  • Problems section 4, the UKG rollback cost of 31 jobs is in the bucket query, but the 23 jobs whose top applicant had no applicant_rank are not.
  • Solution section 5, rule 1 with night selections pulled to 21:59 the evening before, 359 of 703.
  • Solution section 5, reposts under rule 3: 967 clones that expired unfilled, 396 with a free parent applicant, 193 with 6 hours of lead, 244 with 3 hours, and the 34 clones that never got an applicant of their own.
  • Impact section 1, the opening facts: the first auto-selected job 591235, the 2,876 real auto-picks, and the 31 in the week of 1 December 2025.
  • Impact section 4, the lower, central and upper net readings. They are arithmetic on top of the queries above, not queries.
  • Impact section 7, the outlets with the flag off today: the 24 of 2,381, the outlet that posts 59% of their jobs, and the 216 auto-selected jobs at Changi Business Park before it switched off on 11 August 2026.
  • Impact section 8, the short-notice run that shipped on 16 September 2026, 68 of 631 no-selection slots in eight weeks.
  • Impact section 8, the completion rate of re-selected parent applicants, 86%.