Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement