WITH subs_table AS
                (SELECT zoho_subscriptions.zoho_subscription.reference_id,
                        zoho_subscriptions.zoho_subscription.created_at,
                        zoho_subscriptions.zoho_subscription.plan_code,
                        zoho_subscriptions.zoho_subscription.status,
                        zoho_subscriptions.zoho_customer.email
                        FROM zoho_subscriptions.zoho_subscription
                INNER JOIN zoho_subscriptions.zoho_customer
                        ON zoho_subscriptions.zoho_subscription.customer_id = zoho_subscriptions.zoho_customer.customer_id
                INNER JOIN users.users_user
                        ON users.users_user.guid = zoho_subscriptions.zoho_customer.user_guid
                WHERE
                        zoho_subscriptions.zoho_customer.email NOT LIKE '%@ueni%'
                        AND zoho_subscriptions.zoho_customer.email NOT LIKE '%@test%'
                        AND zoho_subscriptions.zoho_subscription.reference_id <> ''
                        AND zoho_subscriptions.zoho_subscription.status = 'live'
                        AND zoho_subscriptions.zoho_subscription.created_at > '2018-12-31'
                       ),
venues_table AS (
                SELECT venues.venues_venue.primary_guid,
                                venues.venues_venue.id,
                                venues.venues_content.content_creation_status,
                                venues.venues_venue.slug,
                                venues.venues_address.city,
                                venues.venues_address.neighbourhood,
                                venues.venues_address.postal_code,
                                venues.venues_address.country_code
                FROM venues.venues_venue
                INNER JOIN venues.venues_content
                    ON venues.venues_content.venue_id = venues.venues_venue.id
                LEFT JOIN venues.venues_address
                    ON venues.venues_address.venue_id = venues.venues_venue.id
                WHERE venues.venues_venue.in_subs = TRUE
                    AND venues.venues_content.content_creation_status ='finished'
),
built_date AS (
                SELECT DISTINCT venues.venues_venue.primary_guid,
                                MIN(TO_CHAR(venues.venues_historicalcontent.history_date,'YYYY-MM-DD')) AS built_date
                FROM venues.venues_venue
            INNER JOIN venues.venues_historicalcontent
                    ON venues.venues_venue.id = venues.venues_historicalcontent.venue_id
                WHERE venues.venues_venue.in_subs = TRUE
                    AND (venues.venues_historicalcontent.content_creation_status = 'finished')
            GROUP BY 1
),
category_table AS
             (SELECT DISTINCT venues.venues_venue.primary_guid AS guid,
                        coalesce(MIN(categories.categories_webappcategory.NAME),signup2_categories.name) AS category
                FROM  venues.venues_venue
                LEFT JOIN venues.venues_category
                        ON venues.venues_venue.id=venues.venues_category.venue_id
                LEFT JOIN categories.internal_servicecategory
                        ON venues.venues_category.INDEX = categories.internal_servicecategory.service_index
                LEFT JOIN categories.internal_servicecategory_webapp_categories
                        ON categories.internal_servicecategory_webapp_categories.servicecategory_id = categories.internal_servicecategory.id
                LEFT JOIN categories.categories_webappcategory
                        ON categories.categories_webappcategory.id = categories.internal_servicecategory_webapp_categories.webappcategory_id
                left join (select 	category_index,
                					name
                					from categories.categories_webappcategory) as signup2_categories
                		on signup2_categories.category_index = venues.venues_category.INDEX
                WHERE venues.venues_venue.in_subs = TRUE
                GROUP BY primary_guid,signup2_categories.name)
SELECT subs_table.reference_id,
        subs_table.email,
        venues_table.country_code,
        category_table.category,
        built_date.built_date
FROM subs_table
    INNER JOIN venues_table
        ON venues_table.primary_guid = subs_table.reference_id
    LEFT JOIN category_table
        ON subs_table.reference_id = category_table.guid
    LEFT JOIN built_date
        ON built_date.primary_guid = subs_table.reference_id