CREATE VIEW rep_ops_first_ap_offer as (
SELECT
ap.id "ap_id",
ao.id "offer_id",
ifnull(ap.first_offer_published_at, min(ao.published_at)) "published_date",
ao.created_source,
ao.created_by_type,
IF(pp.created_by = 'c4f8a449-e69e-484a-a277-939b0a1a1e63',1,0) "old_ss",
count(distinct ao.id) "total_offers",
p.address_city_code "city",
bs.score


FROM prod_reporting.accommodation_offer ao
LEFT JOIN prod_reporting.property AS p ON p.id = ao.property_id
LEFT JOIN prod_reporting.accommodation_provider AS ap ON ap.id = p.accommodation_provider_id
LEFT JOIN prod_reporting.prospective_property pp ON p.id = pp.id
LEFT JOIN prod_reporting_ops.rep_ops_ac_bookable_supply AS bs ON bs.offer_id = ao.id

WHERE ap.is_test = 0
AND ao.published_at is not null
GROUP BY ap.id);