DROP TEMPORARY TABLE IF EXISTS top5ads;

CREATE TEMPORARY TABLE top5ads (click_count INT(10) UNSIGNED, ad_id INT(10) UNSIGNED);

INSERT INTO top5ads (click_count, ad_id)

SELECT

	COUNT(*) AS click_count,

	ads.id AS ad_id

FROM

	employ__ad_clicks ad_clicks

	JOIN employ__ads ads ON ads.id = ad_clicks.ad_id

	JOIN employ__companies cmps ON cmps.id = ads.company_id

WHERE

	cmps.vdma_company_id > 0 AND

	ads.job_type_id IN (1,2,3,4,5,13,19) AND

	ad_clicks.host <> '' AND

	-- {job_type_id} AND

	click_date BETWEEN '2018-01-01' AND '2018-07-01'

GROUP BY

	ads.id

ORDER BY

	click_count DESC

LIMIT 5;

SELECT

	click_count,

	CONCAT

	(

		TRIM(ads.title), ' | ',

		(

			SELECT

				CONCAT(pcs.postal_code, ' ', ad_pcs.city)

			FROM

				employ__ads_postal_codes ad_pcs

				JOIN employ__postal_codes pcs ON pcs.id = ad_pcs.postal_code_id

			WHERE

				ad_pcs.ad_id = ads.id

			LIMIT 1

		), ' | ',

		click_count,

		'\n',

		cmps.name, ' ', cmps.second_name, ' | ', DATE_FORMAT(ads.created_at, '%d.%m.%Y'), ' | ',

		CASE WHEN

			ads.published AND

			CURRENT_DATE BETWEEN ads.publishes_at AND ads.ends_at

		THEN

			'online'

		ELSE

			'offline'

		END

	) AS ad_title,

	ads.job_type_id,

	ads.permalink,

	mpgs_clicks_lists.mpgs_clicks_list,

	'Top' AS x_label

FROM

	top5ads

JOIN employ__ads ads ON ads.id = top5ads.ad_id

JOIN employ__companies cmps ON cmps.id = ads.company_id

JOIN

	(

		SELECT

			adcs_ad_id,

			GROUP_CONCAT(mpg_name_click_count ORDER BY mpg_name_click_count SEPARATOR '\n') AS mpgs_clicks_list

		FROM

		(

			SELECT

				adcs_ad_id,

				CONCAT(click_counts_mpgs.name, ': ', click_counts_mpgs.click_count_mpg) AS mpg_name_click_count

			FROM

			(

				SELECT

					adcs.ad_id AS adcs_ad_id,

					COUNT(*) as click_count_mpg,

					mpgs.name

				FROM

					employ__ad_clicks adcs

					JOIN employ__marketplace_group_hosts mpg_hosts ON adcs.host = mpg_hosts.name

					JOIN employ__marketplace_groups mpgs ON mpgs.id = mpg_hosts.marketplace_group_id

				WHERE

					adcs.host <> '' AND

					adcs.click_date BETWEEN '2018-01-01' AND '2018-07-01'

				GROUP BY

					mpgs.name,

					adcs.ad_id

			) click_counts_mpgs

		) mpg_name_click_counts

		GROUP BY

			adcs_ad_id

		ORDER BY mpg_name_click_counts.adcs_ad_id

	)	

	mpgs_clicks_lists ON mpgs_clicks_lists.adcs_ad_id = ads.id;