Example frequency report for "Writing Analytics SQL with Common Table Expressions."

frequency_example_with_subqueries.sql

/*
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
;

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.