Driving Licence Renewal - Rahil

Select A.member_id,A.date as joined_date, A.id as recent_dl_id, D.state, A.grade, IFNULL(B.rsv,0) as rsv, IFNULL(c.amount,0) as amount
from

(Select
distinct dl.member_id as member_id,
date(m.created_at + interval '8' hour) as date,
max(dl.id) as id,
m.grade as grade
from driver_licenses dl
join members m on m.id = dl.member_id
where m.imaginary in ('normal')
group by dl.member_id
order by id desc) A
left join
(select dl.state as state, dl.id as id
from driver_licenses dl) D
on A.id = D.id
left join
(select
distinct r.member_id as member_id,
IFNULL(count(r.id),0) as rsv
from
reservations r
where r.state = 'completed'
group by r.member_id
order by r.member_id) B
on A.member_id = B.member_id
left join
(select
distinct p.member_id as member_id,
sum(p.amount) as amount
from payments p
where paid_type in ('card')
and p.state = 'normal'
group by p.member_id) c
on A.member_id = c.member_id
where D.state ='renewal'


################# To Check DL Impact

select
distinct dl.member_id,
dl.state
from driver_licenses dl
where dl.approved_at + interval '8' hour > '2019-06-24'
and dl.state = 'approved';