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: 1'102 − 749 + 106 = 459, 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.

Leads (Marketing)
1'102
by creation date
Appointments (Operations)
459
by appointment date, only scheduled or held

Marketing counts 643 more. 353 leads are in both.

Where the difference comes from

Counted by Marketing only

Counted by Operations 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((created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_a,
         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_b
  from leads
  where (created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
     or (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')
),
classified as (
  select scoped.*,
         case when in_a and not in_b then case when appointment_at is null then 'minus:time_null'
                  when appointment_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_before'
                  when appointment_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 created_at is null then 'plus:time_null'
                  when created_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_before'
                  when created_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((created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich'), false) as in_a,
         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_b
  from leads
  where (created_at >= timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' and created_at < timestamp '2026-10-01 00:00:00' at time zone 'Europe/Zurich')
     or (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')
),
classified as (
  select scoped.*,
         case when in_a and not in_b then case when appointment_at is null then 'minus:time_null'
                  when appointment_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'minus:time_before'
                  when appointment_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 created_at is null then 'plus:time_null'
                  when created_at < timestamp '2026-09-01 00:00:00' at time zone 'Europe/Zurich' then 'plus:time_before'
                  when created_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

Leads (Marketing)

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

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')

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