Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
with impressions as (
SELECT coalesce(runpid::varchar, run_dev_id_source) uid, min(tstamp) tstamp
FROM
c_f_rat_flat r
LEFT OUTER JOIN
d_xd_src_runpid d
ON r.run_dev_id_source=d.src_id AND r.run_dev_id_type=d.src_id_type
WHERE campaign_id=5147 AND event = 'IMPRESSION' AND tstamp > '2016-08-01 00:00:00' AND src_id_type in ('adGearCookie', 'platformDeviceId')
GROUP BY runpid, run_dev_id_source, txn
),
clicks as (
SELECT coalesce(runpid::varchar, run_dev_id_source) uid, MIN(tstamp) earliest_click
FROM c_f_rat r
LEFT OUTER JOIN
d_xd_src_runpid d
on r.run_dev_id_source=d.src_id AND r.run_dev_id_type=d.src_id_type
WHERE campaign_id=5147 AND event = 'CLICK' AND src_id_type in ('adGearCookie', 'platformDeviceId')
AND tstamp > '2016-08-01 00:00:00'
GROUP BY runpid, run_dev_id_source
)
SELECT imp_count,
count(
CASE WHEN
clicked then NULL
else 1
END
)unclicked,
count(
CASE WHEN
clicked then 1
else NULL
END
) clicked
FROM(
SELECT count(uid) imp_count, min(click) is not null clicked
FROM (
SELECT impressions.uid, impressions.tstamp imp, clicks.earliest_click click
FROM impressions
LEFT OUTER JOIN clicks
ON impressions.uid=clicks.uid
where impressions.tstamp < clicks.earliest_click OR clicks.earliest_click IS NULL
)
GROUP BY uid
)
GROUP BY imp_count
ORDER BY imp_count
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.