TTT - car list algo - A

/* Sorting Algorithm */
select A.list_id, A.listing_date, A.car_brand, A.car_model, A.host, A.category,
A.completed_booking as bookings, coalesce((A.fulfilled_booking)::numeric/nullif((A.fulfilled_booking + A.rejected_booking), 0),0) as fulfillment_rate,
A.listing_price, (A.listing_price - 150) / 150  as price_diff_median,
(listing_price - case when (31*insured_amount)/13440 < '20' then '20' else (31*insured_amount)/13440 end)/
(case when (31*insured_amount)/13440 < '20' then '20' else (31*insured_amount)/13440 end) as price_diff, coalesce(A.review,0) as review,
coalesce((A.rental_amount - A.payout_amount) / A.rental_amount, 0) as commission from
(select l.id as list_id, date_trunc('day', l.created_at)::date as listing_date,
br.name as car_brand, m.name as car_model,
case when u.display_name is not null then u.display_name else concat(u.first_name,' ',u.last_name) end as host,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category,
count(distinct case when b.status = 'completed' then b.id end) as completed_booking,
count(distinct case when b.status in ('accepted', 'completed') then b.id end) as fulfilled_booking,
count(distinct case when b.status = 'rejected' and (b.reason not in ('auto-rejected') or b.reason is null) then b.id end) as rejected_booking,
lp.base_price as listing_price, li.insured_amount,
avg(case when b.status = 'completed' and brv.type = 'renter' then brv.rating end) as review,
sum(case when b.status = 'completed' then bl.amount end) as rental_amount,
sum(case when b.status = 'completed' then po.amount end) as payout_amount
from listings l
left join models m on l.model_id = m.id
left join brands br on m.brand_id = br.id
left join listing_pricing lp on lp.listing_id = l.id
left join listing_insurance li on li.listing_id =l.id
left join company_users cu ON cu.user_id = l.user_id
left join users u on l.user_id = u.id
left join bookings b on b.listing_id = l.id
left join booking_reviews brv on brv.booking_id = b.id
left join payouts po ON po.booking_id=b.id
left JOIN booking_lines bl ON bl.booking_id=b.id AND bl.slug='rental-fee'
where l.status = 'approved' and l.deleted_at is null
group by 1,2,3,4,5,6,10,11) A
/* select percentile_cont(0.5) WITHIN GROUP (ORDER BY lp.base_price) as median_price from listing_pricing lp */

/* Car Model */
select br.name as car_brand, m.name as car_model,
count(distinct l.id) as cars,
count(distinct case when b.status not in ('payment_failed') then b.id end) as requested_booking,
count(distinct case when b.status in ('accepted', 'completed') then b.id end) as fulfilled_booking,
count(distinct case when b.status = 'completed' then b.id end) as completed_booking,
sum(case when b.status = 'completed' then b.gross_amount end) as gross_amount,
sum(case when b.status = 'completed' then b.gross_amount end) - sum(case when b.status = 'completed' then b.net_amount end) as coupon,
sum(case when b.status = 'completed' then b.net_amount end) as net_amount,
sum(case when b.status = 'completed' then (EXTRACT(EPOCH FROM b.request_end_at) - EXTRACT(EPOCH FROM b.request_start_at))/86400 end) as dur,
sum(case when b.status = 'completed' then ceil((EXTRACT(EPOCH FROM b.request_end_at) - EXTRACT(EPOCH FROM b.request_start_at))/86400) end) as adj_dur
from listings l
left join models m on l.model_id = m.id
left join brands br on m.brand_id = br.id
left join bookings b on b.listing_id = l.id
where l.status = 'approved'
and l.deleted_at is null
group by 1,2
order by 1,2

/* Full List */
select l.id, case when u.display_name is not null then u.display_name else concat(u.first_name,' ',u.last_name) end as host,
case when cu.id is not null then 'Fleet'
when cu.id is null and li.insurance_package_id = 2 then 'Individual (Trevo-Shield)'
else 'Individual (Basic)' end category,
m.type as car_type, br.name as car_brand, m.name as car_model, date_trunc('day', l.created_at)::date as listing_date,
lp.base_price as listing_price, li.insured_amount, m.year as purchase_year,
count(distinct case when b.status = 'completed' then b.id end) as completed_booking
from listings l
left join models m on l.model_id = m.id
left join brands br on m.brand_id = br.id
left join listing_pricing lp on lp.listing_id = l.id
left join listing_insurance li on li.listing_id =l.id
left join company_users cu ON cu.user_id = l.user_id
left join users u on l.user_id = u.id
left join bookings b on b.listing_id = l.id
where l.status = 'approved'
and l.deleted_at is null
group by 1,2,3,4,5,6,7,8,9,10
order by 2