View difference between Paste ID: U5aecr8k and favhhCkM

 /* 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