DL Reject reason and members - Rahil

select distinct dl.member_id , dl.reject_category_id , dlc.name , Date(dl.created_at + interval 8 hour) as date
from driver_licenses dl left join driver_license_reject_categories dlc on dlc.id = dl.reject_category_id
where dl.state = 'reject'
and dl.reject_category_id in ('10')
and Date(dl.created_at + interval 8 hour) = date_sub(curdate() , interval 1 day)
order by dl.member_id desc ;

# no of members by reject reason /* */

select
 date_format(date(dl.created_at + interval 8 hour), "%Y-%m-%d") as cday,
 count(dl.id) as total,
 count(if(rc.id=1, dl.id, null)) as sf_cov_bydoc,
 count(if(rc.id=2, dl.id, null)) as sf_wo_face,
 count(if(rc.id=3, dl.id, null)) as sf_wo_doc,
 count(if(rc.id=4, dl.id, null)) as dl_no_doc,
 count(if(rc.id=5, dl.id, null)) as dl_doc_blur,
 count(if(rc.id=6, dl.id, null)) as dl_expired,
 count(if(rc.id=7, dl.id, null)) as pdl,
 count(if(rc.id=8, dl.id, null)) as jpj_blacklist,
 count(if(rc.id=9, dl.id, null)) as dl_unmatched,
 count(if(rc.id=10, dl.id, null)) as pic_crop,
 count(if(rc.id=11, dl.id, null)) as unser_21,
 count(if(rc.id=12, dl.id, null)) as fake_id,
 count(if(rc.id=13, dl.id, null)) as etc,
 count(if(rc.id=14, dl.id, null)) as duplicate_IC,
 count(if(rc.id=15, dl.id, null)) as IC_in_selfie_blur,
 count(if(rc.id=16, dl.id, null)) as passport_blur,
 count(if(rc.id=17, dl.id, null)) as unrelated


from members m, driver_licenses dl, driver_license_reject_categories rc
where
 m.id = dl.member_id
 and m.imaginary in ('sofam', 'normal')
 and dl.state = 'reject'
 and dl.reject_category_id = rc.id
group by cday
order by cday desc