with "cte_contacts" as (
	select			distinct on ("c"."contactid")
				"c"."contactid" "id"
		from		"contacts" "c"
		join		"domains" "d"
			using	("domainid")
		join		"contactdetails" "cd"
			using	("contactid")
		join		"contenttypes" "ct"
			using	("contenttypeid")
		join		"activemailings_0000230" "a"
			using	("contactid")
		left join	"activemailingsgenerated_0000230" "g"
			using	("progresscounter")
		where		"a"."progresscounter" between 415000 and 419999
			and	"g"."contactid" is null
		order by	"c"."contactid"
)
select			distinct on ("cd"."contactid", "cd"."progresscounter")
			"cd".*,
			td_13.td_13,
			relation_4.xd_mw_subscriber_domainname
	from		(
			select			"am"."progresscounter",
						"am"."json",
						"am"."segmentid",
						"c"."localpart"||'@'||"d"."domainname" as "emailaddress",
						"c"."localpart",
						"d"."domainname" as "domain",
						"c"."statusid",
						"d"."domainid",
						"d"."domainname",
						"ct"."description" as "contenttype",
						"cd".*,
						"cd"."firstname"||' '||"cd"."lastname" as "fullname"
				from		"contacts" "c"
				join		"domains" "d"
					using	("domainid")
				join		"contactdetails" "cd"
					using	("contactid")
				join		"contenttypes" "ct"
					using	("contenttypeid")
				join		"activemailings_0000230" "am"
					using	("contactid")
				where		"c"."contactid" in (select "id" from "cte_contacts")
			) "cd"
	left join (
			select			"contactid",
						"tagid" td_13
				from		"tagdetails"
				where		"tagid" = '13'
					and	"contactid" in (select "id" from "cte_contacts")
			) as td_13
		on	td_13."contactid" = "cd"."contactid"
	left join	(
			select			"join_contactid",
						mw_subscriber.subscriberid as xd_mw_subscriber_subscriberid,
						mw_subscriber.countryid as xd_mw_subscriber_countryid,
						mw_subscriber.email as xd_mw_subscriber_email,
						mw_subscriber.firstname as xd_mw_subscriber_firstname,
						mw_subscriber.lastname as xd_mw_subscriber_lastname,
						mw_subscriber.address1 as xd_mw_subscriber_address1,
						mw_subscriber.address2 as xd_mw_subscriber_address2,
						mw_subscriber.city as xd_mw_subscriber_city,
						mw_subscriber.stateorprovince as xd_mw_subscriber_stateorprovince,
						mw_subscriber.postalcode as xd_mw_subscriber_postalcode,
						mw_subscriber.workphone as xd_mw_subscriber_workphone,
						mw_subscriber.homephone as xd_mw_subscriber_homephone,
						mw_subscriber.dateofbirth as xd_mw_subscriber_dateofbirth,
						mw_subscriber.gender as xd_mw_subscriber_gender,
						mw_subscriber.issendable as xd_mw_subscriber_issendable,
						mw_subscriber.subscribertypeid as xd_mw_subscriber_subscribertypeid,
						mw_subscriber.md5_email as xd_mw_subscriber_md5_email,
						mw_subscriber.domainname as xd_mw_subscriber_domainname,
						mw_subscriber.isdeleted as xd_mw_subscriber_isdeleted,
						mw_subscriber.datecreated as xd_mw_subscriber_datecreated,
						mw_subscriber.datemodified as xd_mw_subscriber_datemodified
				from		(
						select			relation_1."xd_contacts_contactid" as "join_contactid",
									subscriber_map.subscriberid as xd_subscriber_map_subscriberid,
									subscriber_map.contactid as xd_subscriber_map_contactid
							from		(
									select			contacts.contactid as xd_contacts_contactid,
												contacts.statusid as xd_contacts_statusid,
												contacts.localpart as xd_contacts_localpart,
												contacts.domainid as xd_contacts_domainid,
												"contacts"."contactid" as "join_contactid"
										from		org0000001.contacts
										where		"contactid" in (select "id" from "cte_contacts")
									) relation_1
							left join	relational.subscriber_map
								on	xd_contacts_contactid = subscriber_map.contactid
						) relation_3
				left join	relational.mw_subscriber
					on	xd_subscriber_map_subscriberid = mw_subscriber.subscriberid
			) relation_4
		on	relation_4."join_contactid" = "cd"."contactid"
	where		"cd"."statusid" = 1
		and	(
				(
					(  td_13 is not null)
				)
			)
	order by	"cd"."progresscounter";