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.  
  68. branch_filter as (
  69.     select crm_app_branch.id
  70.     from crm_app_branch
  71.     where 1=1
  72.     [[and crm_app_branch.merchant_id = {'dropdown_from_table':'merchant':[table_name:crm_app_merchant,column_name_view:name,column_name_key:id]}]]
  73.     [[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]}]]
  74.  
  75.  
  76. ),
  77.  
  78.  
  79. customer_first_loan as (
  80.  
  81. 	select min_month, min_year, sum(case when id = first_loan then 1 else 0 end) as first_transaction, id, customer_id, first_loan, row_num from (
  82. 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,
  83. (select min(lal2.id) from loan_app_loan lal2 where lal2.customer_id = lal.customer_id  ) as first_loan
  84. row_number() over (partition by lal.customer_id order by lal.loan_booking_day, lal.id) as row_num
  85. from loan_app_loan lal
  86. inner join customer_filter cac on cac.cust = lal.customer_id 
  87. inner join merchant_filter mf on mf.id = lal.id
  88. inner join loan_app_loanproduct lalp on lalp.id = lal.loan_product_id
  89. where 1=1 [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  90. [[and lalp.type = {'dropdown':'product_type':['zr':'zr','ib':'ib']}]]
  91. [[and lal.num_months = {'number':'tenor'}]]
  92.  
  93. ) as foo
  94. group by min_month, min_year, id, customer_id, first_loan, row_num		 
  95. )
  96. ,
  97. main_query as (
  98.     select 
  99.         bookings,
  100.         booked_loans,
  101.         first_trans,
  102.         loan_year,
  103.         loan_mon,
  104.         is_axis
  105.     from (
  106.         select 
  107.             loan_year,
  108.             loan_mon,
  109.             case when bool_or(is_axis::boolean) then 1 else 0 end as is_axis,
  110.             sum(net) filter (where net <> 0) as bookings,
  111.             count(id) as booked_loans,
  112.             concat(round(first_transaction * 100.00 / count(id), 2), '%') as first_trans
  113.         from (
  114.             select 
  115.                 extract(year from lal.loan_booking_day) as loan_year,
  116.                 extract(month from lal.loan_booking_day) as loan_mon,
  117.                 lal.principle - coalesce(lsc.principle, 0) as net,
  118.                 lal.id,
  119.                 case when lal.is_axis = true then 1 else 0 end as is_axis
  120.             from loan_app_loan lal
  121.                 left join loan_status_cohort lsc 
  122.                     on lsc.loan_mon = extract(month from lal.loan_booking_day)
  123.                     and lsc.loan_year = extract(year from lal.loan_booking_day)
  124.                     and lsc.loan_id = lal.id
  125.                 inner join customer_filter on customer_filter.cust = lal.customer_id
  126.                 inner join merchant_filter mf on mf.id = lal.id
  127.                 inner join loan_app_loanproduct lalp on lalp.id = lal.loan_product_id
  128.            where 1=1 
  129.                [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  130.                [[and lalp.type = {'dropdown':'product_type':['zr':'zr','ib':'ib']}]]
  131.                [[and lal.num_months = {'number':'tenor'}]]
  132.                 [[and is_axis = {'dropdown':'axis':[False:False,True:True]}]]
  133.  
  134.         ) as t
  135.         inner join customer_first_loan cfl 
  136.             on cfl.min_month = loan_mon and cfl.min_year = loan_year and cfl.id = t.id
  137.         where 1=1
  138.             [[and cfl.row_num = case when '{'dropdown':'Customer_Type':[1:New]}' = '1' then 1 else case when cfl.row_num > 1 then cfl.row_num end end]]
  139.         group by 
  140.             loan_year, 
  141.             loan_mon, 
  142.             first_transaction
  143.     ) as t1
  144. ),
  145.  
  146.  
  147.  
  148.  
  149.  
  150.  
  151. delinq_table as (
  152. 	select mq.bookings,mq.booked_loans,mq.first_trans, mq.loan_year, mq.loan_mon ,
  153.      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" ,
  154. 	 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" ,
  155. 	 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" ,
  156. 	 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" ,
  157. 	 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" ,
  158. 	 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" ,
  159. 	 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" ,
  160. 	 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" ,
  161. 	 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" ,
  162. 	 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" ,
  163. 	 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" ,
  164. 	 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" ,
  165. 	 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" ,
  166. 	 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" ,
  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=14 )),0)/bookings*100 ,3)end  as "14",
  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=15 )),0)/bookings*100 ,3)end  as "15" ,
  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=16 )),0)/bookings*100 ,3)end  as "16" ,
  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=17 )),0)/bookings*100 ,3)end  as "17" ,
  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=18 )),0)/bookings*100 ,3)end  as "18" ,
  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=19 )),0)/bookings*100 ,3)end  as "19" ,
  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=20 )),0)/bookings*100 ,3)end  as "20" ,
  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=21 )),0)/bookings*100 ,3)end  as "21" ,
  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=22 )),0)/bookings*100 ,3)end  as "22" ,
  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=23 )),0)/bookings*100 ,3)end  as "23" ,
  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=24 )),0)/bookings*100 ,3)end  as "24" ,
  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=25 )),0)/bookings*100 ,3)end  as "25" ,
  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=26 )),0)/bookings*100 ,3)end  as "26" ,
  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=27 )),0)/bookings*100 ,3)end  as "27" ,
  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=28 )),0)/bookings*100 ,3)end  as "28" ,
  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=29 )),0)/bookings*100 ,3)end  as "29" ,
  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=30 )),0)/bookings*100 ,3)end  as "30" ,
  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=31 )),0)/bookings*100 ,3)end  as "31" ,
  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=32 )),0)/bookings*100 ,3)end  as "32" ,
  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=33 )),0)/bookings*100 ,3)end  as "33" ,
  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=34 )),0)/bookings*100 ,3)end  as "34" ,
  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=35 )),0)/bookings*100 ,3)end  as "35" ,
  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=36 )),0)/bookings*100 ,3)end  as "36" ,
  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=37 )),0)/bookings*100 ,3)end  as "37" ,
  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=38 )),0)/bookings*100 ,3)end  as "38" ,
  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=39 )),0)/bookings*100 ,3)end  as "39" ,
  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=40 )),0)/bookings*100 ,3)end  as "40" ,
  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=41 )),0)/bookings*100 ,3)end  as "41" ,
  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=42 )),0)/bookings*100 ,3)end  as "42" ,
  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=43 )),0)/bookings*100 ,3)end  as "43" ,
  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=44 )),0)/bookings*100 ,3)end  as "44" ,
  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=45 )),0)/bookings*100 ,3)end  as "45" ,
  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=46 )),0)/bookings*100 ,3)end  as "46" ,
  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=47 )),0)/bookings*100 ,3)end  as "47" ,
  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=48 )),0)/bookings*100 ,3)end  as "48" ,
  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=49 )),0)/bookings*100 ,3)end  as "49" ,
  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=50 )),0)/bookings*100 ,3)end  as "50" ,
  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=51 )),0)/bookings*100 ,3)end  as "51" ,
  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=52 )),0)/bookings*100 ,3)end  as "52" ,
  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=53 )),0)/bookings*100 ,3)end  as "53"
  207. 	  from newlms_mob_view_materialized mmv
  208. 	left join main_query mq on booking_year = mq.loan_year and  booking_month = mq.loan_mon
  209. 	inner join customer_filter cf on cf.cust = mmv.customer_id
  210. 	inner join loan_app_loan lal on lal.id = loan_id_seq
  211. 	inner join merchant_filter mf on mf.id = lal.id
  212. 	inner join customer_first_loan cfl on cfl.id = lal.id
  213.  
  214.    	where 1=1 and seq_date <= current_date
  215. [[and lal.loan_upfront_fee = {'number':'admin_fee_percentage'}]]
  216. [[and lal.num_months = {'number':'tenor'}]]
  217. [[and cfl.row_num = case when '{'dropdown':'Customer_Type':[1:New]}' = '1' then 1 else case when cfl.row_num > 1 then cfl.row_num end end]]
  218.  
  219.  
  220. 	group by  mq.bookings,mq.first_trans,mq.booked_loans,mq.loan_year,mq.loan_mon 
  221.  
  222.  
  223. )
  224.  
  225.  
  226. select * from delinq_table
  227. order by loan_year, loan_mon
Success #stdin #stdout #stderr 0s 5308KB
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/JESyTF
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!