Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Example frequency report for "Writing Analytics SQL with Common Table Expressions."
/*
Copyright 2014 Neustar, Inc.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
SELECT impression_count AS frequency_class,
campaign_id AS campaign_id,
SUM(1) AS total_users,
SUM(COALESCE(impression_count, 0)) AS total_impressions,
SUM(COALESCE(click_count, 0)) AS total_clicks,
SUM(COALESCE(conversion_count, 0)) AS total_conversions
FROM
(SELECT imp.user_id,
imp.campaign_id,
imp.impression_count,
cl.click_count,
conv.conversion_count
FROM (SELECT user_id,
campaign_id,
SUM(1) AS impression_count
FROM impressions
WHERE record_date >= '2014-03-01' AND record_date < '2014-04-01'
GROUP BY 1, 2) AS imp
LEFT OUTER JOIN
(SELECT user_id,
campaign_id,
SUM(1) as click_count
FROM clicks
WHERE record_date >= '2014-03-01' AND record_date < '2014-04-01'
GROUP BY 1, 2) AS cl ON
imp.user_id = cl.user_id AND
imp.campaign_id = cl.campaign_id
LEFT OUTER JOIN
(SELECT conversions.user_id,
conversions.campaign_id,
SUM(1) as conversion_count
FROM (
(SELECT * FROM impression_attributed_conversions)
UNION ALL
(SELECT * FROM click_attributed_conversions)) AS conversions
WHERE conversions.record_date >= '2014-03-01' AND
conversions.record_date < '2014-04-01'
GROUP BY 1, 2) AS conv ON
imp.user_id = conv.user_id AND
imp.campaign_id = conv.campaign_id) AS counts
GROUP BY 1, 2
;
Owner

augescens commented May 19, 2014

This is an example for the blog post "Writing Analytics SQL with Common Table Expressions."

Compare this version to the same query written with Common Table Expressions.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment