fork download
copy 
  1.  
  2. with loan_status_cohort as (
  3. 	select loan_id,lal.principle ,extract(month from day) as loan_mon , extract(year from day) as loan_year
  4. 	from loan_app_loanstatushistroy 
  5. 	inner join loan_app_loan lal on lal.id = loan_id
  6. 	where   status_type=0 and loan_app_loanstatushistroy.status_id in (12,13)
  7. ),
  8.  
  9. -- for future updates: add customer filters here
  10. 	customer_filter as (
  11. 	select * from (
  12.     select distinct cac.id as cust, case when cac2.customer_id is null then 0 else 1 end as app, cac.first_loan_cycle_id, cac.limit_source, cac.rank_id,
  13. 	risk_score,
  14. 	cac.zone_id,
  15.     cac.governorate_id,
  16.     cac.gender,
  17.     cac.marital_status,
  18.     cacc.jobtitle_rating,
  19.     ccs.banked_income_delta,
  20.     ccs.unbanked_income_delta,
  21. 		case when (regexp_split_to_array(cac.national_id, E'(?=(.)+$)'))[1] = '2' then 
  22. 	 	extract(year from CURRENT_DATE)::integer - (array_to_string((regexp_split_to_array(cac.national_id, E'(?=(.)+$)'))[2:3],'','')::integer + 1900)
  23. 	 	else  extract(year from CURRENT_DATE)::integer - (array_to_string((regexp_split_to_array(cac.national_id, E'(?=(.)+$)'))[2:3],'','')::integer + 2000) end as age
  24. 		from crm_app_customer cac 
  25. 		inner join newlms_mob_view_materialized mmv on mmv.customer_id = cac.id
  26. 		left join crm_app_consumer cac2 on cac2.customer_id = cac.id
  27.         left join customer_creditscores ccs on ccs.customer_id = cac.id
  28.         left join credit_app_creditcustomer cacc on cacc.customer_id = cac.id
  29. 		) as inner_filter
  30.         left join datasets_zone on datasets_zone.id = inner_filter.zone_id
  31.         left join crm_app_customerrank cacr on cacr.id = inner_filter.rank_id 
  32. 		where 1=1 
  33. 		 [[and risk_score >= {'number':'risk_score_start'}]] [[and risk_score <=  {'number':'risk_score_end'}]] 
  34.         [[ and datasets_zone.category  = '{'dropdown_from_table':'zone':[table_name:datasets_zone,column_name_view:category,column_name_key:category]}']]
  35.         [[ and age >= {'number':'age_from'}]] [[ and age <= {'number':'age_to'}]] [[ and first_loan_cycle_id = {'number':'cycle'}]]
  36.         [[and inner_filter.gender = {'dropdown':'Gender':[0:Male,1:Female]}]]
  37.         [[and inner_filter.marital_status = {'dropdown':'Marital_Status':[0:Single,1:Married,2:Divorced,3:Widowed]}]]
  38.         [[and inner_filter.jobtitle_rating = {'dropdown':'Employment_Category':['A':A,'B':B,'C':C,'D':D,'E':E,'NAN':NAN]}]]
  39.         [[and inner_filter.banked_income_delta = {'dropdown':'Banked_Income_Delta_Tier':[1:1,2:2,3:3,4:4,5:5,6:6,7:7,8:8,9:9,10:10,11:11]}]]
  40.         [[and inner_filter.unbanked_income_delta = {'dropdown':'UnBanked_Income_Delta_Tier':[1:1,2:2,3:3,4:4,5:5,6:6,7:7,8:8,9:9,10:10,11:11]}]]
  41.         [[and app = {'dropdown':'App_Downloaded':[0:Not_Downloaded,1:Downloaded]}]]
  42.         [[and limit_source = {'dropdown':'Limit_Source':[1:Banked,2:Unbanked]}]]
  43.         [[and cacr.id  = '{'dropdown_from_table':'rank':[table_name:crm_app_customerrank,column_name_view:name,column_name_key:id]}']]
  44.         [[and inner_filter.governorate_id  = '{'dropdown_from_table':'governorate':[table_name:datasets_governorate,column_name_view:name,column_name_key:id]}']]
  45.         [[and cac.governorate_id in {'multi_dropdown_from_table':'governorate':[table_name:datasets_governorate,column_name_view:name,column_name_key:id]}]]
  46.  
  47.  
  48.  
  49.  
  50. 	),
  51.  
  52. -- for future updates: add merchant filters here
  53. merchant_filter as (
  54.     select lal.id
  55.     from loan_app_loan lal 
  56.     inner join crm_app_merchant on lal.merchant_id = crm_app_merchant.id
  57.     left join crm_app_branchstaff on lal.cashier_id = crm_app_branchstaff.staff_id
  58.     left join crm_app_branch on crm_app_branchstaff.branch_id = crm_app_branch.id
  59.     inner join loan_app_loanproduct lalp on lalp.id = lal.loan_product_id
  60.     where 1=1 
  61.     [[and crm_app_merchant.id = {'dropdown_from_table':'merchant':[table_name:crm_app_merchant,column_name_view:name,column_name_key:id]}]] 
  62.     [[and crm_app_branch.id = {'dropdown_from_table':'branch':[table_name:crm_app_branch,column_name_view:short_name,column_name_key:id,filter_column:merchant_id,filter_value:crm_app_merchant.id]}]]
  63.     [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  64.     [[and lalp.type = {'dropdown':'product_type':['zr':'zr','ib':'ib']}]]
  65.     [[and lal.num_months = {'number':'tenor'}]]
  66. ), 
  67. loan_sequence as (
  68.     select
  69.         lal.id as loan_id,
  70.         row_number() over (
  71.             partition by lal.customer_id
  72.             order by lal.loan_booking_day, lal.id
  73.         ) as loan_seq
  74.     from loan_app_loan lal
  75. ),
  76.  
  77. branch_filter as (
  78.     select crm_app_branch.id
  79.     from crm_app_branch
  80.     where 1=1
  81.     [[and crm_app_branch.merchant_id = {'dropdown_from_table':'merchant':[table_name:crm_app_merchant,column_name_view:name,column_name_key:id]}]]
  82.     [[and crm_app_branch.id = {'dropdown_from_table':'branch':[table_name:crm_app_branch,column_name_view:short_name,column_name_key:id,filter_column:merchant_id,filter_value:crm_app_merchant.id]}]]
  83.  
  84.  
  85. ),
  86.  
  87.  
  88. customer_first_loan as (
  89.  
  90. 	select min_month, min_year, sum(case when id = first_loan then 1 else 0 end) as first_transaction from (
  91. select lal.id, lal.customer_id, lal.loan_booking_day, extract (month from lal.loan_booking_day) as min_month, extract (year from lal.loan_booking_day) as min_year,
  92. (select min(lal2.id) from loan_app_loan lal2 where lal2.customer_id = lal.customer_id  ) as first_loan
  93. from loan_app_loan lal
  94. inner join customer_filter cac on cac.cust = lal.customer_id 
  95. inner join merchant_filter mf on mf.id = lal.id
  96. inner join loan_app_loanproduct lalp on lalp.id = lal.loan_product_id
  97. inner join loan_sequence ls on ls.loan_id = lal.id
  98. where 1=1 [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  99. [[and lalp.type = {'dropdown':'product_type':['zr':'zr','ib':'ib']}]]
  100. [[and lal.num_months = {'number':'tenor'}]]
  101. [[and {'dropdown':'Customer_Type':['new':'new','existing':'existing']} = 'new' and ls.loan_seq = 1]]
  102. [[and {'dropdown':'Customer_Type':['new':'new','existing':'existing']} = 'existing' and ls.loan_seq > 1]]
  103.  
  104.  
  105.  
  106. ) as foo
  107. group by min_month, min_year	 
  108. )
  109. ,
  110. main_query as (
  111.     select 
  112.         bookings,
  113.         booked_loans,
  114.         first_trans,
  115.         loan_year,
  116.         loan_mon,
  117.         is_axis
  118.     from (
  119.         select 
  120.             loan_year,
  121.             loan_mon,
  122.             case when bool_or(is_axis::boolean) then 1 else 0 end as is_axis,
  123.             sum(net) filter (where net <> 0) as bookings,
  124.             count(id) as booked_loans,
  125.             concat(round(first_transaction * 100.00 / count(id), 2), '%') as first_trans
  126.         from (
  127.             select 
  128.                 extract(year from lal.loan_booking_day) as loan_year,
  129.                 extract(month from lal.loan_booking_day) as loan_mon,
  130.                 lal.principle - coalesce(lsc.principle, 0) as net,
  131.                 lal.id,
  132.                 case when lal.is_axis = true then 1 else 0 end as is_axis
  133.             from loan_app_loan lal
  134.             inner join loan_sequence ls on ls.loan_id = lal.id
  135.                 left join loan_status_cohort lsc 
  136.                     on lsc.loan_mon = extract(month from lal.loan_booking_day)
  137.                     and lsc.loan_year = extract(year from lal.loan_booking_day)
  138.                     and lsc.loan_id = lal.id
  139.                 inner join customer_filter on customer_filter.cust = lal.customer_id
  140.                 inner join merchant_filter mf on mf.id = lal.id
  141.                 inner join loan_app_loanproduct lalp on lalp.id = lal.loan_product_id
  142.            where 1=1 
  143.                [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  144.                [[and lalp.type = {'dropdown':'product_type':['zr':'zr','ib':'ib']}]]
  145.                [[and lal.num_months = {'number':'tenor'}]]
  146.                 [[and is_axis = {'dropdown':'axis':[False:False,True:True]}]]
  147.  
  148.         ) as t
  149.         inner join customer_first_loan cfl 
  150.             on cfl.min_month = loan_mon and cfl.min_year = loan_year
  151.         [[and {'dropdown':'Customer_Type':['new':'new','existing':'existing']} = 'new' and ls.loan_seq = 1]]
  152. 		[[and {'dropdown':'Customer_Type':['new':'new','existing':'existing']} = 'existing' and ls.loan_seq > 1]]
  153.         group by 
  154.             loan_year, 
  155.             loan_mon, 
  156.             first_transaction
  157.     ) as t1
  158. ),
  159.  
  160.  
  161.  
  162.  
  163.  
  164.  
  165. delinq_table as (
  166. 	select mq.bookings,mq.booked_loans,mq.first_trans, mq.loan_year, mq.loan_mon ,
  167.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=0 )),0)/bookings*100 ,3) end as "0" ,
  168. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=1 )),0)/bookings*100 ,3)end  as "1" ,
  169. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=2 )),0)/bookings*100 ,3)end  as "2" ,
  170. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=3 )),0)/bookings*100 ,3)end  as "3" ,
  171. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=4 )),0)/bookings*100 ,3)end  as "4" ,
  172. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=5 )),0)/bookings*100 ,3)end  as "5" ,
  173. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=6 )),0)/bookings*100 ,3)end  as "6" ,
  174. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=7 )),0)/bookings*100 ,3)end  as "7" ,
  175. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=8 )),0)/bookings*100 ,3)end  as "8" ,
  176. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=9 )),0)/bookings*100 ,3)end  as "9" ,
  177. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=10 )),0)/bookings*100 ,3)end  as "10" ,
  178. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=11 )),0)/bookings*100 ,3)end  as "11" ,
  179. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=12 )),0)/bookings*100 ,3)end  as "12" ,
  180. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=13 )),0)/bookings*100 ,3)end  as "13" ,
  181. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=14 )),0)/bookings*100 ,3)end  as "14",
  182. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=15 )),0)/bookings*100 ,3)end  as "15" ,
  183. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=16 )),0)/bookings*100 ,3)end  as "16" ,
  184. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=17 )),0)/bookings*100 ,3)end  as "17" ,
  185. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=18 )),0)/bookings*100 ,3)end  as "18" ,
  186. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=19 )),0)/bookings*100 ,3)end  as "19" ,
  187. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=20 )),0)/bookings*100 ,3)end  as "20" ,
  188. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=21 )),0)/bookings*100 ,3)end  as "21" ,
  189. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=22 )),0)/bookings*100 ,3)end  as "22" ,
  190. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=23 )),0)/bookings*100 ,3)end  as "23" ,
  191. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=24 )),0)/bookings*100 ,3)end  as "24" ,
  192. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=25 )),0)/bookings*100 ,3)end  as "25" ,
  193. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=26 )),0)/bookings*100 ,3)end  as "26" ,
  194. 	 case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=27 )),0)/bookings*100 ,3)end  as "27" ,
  195.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=28 )),0)/bookings*100 ,3)end  as "28" ,
  196.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=29 )),0)/bookings*100 ,3)end  as "29" ,
  197.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=30 )),0)/bookings*100 ,3)end  as "30" ,
  198.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=31 )),0)/bookings*100 ,3)end  as "31" ,
  199.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=32 )),0)/bookings*100 ,3)end  as "32" ,
  200.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=33 )),0)/bookings*100 ,3)end  as "33" ,
  201.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=34 )),0)/bookings*100 ,3)end  as "34" ,
  202.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=35 )),0)/bookings*100 ,3)end  as "35" ,
  203.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=36 )),0)/bookings*100 ,3)end  as "36" ,
  204.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=37 )),0)/bookings*100 ,3)end  as "37" ,
  205.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=38 )),0)/bookings*100 ,3)end  as "38" ,
  206.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=39 )),0)/bookings*100 ,3)end  as "39" ,
  207.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=40 )),0)/bookings*100 ,3)end  as "40" ,
  208.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=41 )),0)/bookings*100 ,3)end  as "41" ,
  209.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=42 )),0)/bookings*100 ,3)end  as "42" ,
  210.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=43 )),0)/bookings*100 ,3)end  as "43" ,
  211.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=44 )),0)/bookings*100 ,3)end  as "44" ,
  212.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=45 )),0)/bookings*100 ,3)end  as "45" ,
  213.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=46 )),0)/bookings*100 ,3)end  as "46" ,
  214.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=47 )),0)/bookings*100 ,3)end  as "47" ,
  215.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=48 )),0)/bookings*100 ,3)end  as "48" ,
  216.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=49 )),0)/bookings*100 ,3)end  as "49" ,
  217.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=50 )),0)/bookings*100 ,3)end  as "50" ,
  218.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=51 )),0)/bookings*100 ,3)end  as "51" ,
  219.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=52 )),0)/bookings*100 ,3)end  as "52" ,
  220.      case when bookings = 0 then 0 else round(coalesce ((sum(remaining_principal) filter (where case when (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]} )=1 then mmv.status_id = 1 else mmv.status_id >= (select id from loan_app_loanstatus where id = {'dropdown_from_table':'status':[table_name:loan_app_loanstatus,column_name_view:name,column_name_key:id]}) or mmv.status_id = 7 end  and mmv.status_id not in (6,8,12,13,15)   and vintage_index=53 )),0)/bookings*100 ,3)end  as "53"
  221. 	  from newlms_mob_view_materialized mmv
  222.  
  223. 	left join main_query mq on booking_year = mq.loan_year and  booking_month = mq.loan_mon
  224. 	inner join customer_filter cf on cf.cust = mmv.customer_id
  225. 	inner join loan_app_loan lal on lal.id = loan_id_seq
  226. 	inner join merchant_filter mf on mf.id = lal.id
  227. 	inner join loan_sequence ls on ls.loan_id = lal.id
  228.  
  229.    	where 1=1 and seq_date <= current_date
  230. [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  231. [[and lal.num_months = {'number':'tenor'}]]
  232. [[and {'dropdown':'Customer_Type':['new':'new','existing':'existing']} = 'new' and ls.loan_seq = 1]]
  233. [[and {'dropdown':'Customer_Type':['new':'new','existing':'existing']} = 'existing' and ls.loan_seq > 1]]
  234.  
  235. 	group by  mq.bookings,mq.first_trans,mq.booked_loans,mq.loan_year,mq.loan_mon 
  236.  
  237.  
  238. )
  239.  
  240.  
  241. select * from delinq_table
  242. order by loan_year, loan_mon
Success #stdin #stdout #stderr 0.01s 5320KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 2: near "from": syntax error
https://ideone.com/W579ti
language:
SQLite (sqlite 3.27.2)
created:
1 day ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!