/* Coupon by Month [Brief version] */
select C.year, C.month, case when C.coupon_name is null then '' else C.coupon_name end as coupon_name, 
C.bookings, coalesce(D.MFT, 0) as MFT, C.MAU, C.gross_rev, C.net_rev, 
ROUND(C.coupon_rate*100, 2) as coupon_rate, C.coupon_description from
(select extract('year' from b.request_start_at) as year,
extract('month' from b.request_start_at) as month, p.code as coupon_name, p.description as coupon_description,
count(distinct b.id) as bookings, count(distinct b.user_id) as MAU, sum(b.gross_amount) as gross_rev, 
sum(b.gross_amount) - sum(b.net_amount) as coupon, sum(b.net_amount) as net_rev,
(sum(b.gross_amount) - sum(b.net_amount)) / sum(b.gross_amount) as coupon_rate
from bookings b 
left JOIN booking_promos bp ON bp.booking_id=b.id
LEFT JOIN promos p ON bp.promo_id=p.id
where b.status = 'completed' and b.request_start_at between '2020-01-01' and '2020-06-30'
group by 1,2,3,4) C
left join
(SELECT extract('year' from b.request_start_at) as year,
extract('month' from b.request_start_at) as month, 
p.code as coupon_name, p.description as coupon_description,
count(b.id) as MFT FROM bookings b
left JOIN booking_promos bp ON bp.booking_id=b.id
LEFT JOIN promos p ON bp.promo_id=p.id
JOIN users u ON b.user_id = u.id
WHERE b.id IN (SELECT min(id) FROM bookings WHERE status IN ('completed') GROUP BY user_id)
GROUP BY 1,2,3,4) D
on C.year = D.year and C.month = D.month and C.coupon_name = D.coupon_name