gistfile1.sql

select

CASE
WHEN status IN ('OPEN','ACTIVE') then '1open' 
WHEN status IN ('SUBMITTED','IN_VALIDATION') or (statusid IN (14,15,30) and scoringmodel is null) then '11inprocess' 
WHEN statusid IN (21,27) THEN '2autoreject'
WHEN statusid IN (24,25,26,28) THEN '3premodelreject'
WHEN statusid IN (23) THEN '4modelreject'
WHEN statusid IN (29) THEN '5cbreject'
WHEN scoringmodel is not null and status IN ('REVIEW','REVIEW_DOCUMENTS','MISSING_DOCUMENTS','ACCEPTED','ACCEPTED_LIMIT','WAITING_PAYOUT','PRECONFIRMED') THEN '6acceptedWaiting'
WHEN scoringmodel is not null and status IN ('CANCELLED','TIMED_OUT') THEN '66acceptedLost'
WHEN status IN ('CONFIRMED','DUE','REPAID','REPAID_PENDING','SETTLED','DUE','FACTORING','FACTORING_PENDING','COLLECTION','COURT','DEFAULT') or status like '%OVERDUE%' THEN '7paidout'
ELSE status||statusid END as workflowPoint,
count(id),client

from application
where date(createdat) >= date(current_Timestamp - interval '1 days') and (firstapplication=true or status='REJECT')
group by client,workflowPoint
order by client,workflowPoint

-- high level dashboard (share with marketing and rest of company)
-- total number of leads (11+2+3+4+5+6+66+7)
-- acceptance ratio = (6+66+7) / (3+4+5+6+7)
-- loss after acceptance ratio = 66/(6+66+7)
-- Payout rate = paidout/num applications accepted = 7/(6+66+7)


-- operational dashboard (internal) global
-- split of cases in 2,3,4,5,6,66,7 (full workflow) as ratios over (2+3+4+5+6+66+7)

-- operational dashboard (internal) reject reasons
-- split of cases per statusid (21,22,23,24,25,26,27,28,29), out of all rejects

-- operational dashboard (internal) stuck applications