GrabPay by city Analyses Query - Rahil

### Reservations with Grabpay vs Card Pay - Divided by Regions
select
Date(r.return_at + interval '8' hour) as date,
z.city as city,
count(distinct r.id) as resv,
count(distinct case when p.paid_type = 'card' then p.member_id end) as cardpaid,
count(distinct case when p.paid_type = 'eWallet' then p.member_id end) as grabpay
from reservations r
join zones z on r.start_zone_id = z.id
right join payments p on p.reservation_id = r.id
join members m on r.member_id = m.id
where r.return_at >= '2019-4-1'
and r.state = 'completed'
and z.city in ('KL','JB','PG','SL')
and m.state = 'normal'
and m.imaginary = 'normal'
group by date,city;

##### Finding MFTs Tagged to Members paid through "Grabpay" , Divided by "City"

select
Date(r.start_at + interval '8' hour) as date,
count(distinct r.member_id) as mid,
z.city as city
from reservations r
Right join payments p on p.member_id = r.member_id
join members m on m.id = r.member_id
join zones z on r.start_zone_id = z.id
where m.created_at + interval '8' hour >= '2019-6-12'
and p.paid_type = 'eWallet'
group by date,city