A redeployment report in plain SQL

The Monday-morning redeployment report does not need a BI tool. If placement data lands in a Postgres table, from your ATS API or a scheduled export, the whole report is a handful of queries and two window functions. This walkthrough builds it step by step. Column names are from the sync table in our end-dates walkthrough; adapt them to your own export. Everything here is standard SQL, and the window-function reference is in the official PostgreSQL documentation.

The two tables you need

Table	What it holds	One row per
placement_snapshots	Placements synced from the ATS: candidate, job, start and end dates, status	placement
outreach_log	Every redeployment touch a recruiter makes: who, when, on what channel	contact event

If nobody logs outreach anywhere queryable yet, start the log now; the report is honest only if the contacts are recorded:

create table outreach_log (
  id           bigserial primary key,
  candidate_id bigint not null,
  placement_id bigint,
  contacted_at timestamptz not null,
  channel      text not null,   -- 'email' | 'sms' | 'call'
  recruiter    text not null
);

Step one: the finishing buckets

Who finishes in the next 30, 60 and 90 days:

select
  placement_id,
  candidate_id,
  end_date,
  end_date - current_date as days_until_end,
  case
    when end_date - current_date <= 30 then 'finishing-30'
    when end_date - current_date <= 60 then 'finishing-60'
    else 'finishing-90'
  end as bucket
from placement_snapshots
where status = 'Active'
  and end_date - current_date between 0 and 90;

Do not lose the nulls

A placement with no end date vanishes from that query, and those are often the riskiest rows: the extension nobody recorded. Count them separately and put the number at the top of the report:

select count(*) as active_without_end_date
from placement_snapshots
where status = 'Active' and end_date is null;

If that number is large, fixing ATS hygiene is worth more than any report.

Step two: the most recent contact per candidate

row_number() over a window partitioned by candidate gives you the latest touch without a self-join:

select candidate_id, contacted_at, recruiter
from (
  select
    candidate_id,
    contacted_at,
    recruiter,
    row_number() over (
      partition by candidate_id
      order by contacted_at desc
    ) as rn
  from outreach_log
) t
where rn = 1;

row_number(), rank(), lag() and their siblings are all described on the window functions page; this pattern, latest-row-per-group, is the one you will reuse most.

Step three: the report itself

Join the buckets to the latest contact and classify each finishing consultant:

with finishing as (
  select placement_id, candidate_id, end_date,
         end_date - current_date as days_until_end,
         case
           when end_date - current_date <= 30 then 'finishing-30'
           when end_date - current_date <= 60 then 'finishing-60'
           else 'finishing-90'
         end as bucket
  from placement_snapshots
  where status = 'Active'
    and end_date - current_date between 0 and 90
),
last_contact as (
  select candidate_id, contacted_at, recruiter
  from (
    select candidate_id, contacted_at, recruiter,
           row_number() over (partition by candidate_id
                              order by contacted_at desc) as rn
    from outreach_log
  ) t
  where rn = 1
)
select
  f.bucket,
  f.candidate_id,
  f.end_date,
  f.days_until_end,
  lc.contacted_at as last_contacted,
  lc.recruiter,
  case
    when lc.contacted_at is null then 'never contacted'
    when lc.contacted_at < now() - interval '14 days' then 'gone quiet'
    else 'in conversation'
  end as contact_status
from finishing f
left join last_contact lc using (candidate_id)
order by f.days_until_end, lc.contacted_at nulls first;

A few rows of output look like this:

bucket	candidate_id	end_date	days_until_end	last_contacted	contact_status
finishing-30	88112	2026-09-12	18		never contacted
finishing-30	90417	2026-09-19	25	2026-08-02	gone quiet
finishing-60	87003	2026-10-21	57	2026-08-21	in conversation

The sort order is deliberate: soonest end date first, never-contacted above everyone else. The top of the report is the phone list.

Step four: make it arrive

A report nobody opens changes nothing. Schedule the query with cron or your job runner, render the rows to a plain HTML table, and send it to the recruiters and the owner early Monday. Join candidate names from wherever they live in your snapshot, for example out of the raw JSON: raw -> 'candidate' ->> 'name'. Names make it a work list; ids make it a data exercise.

What the numbers will tell you

Run it for a month and the pattern is usually the same one we described when measuring redeployment honestly: conversion is fine, contact rate is the leak, and the consultants who leave are mostly the ones nobody called. The report is the measurement half of redeployment. The action half, matching finishing consultants to open reqs and drafting the outreach for a recruiter to send, is what a Redeployment Agent does in production. If you want help wiring either half to your ATS, contact us and name your systems.