SDI Lab / BI cockpit Demo on a made-up Swiss insurance-lead business, data as of 28.10.2026 18:00. Names, brokers and numbers are invented.

Referee

Both numbers are right. Here is what each one counts that the other doesn't. ✓ Adds up: 459 − 359 + 92 = 192, and both totals match the numbers on the overview. Every step comes from the two definitions: the date each one counts by, and what it filters out. Only counts can be compared this way; revenue adds up francs, not leads.

Appointments (Operations)
459
by appointment date, only scheduled or held
Leads invoiced (Finance)
192
by invoice date

Operations counts 267 more. 100 leads are in both.

Where the difference comes from

Counted by Operations only

Counted by Finance only

How it's calculated

Rows per step

with scoped as (
  select lead_id, created_at, channel, canton, product, broker, appointment_at, appointment_status, contract_signed_at, invoiced_at, invoice_amount_chf,
         coalesce((appointment_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and appointment_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) and coalesce(appointment_status in ('scheduled', 'held'), false) as in_a,
         coalesce((invoiced_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and invoiced_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_b
  from leads
  where (appointment_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and appointment_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
     or (invoiced_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and invoiced_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
),
classified as (
  select scoped.*,
         case when in_a and not in_b then case when invoiced_at is null then 'minus:time_null'
                  when invoiced_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_before'
                  when invoiced_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_after'
                  else 'minus:filter' end
              when in_b and not in_a then case when appointment_at is null then 'plus:time_null'
                  when appointment_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_before'
                  when appointment_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_after'
                  else 'plus:filter' end
              when in_a then 'both'
              else 'neither' end as bucket
  from scoped
),
stepped as (
  select s.step, classified.*
  from classified
  cross join lateral (values (case when in_a then 'start' end),
                             (case when in_b then 'end' end),
                             (bucket)) as s(step)
  where s.step is not null
)
select step, count(*) as n
from stepped
group by step
order by step

Examples per step

with scoped as (
  select lead_id, created_at, channel, canton, product, broker, appointment_at, appointment_status, contract_signed_at, invoiced_at, invoice_amount_chf,
         coalesce((appointment_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and appointment_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) and coalesce(appointment_status in ('scheduled', 'held'), false) as in_a,
         coalesce((invoiced_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and invoiced_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_b
  from leads
  where (appointment_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and appointment_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
     or (invoiced_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and invoiced_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
),
classified as (
  select scoped.*,
         case when in_a and not in_b then case when invoiced_at is null then 'minus:time_null'
                  when invoiced_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_before'
                  when invoiced_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_after'
                  else 'minus:filter' end
              when in_b and not in_a then case when appointment_at is null then 'plus:time_null'
                  when appointment_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_before'
                  when appointment_at >= timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_after'
                  else 'plus:filter' end
              when in_a then 'both'
              else 'neither' end as bucket
  from scoped
),
stepped as (
  select s.step, classified.*
  from classified
  cross join lateral (values (case when in_a then 'start' end),
                             (case when in_b then 'end' end),
                             (bucket)) as s(step)
  where s.step is not null
)
select step, lead_id, created_at, channel, canton, product, broker, appointment_at, appointment_status, contract_signed_at, invoiced_at, invoice_amount_chf
from (
  select stepped.*, row_number() over (partition by step order by lead_id) as rn
  from stepped
  where step not in ('both', 'neither')
) ranked
where rn <= 5
order by step, lead_id

Appointments (Operations)

select count(distinct lead_id) as value
from leads
where appointment_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich'
  and appointment_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'
  and appointment_status in ('scheduled', 'held')

Leads invoiced (Finance)

select count(distinct lead_id) as value
from leads
where invoiced_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich'
  and invoiced_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'

The app runs these queries with parameters; here the values are written in, so they can be read or pasted into psql.