SELECT
cid,
SUM(redirects_to_offer) AS redirects_to_offer,
SUM(visitors) AS visitors,
SUM(unique_visitors) AS unique_visitors,
SUM(loaded) AS loaded,
SUM(blocked) AS blocked,
SUM(subscription_failed) AS subscription_failed,
SUM(service_worker_failed) AS service_worker_failed,
SUM(browser_unsupported) AS browser_unsupported,
SUM(subscribed) AS subscribed,
SUM(blocked_with_back_button) AS blocked_with_back_button,
SUM(inside_iframe) AS inside_iframe,
SUM(already_subscribed) AS already_subscribed,
SUM(already_blocked) AS already_blocked,
toDecimal32(SUM(cost), 4) AS cost,
toDecimal32(SUM(revenue), 4) AS revenue
FROM
(
SELECT
e.cid AS cid,
0 AS redirects_to_offer,
0 AS visitors,
0 AS unique_visitors,
SUM(if(e.event_id = '1', 1, 0)) AS loaded,
SUM(if(e.event_id = '2', 1, 0)) AS blocked,
SUM(if(e.event_id = '3', 1, 0)) AS subscription_failed,
SUM(if(e.event_id = '4', 1, 0)) AS service_worker_failed,
SUM(if(e.event_id = '5', 1, 0)) AS browser_unsupported,
SUM(if(e.event_id = '6', 1, 0)) AS subscribed,
SUM(if(e.event_id = '7', 1, 0)) AS blocked_with_back_button,
SUM(if(e.event_id = '8', 1, 0)) AS inside_iframe,
SUM(if(e.event_id = '9', 1, 0)) AS already_subscribed,
SUM(if(e.event_id = '10', 1, 0)) AS already_blocked,
SUM(toDecimal32(e.cost, 4)) AS cost,
toDecimal32(0, 4) AS revenue
FROM events AS e
WHERE
toDate(e.timestamp) >= toDate(?)
AND
toDate(e.timestamp) <= toDate(?)
AND
ts=?
GROUP BY
e.cid
UNION ALL
SELECT
conversions.cid AS cid,
0 AS redirects_to_offer,
0 AS visitors,
0 AS unique_visitors,
0 AS loaded,
0 AS blocked,
0 AS subscription_failed,
0 AS service_worker_failed,
0 AS browser_unsupported,
0 AS subscribed,
0 AS blocked_with_back_button,
0 AS inside_iframe,
0 AS already_subscribed,
0 AS already_blocked,
toDecimal32(0, 4) AS cost,
SUM(toDecimal32(conversions.payout, 4)) AS revenue
FROM conversions
WHERE
toDate(conversions.timestamp) >= toDate(?)
AND
toDate(conversions.timestamp) <= toDate(?)
AND
ts=?
GROUP BY
conversions.cid
UNION ALL
SELECT
visits.cid AS cid,
0 AS redirects_to_offer,
count() AS visitors,
uniq(client_id) AS unique_visitors,
0 AS loaded,
0 AS blocked,
0 AS subscription_failed,
0 AS service_worker_failed,
0 AS browser_unsupported,
0 AS subscribed,
0 AS blocked_with_back_button,
0 AS inside_iframe,
0 AS already_subscribed,
0 AS already_blocked,
toDecimal32(0, 4) AS cost,
toDecimal32(0, 4) AS revenue
FROM visits
WHERE
toDate(visits.timestamp) >= toDate(?)
AND
toDate(visits.timestamp) <= toDate(?)
AND
ts=?
GROUP BY
visits.cid
UNION ALL
SELECT
clicks.cid AS cid,
count() AS redirects_to_offer,
0 AS visitors,
0 AS unique_visitors,
0 AS loaded,
0 AS blocked,
0 AS subscription_failed,
0 AS service_worker_failed,
0 AS browser_unsupported,
0 AS subscribed,
0 AS blocked_with_back_button,
0 AS inside_iframe,
0 AS already_subscribed,
0 AS already_blocked,
toDecimal32(0, 4) AS cost,
toDecimal32(0, 4) AS revenue
FROM clicks
WHERE
toDate(clicks.timestamp) >= toDate(?)
AND
toDate(clicks.timestamp) <= toDate(?)
AND
type=1
AND
ts=?
GROUP BY
clicks.cid
)
GROUP BY
cid